SQL对日期相关操作

 

在项目开发中，对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 datetime
set @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 datetime
set @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 datetime
set @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 datetime
set @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 datetime
set @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 datetime
set @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 datetime
set @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 datetime
set @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.000
SELECT 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()); -- 2
SELECT WEEK(NOW(),0); -- 2
SELECT 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');  

 

 

如果大家有补充的可以说下，我再继续添加；