sql中的日期和時間處理

1.sql中的日期和時間函數

函數                                    決定論
----------------------------------------------------------------

 DATEADD(datepart,number,date)              決定性
DATEDIFF(datepart,startdate,enddate)    決定性
DATENAME(datepart,date)                         非決定性
DATEPART(datepart,date)                         除了以 DATEPART (dw, date) 的方式使用之外，為決定性函數。 dw 為工作天，datepart 取決於 SET DATEFIRST 所設定的值 ，其中設定了一星期的第一天。
DAY(date)                                                     決定性
GETDATE()                                                  非決定性
GETUTCDATE()                                         非決定性
MONTH(date)                                             決定性
YEAR(date)                                                 決定性

下表所列為sqlserver所能辨識的日期部份與縮寫
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 微秒

使用示例:
--1.SELECT DATENAME(month, getdate()) AS 'Month Name' 返回值为 十一月

--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年

2.sql server日期格式转换
SQL Server中文版的默认的日期字段datetime格式是yyyy-mm-dd Thh:mm:ss.mmm
sql2000提供以下的列表的時間格式.
(yy) (yyyy)       標準               輸入/輸出**
----------------------------------------------------------------
-  0 或 100 (*)   預設值             mon dd yyyy hh:miAM (或 PM)
1  101            USA                       mm/dd/yy
2  102            ANSI                      yy.mm.dd
3  103            British/French     dd/mm/yy
4  104            德文                      dd.mm.yy
5  105            義大利文              dd-mm-yy
6  106            -                            dd mon yy
7  107            -                            Mon dd, yy
8  108            -                            hh:mm:ss
-  9 或 109 (*)   預設值 + 毫秒      mon dd yyyy hh:mi:ss:mmmAM (或 PM)
10 110            USA                         mm-dd-yy
11 111            JAPAN                     yy/mm/dd
12 112            ISO                          yymmdd
-  13 或 113 (*)  歐洲預設值 + 毫秒     dd mon yyyy hh:mm:ss:mmm(24h)
14 114            -                                        hh:mi:ss:mmm(24h)
-  20 或 120 (*)  ODBC 標準                 yyyy-mm-dd hh:mi:ss(24h)
-  21 或 121 (*)  ODBC 標準 (有毫秒) yyyy-mm-dd hh:mi:ss.mmm(24h)
-  126(***)       ISO8601                        yyyy-mm-dd Thh:mm:ss:mmm(no spaces)
-  130*           Kuwaiti                            dd mon yyyy hh:mi:ss:mmmAM
-  131*           Kuwaiti                            dd/mm/yy hh:mi:ss:mmmAM

可以通過convert得到我們要所需要的格式.
select CONVERT(varchar(12), getdate(),111)
顯示:2007/09/12

如果要轉化成自定義格式,則需要結合函數做更多的處理.
如:select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
顯示:20070912110608

3.常用的日期格式化处理

DECLARE @dt datetime
SET @dt=GETDATE()

--1．短日期格式：yyyy-m-d
SELECT REPLACE(CONVERT(varchar(10),@dt,120),N'-0','-')

--2．长日期格式：yyyy年mm月dd日
--A. 方法1
SELECT STUFF(STUFF(CONVERT(char(8),@dt,112),5,0,N'年'),8,0,N'月')+N'日'
--B. 方法2
SELECT DATENAME(Year,@dt)+N'年'+DATENAME(Month,@dt)+N'月'+DATENAME(Day,@dt)+N'日'

--3．长日期格式：yyyy年m月d日
SELECT DATENAME(Year,@dt)+N'年'+CAST(DATEPART(Month,@dt) AS varchar)+N'月'+DATENAME(Day,@dt)+N'日'

--4.完整日期+时间格式：yyyy-mm-dd hh:mi:ss:mmm
SELECT CONVERT(char(11),@dt,120)+CONVERT(char(12),@dt,114)

4.常用的日期推算处理

DECLARE @dt datetime
SET @dt=GETDATE()

DECLARE @number int
SET @number=3

--1．指定日期该年的第一天或最后一天
--A. 年的第一天
SELECT CONVERT(char(5),@dt,120)+'1-1'

--B. 年的最后一天
SELECT CONVERT(char(5),@dt,120)+'12-31'

--2．指定日期所在季度的第一天或最后一天
--A. 季度的第一天
SELECT CONVERT(datetime,
CONVERT(char(8),
DATEADD(Month,
DATEPART(Quarter,@dt)*3-Month(@dt)-2,
@dt),
120)+'1')

--B. 季度的最后一天（CASE判断法）
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)

--C. 季度的最后一天（直接推算法）
SELECT DATEADD(Day,-1,
CONVERT(char(8),
DATEADD(Month,
1+DATEPART(Quarter,@dt)*3-Month(@dt),
@dt),
120)+'1')

--3．指定日期所在月份的第一天或最后一天
--A. 月的第一天
SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+'1')

--B. 月的最后一天
SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1')

--C. 月的最后一天（容易使用的错误方法）
SELECT DATEADD(Month,1,DATEADD(Day,-DAY(@dt),@dt))

--4．指定日期所在周的任意一天
SELECT DATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)

--5．指定日期所在周的任意星期几
--A.  星期天做为一周的第1天
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)

--B.  星期一做为一周的第1天
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt)