SQL 中 DateTime日期时间及方法函数

出处：http://www.cnblogs.com/ethan-qi/archive/2012/04/23/2466292.html

一、SQL获取月第一天和最后一天的时间写法

select dateadd(dd,-day(dateadd(month,-1,getdate()))+1,dateadd(month,-1,getdate())) /*上个月一号*/
select dateadd(dd,-day(getdate()),getdate()) /* 上月月底 */
select dateadd(dd,-day(getdate())+1,getdate()) /* 本月一号 */
select dateadd(dd,-day(dateadd(month,1,getdate())),dateadd(month,1,getdate())) /* 本月底 */
select dateadd(dd,-day(dateadd(month,1,getdate()))+1,dateadd(month,1,getdate())) /* 下月一号 */
select dateadd(dd,-day(dateadd(month,2,getdate())),dateadd(month,2,getdate())) /* 下月月底 */

如果想把时间格式转换成“yyyy/mm/dd hh:mi:ss”这种格式，可以：

select rtrim(convert(char,getdate(),111))+''+(convert(char,getdate(),108)) -- yyyy/mm/dd hh:mi:ss

二、Sql Server中的日期与时间函数
　　1. 当前系统日期、时间
　　 select getdate()
　　2. dateadd 在向指定日期加上一段时间的基础上，返回新的 datetime 值
　　 例如：向日期加上2天
　　 select dateadd(day,2,'2004-10-15') --返回：2004-10-17 00:00:00.000
　　3. datediff 返回跨两个指定日期的日期和时间边界数。
　　 select datediff(day,'2004-09-01','2004-09-18') --返回：17
　　4. datepart 返回代表指定日期的指定日期部分的整数。
　　 select DATEPART(month, '2004-10-15') --返回 10
　　5. datename 返回代表指定日期的指定日期部分的字符串
　　 select datename(weekday, '2004-10-15') --返回：星期五
　　6. day(), month(),year() --可以与datepart对照一下
　　select 当前日期=convert(varchar(10),getdate(),120)
　　,当前时间=convert(varchar(8),getdate(),114)
　　select datename(dw,'2004-10-15')
　　select 本年第多少周=datename(week,'2004-10-15')
　　 ,今天是周几=datename(weekday,'2004-10-15')

三、SQL日期格式转换

以下是代码片段：

 Select CONVERT(varchar(100), GETDATE(), 23)
--yyyy-MM-dd
Select CONVERT(varchar(100), GETDATE(), 20)
--yyyy-MM-dd HH:mm:ss
select CONVERT(varchar, getdate(), 120 )
--2004-09-12 11:06:08
select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
--20040912110608
select CONVERT(varchar(12) , getdate(), 111 )
--2004/09/12
select CONVERT(varchar(12) , getdate(), 112 )
--20040912
select CONVERT(varchar(12) , getdate(), 102 )
--2004.09.12

0

