主頁 > 知識庫 > SQL Server 日期相關資料詳細介紹

SQL Server 日期相關資料詳細介紹

熱門標簽:威海智能語音外呼系統(tǒng) 400電話申請需要開戶費嗎 智能語音外呼系統(tǒng)哪個牌子好 山西語音外呼系統(tǒng)價格 溫州語音外呼系統(tǒng)代理 重慶防封電銷機器人供應商 西安青牛防封電銷卡 南京電銷外呼系統(tǒng)運營商 北京辦理400電話多少

一、日期類型:

對于SQL Server 2008 來說(因為2000甚至2005已經(jīng)稍微有被淘汰的跡象,所以在此不作過多說明,加上自己工作使用的是2008R2。所以不保證08以前的能用),日期類型有:

數(shù)據(jù)類型

格式

范圍

精確度

存儲大?。ㄒ宰止?jié)為單位)

用戶定義的秒的小數(shù)精度

時區(qū)偏移量

time

 

hh:mm:ss[.

nnnnnnn]

00:00:00.0000000 到 23:59:59.9999999

100 納秒

3 到 5

date

 

YYYY-MM-DD

0001-01-01 到 9999-12-31

1 天

3

smalldatetime

 

YYYY-MM-DD hh:mm:ss

1900-01-01 到 2079-06-06

1 分鐘

4

datetime

 

YYYY-MM-DD hh:mm:ss[.

nnn]

1753-01-01 到 9999-12-31

0.00333 秒

8

datetime2

 

YYYY-MM-DD hh:mm:ss[.

nnnnnnn]

0001-01-01 00:00:00.0000000 到 9999-12-31 23:59:59.9999999

100 納秒

6 到 8

datetimeoffset

 

YYYY-MM-DD hh:mm:ss[.

nnnnnnn] [+|-]hh:mm

0001-01-01 00:00:00.0000000 到 9999-12-31 23:59:59.9999999(以 UTC 時間表示)

100 納秒

8 到 10

注意:所有系統(tǒng)日期和時間值均得自運行 SQL Server 實例的計算機的操作系統(tǒng)。

每一種日期類型有其使用范圍,當然以剛好適用為最佳選擇,切記不要為了方便,什么都用datetime類型。從性能方面會有很大影響,舉個例子:

一個表,有1億行的數(shù)據(jù),有10列日期型(對于一些歷史表來說這是完全有可能的)。如果全部使用datetime,那么光這部分的存儲空間就是:10*100000000*8字節(jié)/(1024*1024)≈7629M≈7.4G,當然,如果有這樣的需要,再大也還是要用的,假設其實業(yè)務上不需要那么精確(因為datetime是精確到0.00333秒),只需要精確到1分鐘即可,那么毫不猶豫使用smalldatetime,可以減少一半的空間,也就是大約3.7G。減少空間的好處有很多,比如備份及數(shù)據(jù)庫文件的大小可以減少,讓有限的預算做更多的事情。而且數(shù)據(jù)頁固定8KB,越少的體積單頁能存放的數(shù)據(jù)也就越多,查詢時要訪問的頁面就更少,緩解I/O壓力。同時對索引的使用也更有效,等等。

所以這里就能體現(xiàn)出“設計”的重要性。

二、日期函數(shù):日期函數(shù)是處理日期的基礎,牢記日期函數(shù)能減少很多編程工作

精度較高的系統(tǒng)日期和時間函數(shù)

精確程度取決于運行 SQL Server 實例的計算機硬件和 Windows 版本。標注有:2012有效的是只有2012才出現(xiàn)的功能

函數(shù)

語法

返回值

返回數(shù)據(jù)類型

確定性

SYSDATETIME

 

SYSDATETIME ()

返回包含計算機的日期和時間的 datetime2(7) 值,SQL Server 的實例正在該計算機上運行。

時區(qū)偏移量未包含在內。

datetime2(7)

不具有確定性

SYSDATETIMEOFFSET

 

SYSDATETIMEOFFSET ( )

返回包含計算機的日期和時間的 datetimeoffset(7) 值,SQL Server 的實例正在該計算機上運行。

時區(qū)偏移量包含在內。

datetimeoffset(7)

不具有確定性

SYSUTCDATETIME

 

SYSUTCDATETIME ( )

返回包含計算機的日期和時間的 datetime2(7) 值,SQL Server 的實例正在該計算機上運行。

日期和時間作為 UTC 時間(通用協(xié)調時間)返回。

datetime2(7)

不具有確定性

精度較低的系統(tǒng)日期和時間函數(shù)

