德瑞克：SQL Server 學習筆記: SQL Server：認識日期時間的分隔符號；搭配使用 SET DATEFORMAT、SET LANGUAGE

在使用日期時間資料時，若是搭配日期時間分隔符號，這可能需要由資料庫引擎來判讀。
例如：'01/02/3'

這是指何年？何月？何日？

請參考以下的範例程式碼：

EX1. 查詢：目前連線所使用的語言之日期相關資訊

-- 查詢：目前所用的語言與語言識別碼
USE master
GO
SELECT @@LANGUAGE N'目前所用的語言', @@LANGID N'目前所用語言的識別碼'
GO

-- 01_查詢：目前所用的語言與語言識別碼

-- 查詢：目前所用語言的日期相關資訊

/*
datefirst：每週第一天：1 代表星期一，2 代表星期二，依此類推，7 則代表星期日。
若以「繁體中文」為例，其預設的日期的順序是：ymd
*/
SELECT name N'語言名稱', dateformat N'日期的順序', datefirst N'每週第一天', *
FROM sys.syslanguages
WHERE langid = @@LANGID
GO

-- 02_查詢：目前所用語言的日期相關資訊

-- 測試：不同日期格式，依據預設的語言之日期順序：ymd
DECLARE @dt1 datetime, @dt2 datetime, @dt3 datetime
SET @dt1='01/02/03'
SET @dt2='01-02-03'
SET @dt3='010203' -- 沒有日期分隔符號
SELECT @dt1 N'日期', @dt2 N'日期', @dt3 N'日期'
GO

-- 03_測試：不同日期格式，依據預設的語言之日期順序：ymd

SELECT CAST('01/02/03' AS datetime) N'日期', 
 DATENAME(year, '01/02/03') N'年', DATENAME(month,'01/02/03') N'月', DATENAME(day, '01/02/03') N'日', 
 DATENAME(dayofyear,'01/02/03') N'一年中的第幾天', DATENAME(Weekday, '01/02/03') N'星期'
GO

-- 04_查詢：指定 datepart 的字元字串

EX2. 設定目前的工作階段的語言為：英文 us_english

-- 設定目前的工作階段的語言為：英文 us_english
SET LANGUAGE us_english
GO
SELECT @@LANGUAGE N'目前所用的語言', @@LANGID N'目前所用語言的識別碼'
GO

-- 05_設定目前的工作階段的語言為：英文 us_english

-- 查詢：目前所用語言的日期相關資訊
/*
datefirst：每週第一天：1 代表星期一，2 代表星期二，依此類推，7 則代表星期日。
若以「us_english」為例，其預設的日期的順序是：mdy
*/
SELECT name N'語言名稱', dateformat N'日期的順序', datefirst N'每週第一天', *
FROM sys.syslanguages
WHERE langid = @@LANGID
GO

-- 06_查詢：目前所用語言的日期相關資訊

-- 測試：不同日期格式，依據英文 us_english之日期順序：mdy
DECLARE @dt1 datetime, @dt2 datetime, @dt3 datetime

SET @dt1='01/02/03'
SET @dt2='01-02-03'
SET @dt3='010203' -- 沒有日期分隔符號

SELECT @dt1 N'日期', @dt2 N'日期', @dt3 N'日期'
GO

-- 07_測試：不同日期格式，依據英文 us_english之日期順序：mdy

-- 查詢：指定 datepart 的字元字串
SELECT CAST('01/02/03' AS datetime) N'日期', 
 DATENAME(year, '01/02/03') N'年', DATENAME(month,'01/02/03') N'月', DATENAME(day, '01/02/03') N'日', 
 DATENAME(dayofyear,'01/02/03') N'一年中的第幾天', DATENAME(Weekday, '01/02/03') N'星期'
GO

-- 08_查詢：指定 datepart 的字元字串

EX3. 日期資料，若不包含日期分隔符號

在使用日期資料時，若是不包含日期分隔符號，則系統將會忽略 SET DATEFORMAT 陳述式的組態。
都是使用：年、月、日的格式來解譯。

認識 SET DATEFORMAT (Transact-SQL)

設定解譯 date、smalldatetime、datetime、datetime2 和 datetimeoffset 字元字串之月份、日期與年份日期部分的順序。

DATEFORMAT ydm 不支援 date、datetime2 和 datetimeoffset 資料類型。

就 datetime 和 smalldatetime 值與 date、datetime2 和 datetimeoffset 值而言，DATEFORMAT 設定在解譯字元字串的作用方面可能會不同，端視字串格式而定。
這個設定會影響字元字串轉換成日期值以便儲存在資料庫中的解譯方式。

但是，它並不會影響儲存在資料庫中之日期資料類型值的顯示或儲存格式。

某些字元字串格式 (例如 ISO 8601) 的解譯就與 DATEFORMAT 設定無關。

SET DATEFORMAT 的設定是在執行階段進行設定，而不是在剖析階段進行設定。

SET DATEFORMAT 會覆寫 SET LANGUAGE 的隱含日期格式設定。

-- 設定為：年、月、日
SET DATEFORMAT ymd
DECLARE @dt1 datetime, @dt2 datetime
SELECT @dt1='010203', @dt2='01/02/03'
SELECT @dt1 '010203', @dt2 '01/02/03'
GO

-- 09_設定為：年、月、日

-- 設定為：月、日、年
SET DATEFORMAT mdy
DECLARE @dt1 datetime, @dt2 datetime
SELECT @dt1='010203', @dt2='01/02/03'
SELECT @dt1 '010203', @dt2 '01/02/03'
GO

-- 10_設定為：月、日、年

-- 設定為：日、月、年
SET DATEFORMAT dmy
DECLARE @dt1 datetime, @dt2 datetime
SELECT @dt1='010203', @dt2='01/02/03'
SELECT @dt1 '010203', @dt2 '01/02/03'
GO

-- 11_設定為：日、月、年

建議的寫法：

僅使用數字來呈現年、月、日，不要加上日期隔份分隔符號。

也就是使用以下的格式：
兩位數年：010203
四位數年：20010203

以下列出日期加上時間的完整表示格式：
YYYYMMDD HH:MI:SS[.mmmmmmm][+|-]HH:MI

德瑞克：SQL Server 學習筆記

2012-06-07

SQL Server：認識日期時間的分隔符號；搭配使用 SET DATEFORMAT、SET LANGUAGE

沒有留言:

張貼留言