Feb 22 2006 4:26PM CONVERT(CHAR(19), CURRENT_TIMESTAMP, 0)1 02/22/06 CONVERT(CHAR(8), CURRENT_TIMESTAMP, 1)2 06.02.22 CONVERT(CHAR(8), CURRENT_TIMESTAMP, 2)3 22/02/06 CONVERT(CHAR(8), CURRENT_TIMESTAMP, 3)4 22.02.06 CONVERT(CHAR(8), CURRENT_TIMESTAMP, 4)5 22-02-06 CONVERT(CHAR(8), CURRENT_TIMESTAMP, 5)6 22 Feb 06 CONVERT(CHAR(9), CURRENT_TIMESTAMP, 6)7 Feb 22, 06 CONVERT(CHAR(10), CURRENT_TIMESTAMP, 7)8 16:26:08 CONVERT(CHAR(8), CURRENT_TIMESTAMP, 8)9 Feb 22 2006 4:26:08:020PM CONVERT(CHAR(26), CURRENT_TIMESTAMP, 9)10 02-22-06 CONVERT(CHAR(8), CURRENT_TIMESTAMP, 10)11 06/02/22 CONVERT(CHAR(8), CURRENT_TIMESTAMP, 11)12 060222 CONVERT(CHAR(6), CURRENT_TIMESTAMP, 12)13 22 Feb 2006 16:26:08:020 CONVERT(CHAR(24), CURRENT_TIMESTAMP, 13)14 16:26:08:037 CONVERT(CHAR(12), CURRENT_TIMESTAMP, 14)20 2006-02-22 16:26:08 CONVERT(CHAR(19), CURRENT_TIMESTAMP, 20)21 2006-02-22 16:26:08.037 CONVERT(CHAR(23), CURRENT_TIMESTAMP, 21)22 02/22/06 4:26:08 PM CONVERT(CHAR(20), CURRENT_TIMESTAMP, 22)23 2006-02-22 CONVERT(CHAR(10), CURRENT_TIMESTAMP, 23)24 16:26:08 CONVERT(CHAR(8), CURRENT_TIMESTAMP, 24)25 2006-02-22 16:26:08.037 CONVERT(CHAR(23), CURRENT_TIMESTAMP, 25)100 Feb 22 2006 4:26PM CONVERT(CHAR(19), CURRENT_TIMESTAMP, 100)101 02/22/2006 CONVERT(CHAR(10), CURRENT_TIMESTAMP, 101)102 2006.02.22 CONVERT(CHAR(10), CURRENT_TIMESTAMP, 102)103 22/02/2006 CONVERT(CHAR(10), CURRENT_TIMESTAMP, 103)104 22.02.2006 CONVERT(CHAR(10), CURRENT_TIMESTAMP, 104)105 22-02-2006 CONVERT(CHAR(10), CURRENT_TIMESTAMP, 105)106 22 Feb 2006 CONVERT(CHAR(11), CURRENT_TIMESTAMP, 106)107 Feb 22, 2006 CONVERT(CHAR(12), CURRENT_TIMESTAMP, 107)108 16:26:08 CONVERT(CHAR(8), CURRENT_TIMESTAMP, 108)109 Feb 22 2006 4:26:08:067PM CONVERT(CHAR(26), CURRENT_TIMESTAMP, 109)110 02-22-2006 CONVERT(CHAR(10), CURRENT_TIMESTAMP, 110)111 2006/02/22 CONVERT(CHAR(10), CURRENT_TIMESTAMP, 111)112 20060222 CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112)113 22 Feb 2006 16:26:08:067 CONVERT(CHAR(24), CURRENT_TIMESTAMP, 113)114 16:26:08:067 CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114)120 2006-02-22 16:26:08 CONVERT(CHAR(19), CURRENT_TIMESTAMP, 120)121 2006-02-22 16:26:08.080 CONVERT(CHAR(23), CURRENT_TIMESTAMP, 121)126 2006-02-22T16:26:08.080 CONVERT(CHAR(23), CURRENT_TIMESTAMP, 126)127 2006-02-22T16:26:08.080 CONVERT(CHAR(23), CURRENT_TIMESTAMP, 127)130 24 ???? 1427 4:26:08:080PM CONVERT(CHAR(32), CURRENT_TIMESTAMP, 130)131 24/01/1427 4:26:08:080PM CONVERT(CHAR(25), CURRENT_TIMESTAMP, 131)

四、其它不常用的日期格式转换方法:

以下是代码片段：
select CONVERT(varchar(12) , getdate(), 101 )
　　 09/12/2004
　　 select CONVERT(varchar(12) , getdate(), 103 )
　　 12/09/2004
　　 select CONVERT(varchar(12) , getdate(), 104 )
　　 12.09.2004
　　 select CONVERT(varchar(12) , getdate(), 105 )
　　 12-09-2004
　　 select CONVERT(varchar(12) , getdate(), 106 )
　　 12 09 2004
　　 select CONVERT(varchar(12) , getdate(), 107 )
　　 09 12, 2004
　　 select CONVERT(varchar(12) , getdate(), 108 )
　　 11:06:08
　　 select CONVERT(varchar(12) , getdate(), 109 )
　　 09 12 2004 1
　　 select CONVERT(varchar(12) , getdate(), 110 )
　　 09-12-2004
　　 select CONVERT(varchar(12) , getdate(), 113 )
　　 12 09 2004 1
　　 select CONVERT(varchar(12) , getdate(), 114 )
　　 11:06:08.177

　　举例：

　　1.GetDate() 用于sql server :select GetDate()

　　2.DateDiff('s','2005-07-20','2005-7-25 22:56:32')返回值为 514592 秒

　　DateDiff('d','2005-07-20','2005-7-25 22:56:32')返回值为 5 天

　　3.DatePart('w','2005-7-25 22:56:32')返回值为 2 即星期一(周日为1，周六为7)

　　DatePart('d','2005-7-25 22:56:32')返回值为 25即25号

　　DatePart('y','2005-7-25 22:56:32')返回值为 206即这一年中第206天

　　DatePart('yyyy','2005-7-25 22:56:32')返回值为 2005即2005年

五、函数 

函数

参数/功能

GetDate( )

返回系统目前的日期与时间

DateDiff (interval,date1,date2)

以interval 指定的方式，返回date2 与date1两个日期之间的差值 date2-date1

DateAdd (interval,number,date)

以interval指定的方式，加上number之后的日期

DatePart (interval,date)

返回日期date中，interval指定部分所对应的整数值

DateName (interval,date)

返回日期date中，interval指定部分所对应的字符串名称 

六、参数 interval的设定值如下： 

值

缩 写（Sql Server）

Access 和 ASP

说明

Year

Yy

yyyy

年 1753 ~ 9999

Quarter

Qq

q

季 1 ~ 4

Month

Mm

m

月1 ~ 12

Day of year

Dy

y

一年的日数,一年中的第几日 1-366

Day

Dd

d

日，1-31

Weekday

Dw

w

一周的日数，一周中的第几日 1-7

Week

Wk

ww

周，一年中的第几周 0 ~ 51

Hour

Hh

h

时0 ~ 23

Minute

Mi

n

分钟0 ~ 59

Second

Ss

s

秒 0 ~ 59

Millisecond

Ms

-

毫秒 0 ~ 999

七、常用时间总结

--1.一个月第一天的
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
--2.本周的星期一
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
select dateadd(wk,datediff(wk,0,getdate()),6)
--3.一年的第一天
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
--4.季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
--5.当天的半夜
SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
--6.上个月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
--7.去年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
--8.本月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
--9.本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))
--10.本月的第一个星期一
select DATEADD(wk,
DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)
--查询本周注册人数
select count(*) from [user]
where datediff(week,create_day-1,getdate())=0
--上周注册人数
select count(*) from [user]
where datediff(week,create_day-1,getdate())=1
--本月注册人数
select count(*) from [user]
where datediff(month,create_day,getdate())=0
--上月注册人数
select count(*) from [user]
where datediff(month,create_day,getdate())=1
--如果要效率,这样写查询
--查询本周注册人数
select count(*) from [user]
where create_day>=dateadd(day,2-datepart(weekday,getdate()),convert(varchar,getdate(),112))
 and create_day<dateadd(day,9-datepart(weekday,getdate()),convert(varchar,getdate(),112))
--上周注册人数
select count(*) from [user]
where create_day>=dateadd(day,-5-datepart(weekday,getdate()),convert(varchar,getdate(),112))
 and create_day<dateadd(day,2-datepart(weekday,getdate()),convert(varchar,getdate(),112))
--本月注册人数
select count(*) from [user]
where create_day>=dateadd(day,1-day(getdate()),convert(varchar,getdate(),112))
 and create_day<dateadd(month,1,dateadd(day,1-day(getdate()),convert(varchar,getdate(),112)))
--上月注册人数
select count(*) from [user]
where create_day>=dateadd(month,-1,dateadd(day,1-day(getdate()),convert(varchar,getdate(),112)))
 and create_day<dateadd(day,1-day(getdate()),convert(varchar,getdate(),112))