函數(shù)

語法

返回值

返回數(shù)據(jù)類型

確定性

CURRENT_TIMESTAMP

 

CURRENT_TIMESTAMP

返回包含計算機的日期和時間的 datetime2(7) 值,SQL Server 的實例正在該計算機上運行。

時區(qū)偏移量未包含在內。

datetime

不具有確定性

GETDATE

 

GETDATE ( )

返回包含計算機的日期和時間的 datetime2(7) 值,SQL Server 的實例正在該計算機上運行。

時區(qū)偏移量未包含在內。

datetime

不具有確定性

GETUTCDATE

 

GETUTCDATE ( )

返回包含計算機的日期和時間的 datetime2(7) 值,SQL Server 的實例正在該計算機上運行。

日期和時間作為 UTC 時間(通用協(xié)調時間)返回。

datetime

不具有確定性

用來獲取日期和時間部分的函數(shù)

函數(shù)

語法

返回值

返回數(shù)據(jù)類型

確定性

DATENAME

 

DATENAME ( datepart , date )

返回表示指定日期的指定 datepart 的字符串。

nvarchar

不具有確定性

DATEPART

 

DATEPART ( datepart , date )

返回表示指定 date 的指定 datepart 的整數(shù)。

int

不具有確定性

DAY

 

DAY ( date )

返回表示指定 date 的“日”部分的整數(shù)。

int

具有確定性

MONTH

 

MONTH ( date )

返回表示指定 date 的“月”部分的整數(shù)。

int

具有確定性

YEAR

 

YEAR ( date )

返回表示指定 date 的“年”部分的整數(shù)。

int

具有確定性

用來從部件中獲取日期和時間值的函數(shù)

函數(shù)

語法

返回值

返回數(shù)據(jù)類型

確定性

DATEFROMPARTS(2012有效)

 

DATEFROMPARTS ( yearmonthday )

返回表示指定年、月、日的 date 值。

date

具有確定性

DATETIME2FROMPARTS(2012有效)

 

DATETIME2FROMPARTS ( yearmonthdayhourminuteseconds,fractionsprecision)

對指定的日期和時間返回 datetime2 值(具有指定精度)。

datetime2(precision)

具有確定性

DATETIMEFROMPARTS(2012有效)

 

DATETIMEFROMPARTS ( yearmonthdayhourminuteseconds,milliseconds )

為指定的日期和時間返回 datetime 值。

datetime

具有確定性

DATETIMEOFFSETFROMPARTS(2012有效)

 

DATETIMEOFFSETFROMPARTS ( yearmonthdayhourminute,secondsfractionshour_offsetminute_offsetprecision )

對指定的日期和時間返回 datetimeoffset值,即具有指定的偏移量和精度。

datetime(precision)

具有確定性

SMALLDATETIMEFROMPARTS(2012有效)

 

SMALLDATETIMEFROMPARTS ( yearmonthdayhourminute )

為指定的日期和時間返回 smalldatetime值。

smalldatetime

具有確定性

TIMEFROMPARTS(2012有效)

 

TIMEFROMPARTS ( hourminutesecondsfractionsprecision )

對指定的時間返回 time 值(具有指定精度)。

time(precision)

具有確定性

用來獲取日期和時間差的函數(shù)

函數(shù)

語法

返回值

返回數(shù)據(jù)類型

確定性

DATEDIFF

 

DATEDIFF ( datepart , startdate , enddate )

返回兩個指定日期之間所跨的日期或時間 datepart 邊界的數(shù)目。

int

具有確定性

用來修改日期和時間值的函數(shù)

函數(shù)

語法

返回值

返回數(shù)據(jù)類型

確定性

DATEADD

 

DATEADD ( datepart , number date )

通過將一個時間間隔與指定 date 的指定 datepart 相加,返回一個新的 datetime 值。

date 參數(shù)的數(shù)據(jù)類型。

具有確定性

EOMONTH(2012)

 

EOMONTH ( start_date [,month_to_add ] )

返回包含指定日期的月份的最后一天(具有可選偏移量)。

返回類型為 start_date 的類型或datetime2(7)。

具有確定性

SWITCHOFFSET

 

SWITCH OFFSET ( DATETIMEOFFSETtime_zone)

SWITCH OFFSET 更改 DATETIMEOFFSET 值的時區(qū)偏移量并保留 UTC 值。

datetimeoffset 具有其小數(shù)精度:DATETIMEOFFSET

具有確定性

TODATETIMEOFFSET

 

TODATETIMEOFFSET ( expression ,time_zone)

TODATETIMEOFFSET 將 datetime2 值轉換為 datetimeoffset 值。

datetime2 值被解釋為指定 time_zone 的本地時間。

具有 datetime 參數(shù)的小數(shù)精度的datetimeoffset

具有確定性

用來設置或獲取會話格式的函數(shù)

函數(shù)

語法

返回值

返回數(shù)據(jù)類型

確定性

@@DATEFIRST

 

@@DATEFIRST

返回對會話進行 SET DATEFIRST 操作所得結果的當前值。

tinyint

不具有確定性

SET DATEFIRST

 

SET DATEFIRST { number

|

@number_var }

將一周的第一天設置為從 1 到 7 的一個數(shù)字。

不適用

不適用

SET DATEFORMAT

 

SET DATEFORMAT { format

|

@format_var }

設置用于輸入 datetime 或 smalldatetime 數(shù)據(jù)的日期各部分(月/日/年)的順序。

不適用

不適用

@@LANGUAGE

 

@@LANGUAGE

返回當前使用的語言的名稱。

@@LANGUAGE 不是日期或時間函數(shù)。但是,語言設置會影響日期函數(shù)的輸出。

不適用

不適用

SET LANGUAGE

 

SET LANGUAGE { [ N ] 'language'

|

@language_var }

設置會話和系統(tǒng)消息的語言環(huán)境。

SET LANGUAGE 不是日期或時間函數(shù)。

但是,語言設置會影響日期函數(shù)的輸出。

不適用

不適用

sp_helplanguage

 

sp_helplanguage [ [ @language = ]'language' ]

返回有關所有支持語言的日期格式的信息。

sp_helplanguage 不是日期或時間存儲過程。

但是,語言設置會影響日期函數(shù)的輸出。

不適用

不適用

用來驗證日期和時間值的函數(shù)

函數(shù)

語法

返回值

返回數(shù)據(jù)類型

確定性

ISDATE

 

ISDATE (expression )

確定 datetime 或 smalldatetime 輸入表達式是否為有效的日期或時間值。

int

只有與 CONVERT 函數(shù)一起使用,同時指定了 CONVERT 樣式參數(shù)且樣式不等于 0、100、9 或 109 時,ISDATE 才是確定的。

三、日期操作詳解:

3.1、注意:SQL Server 將 0 解釋為 1900 年 1 月 1 日。

3.2、對于一些當前會話需要臨時改變日期設置時,可以使用SET關鍵字改變:

SET DATEFIRST { number | @number_var }:

將一周的第一天設置為從 1 到 7 的一個數(shù)字。7為默認的第一天,即周日。查看 SET DATEFIRST 的當前設置,請使用 @@DATEFIRST函數(shù)。注意此值是在運行時執(zhí)行

SET DATEFORMAT{format|@format_var}:

   設置用于解釋 date、smalldatetime、datetime、datetime2 和 datetimeoffset 字符串的月、日和年日期部分的順序。有效參數(shù)為 mdy、 dmy、 ymd、 ydm、 myd 和 dym默認值為 mdy。SETDATEFORMAT 將覆蓋 SETLANGUAGE 的隱式日期格式設置。

設置會話語言:雖然這里是語言,但是會影響日期的格式:

SET LANGUAGE Italian;

GO

SELECT @@DATEFIRST;

GO

SET LANGUAGE us_english;

GO

SELECT @@DATEFIRST;

         3.3、常用函數(shù)操作:

函數(shù)及其參數(shù)

描述

DAY ( date )

返回一個整數(shù),該整數(shù)表示指定的 date 是該月份的哪一天,DAY 與 DATEPART ( day、 date) 返回相同的值。 如果 date 只包含時間部分,則返回值為 1,即基準日

YEAR ( date )

返回一個整數(shù),它表示指定 date 的年份, YEAR 與 DATEPART ( year, date) 返回相同的值。如果 date 僅包含一個時間部分,則返回值為 1900,即基準年

CURRENT_TIMESTAMP

與getdate()相同

DATENAME (datepart, date )

返回表示指定 date 的指定 datepart 的字符串,DATENAME 可用于選擇列表 WHERE、HAVING、GROUP BY 和 ORDER BY 子句中

DATEDIFF (datepart, startdate , enddate )

返回指定的 startdate 和 enddate 之間所跨的指定 datepart 邊界的計數(shù)(帶符號的整數(shù))。

DATEADD (datepart , number , date )

指定 number 時間間隔(有符號整數(shù))與指定 date 的指定 datepart 相加后,返回該 date

