?
在项目开发中，对SQL的查询经常会涉及到日期的计算上，今天就来简单归纳一下相关日期操作，主要分为MySQL和MSSQL的，Oracle在项目中暂时还没真正使用，所以暂时先不归纳，等用到时再根据实际项目开发情况进行归纳；
MSSQL：
1、 求相差天数
select datediff(day,'2012-01-01',getdate()) as '相差天数'; --8
?
2、求本年的第一天
SELECT DATEADD(yy,DATEDIFF(yy,0,getdate()),0) as '本年第一天';-- 2012-01-01 00:00:00.000
?
或
declare @dt datetimeset @dt=GETDATE()select CONVERT(char(5),@dt,120)+'1-1' as '本年的第一天';--2012-1-1
?
?3、求本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1,0)) as '本年最后一天';--2012-12-31 23:59:59.997
?
?或
declare @dt datetimeset @dt=GETDATE()SELECT CONVERT(char(5),@dt,120)+'12-31' as '本年的最后一天';-- 2012-12-31
?
?4、去年的最后一天
?
SELECT dateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate()), 0)) as '去年的最后一天';--2011-12-31 23:59:59.997
?
?5、求本季度第一天
SELECT DATEADD(yy,DATEDIFF(yy,0,getdate()),0) as '本季度第一天';-- 2012-01-01 00:00:00.000
?
?或
declare @dt datetimeset @dt=GETDATE()SELECT CONVERT(datetime,CONVERT(char(8),DATEADD(Month,DATEPART(Quarter,@dt)*3-Month(@dt)-2,@dt),120)+'1') as '本季度的第一天';--2012-01-01 00:00:00.000
?
6、求本季度最后一天
declare @dt datetimeset @dt=GETDATE()SELECT CONVERT(datetime, CONVERT(char(8), DATEADD(Month, DATEPART(Quarter,@dt)*3-Month(@dt), @dt), 120) +CASE WHEN DATEPART(Quarter,@dt) in(1,4) THEN '31'ELSE '30' END) as '本季度最后一天';--2012-03-31 00:00:00.000
?
?或
declare @dt datetimeset @dt=getdate()SELECT DATEADD(Day,-1, CONVERT(char(8), DATEADD(Month, 1+DATEPART(Quarter,@dt)*3-Month(@dt), @dt), 120)+'1') as '本季度最后一天';--2012-03-31 00:00:00.000
?
?7、求本月第一天：
SELECT DATEADD(mm,DATEDIFF(mm,0,getdate()),0) as '本月第一天';--2012-01-01 00:00:00.000
?或
declare @dt datetimeset @dt=getdate()SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+'1') as '本月第一天';--2012-01-01 00:00:00.000
?
8、求本月最后一天：
SELECT dateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0)) as '本月最后一天';--2012-01-31 23:59:59.997
?
?或
declare @dt datetimeset @dt=GETDATE()SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1') as '本月最后一天';--2012-01-31 00:00:00.000
?
或
declare @dt datetimeset @dt=GETDATE()SELECT DATEADD(Month,1,DATEADD(Day,-DAY(@dt),@dt)) as '本月最后一天';--2012-01-31 10:18:32.753
?
?9、上个月最后一天
SELECT dateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0)) as '上个月的最后一天';--2011-12-31 23:59:59.997
?
10、本月的第一个星期一
select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())),0) as '本月的第一个星期一';--2012-01-02 00:00:00.000
?
11、本周的星期一
SELECT DATEADD(wk,DATEDIFF(wk,0,getdate()),0) as '本周的星期一';--2012-01-09 00:00:00.000SELECT DATEADD(wk,DATEDIFF(wk,0,getdate()),1) as '本周的星期二';--2012-01-10 00:00:00.000
?
12、当天的半夜
SELECT DATEADD(dd,DATEDIFF(dd,0,getdate()), 0) as '当天的半夜';--2012-01-09 00:00:00.000
?
13、查询本周记录
select * from tableName where DATEPART(wk, theDate) = DATEPART(wk, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE());
?
14、查询本季度记录
select * from tableName where DATEPART(qq, theDate) = DATEPART(qq, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())
?
15、获取当月总天数
select DATEDIFF(dd,getdate(),DATEADD(mm, 1, getdate()))
?
?或
select datediff(day,dateadd(mm, datediff(mm,'',getdate()), ''),dateadd(mm, datediff(mm,'',getdate()), '1900-02-01'))
?
?16、获取当前为星期几
select DATEPART(dw, GETDATE())--2,星期日为1
?这里简单对DatePart函数做个说明，第一个参数表示要传回日期部分的参数，第二个参数表示日期；
?
日期部份 | 缩写 |
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 |
?
?
?
MySQL：
?
-- 返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。 SELECT DAYOFWEEK(NOW()); -- 2-- 返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。 SELECT WEEKDAY(NOW()); -- 0-- 返回date的月份中日期，在1到31范围内。 SELECT DAYOFMONTH(NOW()); -- 9 -- 返回date在一年中的日数, 在1到366范围内SELECT DAYOFYEAR(NOW()); -- 9 -- 返回date的月份，范围1到12。 SELECT MONTH(NOW()); -- 1-- 返回date的星期名字。 SELECT DAYNAME(NOW()); -- Monday-- 返回date的月份名字。 SELECT MONTHNAME(NOW()); -- January-- 返回date一年中的季度，范围1到4 SELECT QUARTER(NOW()); -- 1-- 对于星期天是一周的第一天的地方，有一个单个参数，返回date的周数，范围在0到52。2个参数形式WEEK()允许 你指定星期是否开始于星期天或星期一。如果第二个参数是0，星期从星期天开始，如果第二个参数是1，从星期一开始。 SELECT WEEK(NOW()); -- 2SELECT WEEK(NOW(),0); -- 2SELECT WEEK(NOW(),1); -- 2-- 返回date的年份，范围在1000到9999。 SELECT YEAR(NOW());-- 2012-- 返回time的小时，范围是0到23。 SELECT HOUR(NOW()); -- 11-- 返回time的分钟，范围是0到59。 SELECT MINUTE(NOW()); -- 2-- SECOND-- MySQL 为日期增加一个时间间隔：date_add()SET @dt = NOW();SELECT DATE_ADD(@dt, INTERVAL 1 DAY); -- 加1天SELECT DATE_ADD(@dt, INTERVAL 1 HOUR); -- 加1小时SELECT DATE_ADD(@dt, INTERVAL 1 MINUTE); -- 加1分钟SELECT DATE_ADD(@dt, INTERVAL 1 SECOND); -- 加1秒SELECT DATE_ADD(@dt, INTERVAL 1 MICROSECOND);-- 加1毫秒SELECT DATE_ADD(@dt, INTERVAL 1 WEEK);-- 加1周SELECT DATE_ADD(@dt, INTERVAL 1 MONTH);-- 加1月SELECT DATE_ADD(@dt, INTERVAL 1 QUARTER);-- 加1季SELECT DATE_ADD(@dt, INTERVAL 1 YEAR);-- 加1年SELECT DATE_ADD(@dt, INTERVAL '01:15:30' HOUR_SECOND);-- 加上1小时15分30秒 SELECT DATE_ADD(@dt, INTERVAL '1 01:15:30' DAY_SECOND);-- 加1天 1小时15分30秒-- MySQL 为日期减去一个时间间隔：date_sub()SELECT DATE_SUB('1998-01-01 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND); -- 1997-12-30 22:58:59-- MySQL 另类日期函数：period_add(P,N), period_diff(P1,P2)函数参数“P” 的格式为“YYYYMM” 或者 “YYMM”，第二个参数“N” 表示增加或减去 N month（月）。MySQL period_add(P,N)：日期加/减去N月。SELECT PERIOD_ADD(200808,2), PERIOD_ADD(20080808,-2) -- 200810 20080806 SELECT PERIOD_DIFF(200808, 200801); -- 7-- datediff(date1,date2)：两个日期相减 date1 date2，返回天数 SELECT DATEDIFF('2008-08-08', '2008-08-01'); -- 7 SELECT DATEDIFF('2008-08-01', '2008-08-08'); -- -7 -- timediff(time1,time2)：两个日期相减 time1 time2，返回 time 差值。 SELECT TIMEDIFF('2008-08-08 08:08:08', '2008-08-08 00:00:00');-- 08:08:08 SELECT TIMEDIFF('08:08:08', '00:00:00'); -- 08:08:08 SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y'); ?
?
如果大家有补充的可以说下，我再继续添加；
?