SQL中数值与日期处理技巧

### SQL 中数值与日期处理技巧 在 SQL 编程中，处理数值和日期是常见的任务。下面将详细介绍如何在 SQL 中处理数值的前导零以及各种日期操作。 #### 显示前导零 在展示数值数据时，我们常常需要显示前导零。通常，我们可能会逐行处理，摆弄单个字节，但其实有更简单的 Transact - SQL 解决方案。 我们可以使用 `STR()` 和 `REPLACE()` 函数来实现。`STR()` 函数能将数字转换为字符串，并且可以控制结果的位数和小数位数。其语法如下： ```sql STR (float_expression[, length[, decimal]]) ``` 而 `REPLACE()` 函数则可以将 `STR()` 函数生成的字符串中的前导空格替换为前导零。它会在一个字符串中查找指定的字符序列，并将其替换为另一个指定的字符序列。语法如下： ```sql REPLACE ('string_expression1', 'string_expression2', 'string_expression3') ``` 下面是一个示例代码，它将数字 25 转换为带有两位小数和三个前导零的字符串（00025.00）： ```sql SELECT REPLACE (STR (25, 8, 2), ' ', '0') AS TwentyFive ``` #### 日期处理 在商业世界中，日期操作被广泛使用，SQL Server 提供了丰富的函数来处理各种日期相关的任务。 ##### 日期数据类型 在处理日期时，我们实际上使用的是 `datetime` 或 `smalldatetime` 数据类型。这两种类型都包含日期和时间信息，不存在只包含日期或只包含时间的数据类型。如果只给 `datetime` 或 `smalldatetime` 提供日期，它会默认时间为午夜。 `datetime` 数据类型可以精确到 3.33 毫秒，占用 8 个字节，其值的范围从 1753 年 1 月 1 日到 9999 年 12 月 31 日 23:59:59.999。`smalldatetime` 数据类型精确到分钟，占用 4 个字节，其值的范围从 1900 年 1 月 1 日到 2079 年 6 月 6 日 23:59。 ##### 提取日期部分 我们经常需要从日期中提取年、月、日等信息。`DATEPART()` 函数可以完成这些任务，其语法如下： ```sql DATEPART (datepart, date) ``` `DATEPART()` 函数的 `datepart` 参数有多种取值，如下表所示： | Datepart | Abbreviation | | --- | --- | | year | yy, yyyy | | quarter | qq, q | | month | mm, m | | dayofyear | dy, y | | day | dd, d | | week | wk, ww | | weekday | dw | | hour | hh | | minute | mi, n | | second | ss, s | | millisecond | ms | 除了 `DATEPART()` 函数，还有一些更直观的函数可以完成相同的任务，例如 `YEAR()`、`MONTH()` 和 `DAY()` 函数。以下两个查询是等价的： ```sql SELECT DATEPART (yy, getdate ()), DATEPART (mm, getdate ()), DATEPART (dd, getdate ()) SELECT YEAR (getdate ()), MONTH (getdate ()), DAY (getdate ()) ``` ##### 获取日期部分的名称 使用 `DATENAME()` 函数可以获取日期部分的名称，例如月份名称或星期几。返回的名称是当前连接语言的字符字符串，这对于有不同语言设置的用户很方便。 假设我们需要从 Northwind 数据库中生成一个按年和月统计订单数量的报告，并且希望结果集中显示月份名称而不是月份编号，同时按年和月份编号排序。可以使用以下代码： ```sql SELECT YEAR (OrderDate) AS 'Year', MONTH (OrderDate) AS 'Month', DATENAME (mm, OrderDate) AS 'Month Name', COUNT (*) AS 'Count' FROM Orders GROUP BY YEAR (OrderDate), MONTH (OrderDate), DATENAME (mm, OrderDate) ORDER BY YEAR (OrderDate), MONTH (OrderDate) ``` ##### 日期加减操作 `DATEADD()` 函数可以用于日期的递增和递减操作，其语法如下： ```sql DATEADD (datepart, number, date) ``` 例如，要给当前日期加上 5 天，可以使用以下代码： ```sql SELECT DATEADD (dd, 5, GETDATE ()) ``` 如果要减去日期，只需给 `number` 参数传入负数。 从 SQL Server 7.0 版本开始，加法运算符也得到了增强，现在可以使用 `+` 号给给定日期添加天数，而且不限于整数天。例如，要给当前日期加上 1.5 天（36 小时），可以使用以下代码： ```sql SELECT GETDATE () + 1.5 ``` ##### 计算两个日期之间的差值 `DATEDIFF()` 函数可以计算两个不同日期之间的天数、月数、年数、季度数、小时数、分钟数、秒数或毫秒数，其语法如下： ```sql DATEDIFF (datepart, startdate, enddate) ``` ##### 避免日期查询中的陷阱 在使用 `datetime` 和 `smalldatetime` 列存储日期和时间信息时，使用 `BETWEEN` 谓词进行日期范围查询可能会导致意外结果。例如，以下查询： ```sql WHERE SalesDateTime BETWEEN '1Jul2000' and '31Jul2000' `` ```