ISDATE ( expression )

如果 expression 是有效的 date、 time 或 datetime 值,則返回 1;否則,返回 0

SWITCHOFFSET (DATETIMEOFFSET, time_zone )

返回從存儲的時區(qū)偏移量變?yōu)橹付ǖ男聲r區(qū)偏移量時得到的 datetimeoffset 值

四、常用日期處理案例:這是文章的重點,因為上面大部分內容都可以從聯(lián)機叢書中查到

給定某個日期,計算相關的值,目前我的工作中遇到比較多的就是這些,至于有些特殊歷法所需日期,目前沒遇到,所以沒總結:

復制代碼 代碼如下:

--定義給定的一天
DECLARE @Date DATETIME = GETDATE();

SELECT @Date AS '目前時間'
,DATEADD(DD,-1,@Date) AS '前一天'
,DATEADD(DD,1,@Date) AS '后一天'
/*月計算*/
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0) AS '月初'--在SQL Server中0 代表1900-01-01,通過月運算,保證日恒久為1號
,DATEADD(DD,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精確到天)'--找到下月初再扣減1天,建議使用DATEADD而不要直接“-1”
,DATEADD(SS,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精確到datetime的小數(shù)位)'
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)-1,0) AS '上月第一天'
,DATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) AS '上月最后一天'
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)+1,0) AS '下月第一天'
,DATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))) AS '下月最后一天'
/*周計算*/
,DATEADD(WEEKDAY,1-DATEPART(WEEKDAY,@Date),@Date) AS '本周第一天(周日)'--注意此處與@@datefirst的值有關
,DATEADD(WEEK,DATEDIFF(WEEK,-1,@Date),-1) AS '所在星期的星期日'--注意此處與@@datefirst的值有關
,DATEADD(DAY,2-DATEPART(WEEKDAY,@Date),@Date) AS '所在星期的第二天'--注意此處與@@datefirst的值有關,其他天數(shù)類推
,DATEADD(WEEK,-1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '上個星期第一天(周日)'--注意此處與@@datefirst的值有關
,DATEADD(WEEK,1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '下個星期第一天(星期日)'--注意此處與@@datefirst的值有關
,DATENAME(WEEKDAY,@Date) AS '本日是周幾'
,DATEPART(WEEKDAY,@Date) AS '本日是周幾'--返回值 1-星期日,2-星期一,3-星期二......7-星期六
/*年度計算*/
,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0) AS '年初'
,DATEADD(YEAR,DATEDIFF(YEAR,-1,@Date),-1) AS '年末'
,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date)-1,0) AS '去年年初'
,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date),-1) AS '去年年末'
,DATEADD(YEAR,1+DATEDIFF(YEAR,0,@Date),0) AS '明年年初'
,DATEADD(YEAR,1+DATEDIFF(YEAR,-1,@Date),-1) AS '明年年末'
/*季度計算*/
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),0) AS '本季季初'
,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),-1) AS '本季季末'
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date)-1,0) AS '上季季初'
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),-1) AS '上季季末'
,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),0) AS '下季季初'
,DATEADD(QUARTER,2+DATEDIFF(QUARTER,0,@Date),-1) AS '下季季末'


五、建議:

創(chuàng)建時間維度表:在本人以前工作中,經(jīng)常需要查詢時間范圍(精確到天),此時,可以創(chuàng)建一個表,每一行對應一天,然后其他列就是所需日期,比如季初季末、月初月末、年初年末甚至上年下年等。以供直接調用,并且就算存10年的數(shù)據(jù),也就3000多條。有這樣需求的可以考慮使用。

六、速查手冊:日期往往要轉換成字符型再進行處理,所以這里貼出部分轉換結果

復制代碼 代碼如下:

