Author: Mars (hnynes)
MSN: hnynes@gmail.com
近期用到了数据库的时间处理,总结一些常用的,分享我理解到的工作原理
先看下一些函数的使用
DATEDIFF(datepart, startdate, endate)
DATEDIFF的后两个参数都是DATETIME类型的
DATEADD (datepart , number , date)
DATEADD的第三个参数也是DATETIME类型的
再看一条SQL语句
SELECT CAST(0 AS DATETIME)
返回的结果是1900-01-01 00:00:00.000
也就是说将0转换成DATETIME类型时,就相当于这么一个常量
那么在使用函数时,若将0赋给DATETIME的形参时,就相当于与0对应的常量操作,
即1900-01-01 00:00:00.000
另外还有一点是SQL SERVER中的DATETIME是精确到3ms的.
the first day of this month
SELECT DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0)
工作原理: 这里重点需要理解的是0,将0与当前的时间在month上做减法,然后再把
这个差值加到0的月份上,最后得出的就是本月的第一天.
the first day of this week
SELECT DATEADD(WK, DATEDIFF(WK, 0, GETDATE()), 0)
工作原理: 同上理,将0与当前的时间在week上做减法,然后再把这个差值加到0的week上,
最后得出的就是本周的第一天.
the first day of this year
SELECT DATEADD(YY, DATEDIFF(YY, 0, GETDATE()), 0)
工作原理与上类似,在此就不赘述了.
the first day of current quarter
SELECT DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()), 0)
工作原理与上类似
at 0 o'clock of today
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
工作原理与上类似
the last day of last month
SELECT DATEADD(MS, -3, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0))
SELECT DATEADD(DAY, -1, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0))
两条语句都可以实现
第一条语句是应用了SQL SERVER的DATETIME精确到3ms的特性,因为只需要取到day,
所以只要日期对就可以,时间可以不理会.
第二条语句就是在本月第一天0点的基础上减去一天,就得到上月的最后一天.
the last day of last year
SELECT DATEADD(MS, -3, DATEADD(YY, DATEDIFF(YY, 0, GETDATE()), 0))
SELECT DATEADD(DAY, -1, DATEADD(YY, DATEDIFF(YY, 0, GETDATE()), 0))
工作原理同上.
the last day of current month
SELECT DATEADD(MS, -3, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0))
SELECT DATEADD(DAY, -1, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0))
工作原理同上,先获取下月的第一天,然后再修改为本月的最后一天.
the last day of current year
SELECT DATEADD(MS, -3, DATEADD(YY, DATEDIFF(YY, 0, GETDATE()) + 1, 0))
SELECT DATEADD(DAY, -1, DATEADD(YY, DATEDIFF(YY, 0, GETDATE()) + 1, 0))
工作原理同上.
the first Mondy of current month
SELECT DATEADD(WK, DATEDIFF(WK, 0, DATEADD(DAY, 6 - DATEPART(DAY, GETDATE()), GETDATE())), 0)
工作原理:此条语句简单的解释就是获取每个月6号所在周的周一
这里需要注意的是SELECT DATEPART(DW, '1900-1-1')返回值为2,即周一,在此
情形下,上面的那句话才是有正确的.
默认时,周日为第一天,周六为第七天.若有习惯周一为第一天的,周日为第七天,
可以设置数据库相关的全局参数.
参数DW在DATEADD与DATEDIFF的文档页,是没有列出的,在DATEPART文档页才有列出.
此语句使用了一个小技巧,因为默认周日为第一天,而一周有七天,因此如果6号在
第一周,则至少要是周六之前,因为周日就是下一周了,既然在周六之前,那么获取
这一周的第一天,当然就是周一了,若6号是周日或周日以后,则表示这个月的1号
在周一之后了,所以本月的第一个周一还是在6号所在的这一周.
如果要获取周二,如下,其它的类推.
SELECT DATEADD(WK, DATEDIFF(WK, 0, DATEADD(DAY, 6 - DATEPART(DAY, GETDATE()), GETDATE())), 1)
以上语句虽然是在SQL SERVER下测试的,但原理对SQL语句而言,都是相通的,
即在其它数据库如MYSQL等,用数据库相对应的函数也是可以得到相同的结果的.
对于最后一个,若应用到其它数据库,需要注意其它数据库对一周的周起止是否与
上面提到的一致.
若对上述有不同的见解,欢迎邮件或MSN到 hnynes@gmail.com