Select CONVERT(varchar(100), GETDATE(), 0)--05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1)--05/16/06
Select CONVERT(varchar(100), GETDATE(), 2)--06.05.16
Select CONVERT(varchar(100), GETDATE(), 3)--16/05/06
Select CONVERT(varchar(100), GETDATE(), 4)--16.05.06
Select CONVERT(varchar(100), GETDATE(), 5)--16-05-06
Select CONVERT(varchar(100), GETDATE(), 6)--16 05 06
Select CONVERT(varchar(100), GETDATE(), 7)--05 16, 06
Select CONVERT(varchar(100), GETDATE(), 8)--10:57:46
Select CONVERT(varchar(100), GETDATE(), 9)--05 16 200610:57:46:827AM
Select CONVERT(varchar(100), GETDATE(), 10)--05-16-06
Select CONVERT(varchar(100), GETDATE(), 11)--06/05/16
Select CONVERT(varchar(100), GETDATE(), 12)--060516
Select CONVERT(varchar(100), GETDATE(), 13)--16 05 2006 10:57:46:937
Select CONVERT(varchar(100), GETDATE(), 14)--10:57:46:967
Select CONVERT(varchar(100), GETDATE(), 20)--2006-05-16 10:57:47
Select CONVERT(varchar(100), GETDATE(), 21)--2006-05-16 10:57:47.157
Select CONVERT(varchar(100), GETDATE(), 22)--05/16/06 10:57:47 AM
Select CONVERT(varchar(100), GETDATE(), 23)--2006-05-16
Select CONVERT(varchar(100), GETDATE(), 24)--10:57:47
Select CONVERT(varchar(100), GETDATE(), 25)--2006-05-16 10:57:47.250
Select CONVERT(varchar(100), GETDATE(), 100)--05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 101)--05/16/2006
Select CONVERT(varchar(100), GETDATE(), 102)--2006.05.16
Select CONVERT(varchar(100), GETDATE(), 103)--16/05/2006
Select CONVERT(varchar(100), GETDATE(), 104)--16.05.2006
Select CONVERT(varchar(100), GETDATE(), 105)--16-05-2006
Select CONVERT(varchar(100), GETDATE(), 106)--16 05 2006
Select CONVERT(varchar(100), GETDATE(), 107)--05 16, 2006
Select CONVERT(varchar(100), GETDATE(), 108)--10:57:49
Select CONVERT(varchar(100), GETDATE(), 109)--05 16 200610:57:49:437AM
Select CONVERT(varchar(100), GETDATE(), 110)--05-16-2006
Select CONVERT(varchar(100), GETDATE(), 111)--2006/05/16
Select CONVERT(varchar(100), GETDATE(), 112)--20060516
Select CONVERT(varchar(100), GETDATE(), 113)--16 05 2006 10:57:49:513
Select CONVERT(varchar(100), GETDATE(), 114)--10:57:49:547
Select CONVERT(varchar(100), GETDATE(), 120)--2006-05-16 10:57:49
Select CONVERT(varchar(100), GETDATE(), 121)--2006-05-16 10:57:49.700
Select CONVERT(varchar(100), GETDATE(), 126)--2006-05-16T10:57:49.827
Select CONVERT(varchar(100), GETDATE(), 130)--18 ???? ?????? 142710:57:49:907AM
Select CONVERT(varchar(100), GETDATE(), 131)--18/04/142710:57:49:920AM

您可能感興趣的文章:
  • SQL Server 日期函數(shù)CAST 和 CONVERT 以及在業(yè)務中的使用介紹
  • SQLserver中用convert函數(shù)轉換日期格式的方法
  • sqlserver獲取當前日期的最大時間值
  • sqlserver中如何查詢出連續(xù)日期記錄的代碼
  • sql 查詢本年、本月、本日記錄的語句,附SQL日期函數(shù)
  • Sqlserver 常用日期時間函數(shù)
  • 根據(jù)日期知道當天是星期幾的手動計算方法
  • 按日期選擇最晚日期與最早日期對應的數(shù)的差值
  • MSSQL 計算兩個日期相差的工作天數(shù)的語句
  • SQL Server自動生成日期加數(shù)字的序列號
  • SQL 返回期間內的所有日期
  • 數(shù)據(jù)庫 日期加減處理
  • sql語句中如何將datetime格式的日期轉換為yy-mm-dd格式
  • 在SQL中使用convert函數(shù)進行日期的查詢的代碼
  • SQL Server日期計算
  • SQL Server各種日期計算方法(收藏)
  • SqlServer中的日期與時間函數(shù)

標簽:宜春 黃山 新余 濟寧 金昌 中衛(wèi) 河源 貸款群呼

巨人網(wǎng)絡通訊聲明:本文標題《SQL Server 日期相關資料詳細介紹》,本文關鍵詞  SQL,Server,日期,相關,資料,;如發(fā)現(xiàn)本文內容存在版權問題,煩請?zhí)峁┫嚓P信息告之我們,我們將及時溝通與處理。本站內容系統(tǒng)采集于網(wǎng)絡,涉及言論、版權與本站無關。
  • 相關文章
  • 下面列出與本文章《SQL Server 日期相關資料詳細介紹》相關的同類信息!
  • 本頁收集關于SQL Server 日期相關資料詳細介紹的相關信息資訊供網(wǎng)民參考!
  • 推薦文章