SQL SERVER日期格式與多國語言問題

kitesky發表於2005-06-23
SQLServer

資料庫language不同，sql server對日期格式的選擇也不同。

[@more@]

數字日期格式

Microsoft® SQL Server™ 2000 允許用指定的數字月份指定日期資料。例如，5/20/97 表示 1997 年 5 月的第 20 天，當使用數字日期格式時，在字串中以斜槓（/）、連字元（-）或句號（.）作為分隔符來指定月、日、年。字串必須以下面的形式出現：

數字 分隔符 數字 分隔符 數字 [時間] [時間]

下面的數字日期格式是有效的：

[0]4/15/[19]96 -- (mdy)
[0]4-15-[19]96 -- (mdy)
[0]4.15.[19]96 -- (mdy)
[04]/[19]96/15 -- (myd)
 
15/[0]4/[19]96 -- (dmy)
15/[19]96/[0]4 -- (dym)
[19]96/15/[0]4 -- (ydm)
[19]96/[04]/15 -- (ymd)

當語言被設定為 us_english 時，預設的日期順序是 mdy。可以使用 SET DATEFORMAT 語句改變日期的順序，根據所用的語言，它也會影響日期順序。

對 SET DATEFORMAT 的設定決定了如何解釋日期資料。如果順序和設定不匹配，則該值不會被解釋為日期（因為它們超出了範圍），或者被錯誤地解釋。例如，根據不同的 DATEFORMAT 設定，12/10/08 能被解釋為六種日期的一種。

字母日期格式

Microsoft® SQL Server™ 2000 允許使用以當前語言給出的月的全名（如 April）或月的縮寫（如 Apr）來指定日期資料；逗號是可選的，而且忽略大小寫。

以下是使用字母日期格式的一些指導：

  • 把日期和時間資料括在單引號中 (')。
  • 下面是 SQL Server 日期資料的合法字母格式（括號內的字元是可選字元）： 
·         Apr[il] [15][,] 1996 
·         Apr[il] 15[,] [19]96 
·         Apr[il] 1996 [15]
·          
·         [15] Apr[il][,] 1996 
·         15 Apr[il][,][19]96 
·         15 [19]96 apr[il]
·         [15] 1996 apr[il]
·          
·         1996 APR[IL] [15]
·         1996 [15] APR[IL]
  • 如果只指定年份的最後兩位數字，則小於 two digit year cutoff 配置選項值最後兩位數字的值與縮略形式的年份位於同一個世紀。大於或等於該選項值的值位於縮略形式年份的上一世紀。例如，如果 two digit year cutoff 為 2050（預設），則 25 被解釋為 2025，50 被解釋為 1950。為避免模糊，請使用四位數字的年份。
  • 如果沒有指定日，則預設值為當月第一天。
  • 當按字母形式指定月時，SET DATEFORMAT 會話設定不起作用。

使用日期和時間資料

Microsoft® SQL Server™ 2000 使用 datetime 和 smalldatetime 資料型別儲存日期和時間資料。

沒有隻儲存時間或日期的獨立的時間和日期資料型別。當設定 datetime 或 smalldatetime 值時，如果只指定時間，則日期預設為 1900 年 1 月 1 日。如果只指定日期，則時間預設為 12:00 AM（午夜）。

對於 datetime 資料，可以執行如下操作：

  • 鍵入新的資料或更改現有的資料。
  • 進行日期和時間的計算，比如日期的加或減。有關日期演算法的更多資訊，請參見 +（加）和 -（減）。
  • 搜尋特定的日期和/或時間。

可以使用系統日期函式進行一些關於 datetime 資料的算術運算。有關更多資訊，請參見函式。

下面是一些使用日期和時間資料的準則：

  • 若要對日期和時間進行精確搜尋匹配，請使用等號（=）。Microsoft SQL Server 以 12:00:00:000 AM（預設值）的精確時間形式返回年、月、日都完全匹配的日期和時間值。
  • 若要搜尋日期或時間的一部分，請使用 LIKE 運算子。SQL Server 首先把資料轉換為 datetime 格式然後再轉換為 varchar 格式。由於標準時間顯示格式不包括秒和毫秒，不能使用 LIKE 和匹配模式對其搜尋，除非使用 CONVERT 函式並把 style 引數設為 9 或 109。有關部分日期和時間搜尋的更多資訊，請參見 LIKE。
  • SQL Server 在執行時計算 datetime 常量。用於某種語言期望的日期格式的日期字串，如果由使用不同語言和日期格式設定的連線來執行查詢，則可能無法識別。例如，下面的檢視對於語言設定為美國英語的連線可以工作正常，但對於其它語言設定的連線就不能工作正常： 
·                CREATE VIEW USA_Dates AS
·                SELECT *
·                FROM Northwind.dbo.Orders
·                WHERE OrderDate < 'May 1, 1997'

當在查詢中使用 datetime 常量，而且該查詢由使用不同語言設定的連線執行時，需確保日期對於所有語言設定均是可以接受的。對於國際資料庫中永久物件中的 datetime 常量，比如表約束和檢視 WHERE 子句，必須同樣小心。有關所有語言設定都解釋為相同的日期格式的更多資訊，請參見編寫國際 Transact-SQL 語句。

SQL Server 可以識別以下列格式括在單引號 (') 中的日期和時間：

  • 字母日期格式（例如，'April 15, 1998'）
  • 數字日期格式（例如，'4/15/1998'、'April 15，1998'）
  • 未分隔的字串格式（例如'19981207'、'December 12， 1998'）

編寫國際化 Transact-SQL 語句

如果遵從以下準則，則使用 Transact-SQL 語句的資料庫和資料庫應用程式將更便於在語言間進行移植，或將支援多種語言：

  • 用 ncha、nvarchar 和 ntext 代替所有的 char、varchar 和 text 資料型別。這樣就可以不必考慮內碼表的轉換問題。
  • 當執行月份和星期的比較與操作時，請使用數字日期，不要使用名稱字串。不同語言設定返回的月份和星期名稱是不同的。例如，當語言設定為美國英語時，DATENAME(MONTH,GETDATE()) 返回 May，而當語言設定為德語時，返回 Mai，語言設定為法語時則返回 mai。應使用以數字而非名稱表示月份的函式，如 DATEPART。當生成要顯示給使用者的結果集時，可使用DATEPART名稱，因為日期名稱通常比數字表示形式更有意義；但是，編寫邏輯程式碼時不要使用任何依賴於特定語言顯示的名稱。
  • 當指定用於比較操作的日期或者用於 INSERT 或 UPDATE 語句的輸入的日期時，請使用對於所有的語言設定解釋都相同的常量：
    • ADO、OLE DB 和 ODBC 應用程式應該使用以下的 ODBC 時間戳、日期和時間轉義子句：
      { ts 'yyyy-mm-dd hh:mm:ss[.fff] '}，例如 { ts '1998-09-24 10:02:20' }
      { d 'yyyy-mm-dd'}，例如 { d '1998-09-24' }
      { t 'hh:mm:ss'}，例如 { t '10:02:20'}
    • 使用其它 API 的應用程式或 Transact-SQL 指令碼、儲存過程和觸發器都應該使用連在一起的數字字串（例如 yyyymmdd 為 19980924）。
    • 使用其它 API 的應用程式或 Transact-SQL 指令碼、儲存過程和觸發器都應該使用帶有顯式引數的 CONVERT 語句，以進行 date 和 smalldate 資料型別及字串資料型別之間的所有轉換。例如，下列語句的解釋對於所有語言或日期格式連線設定都是相同的： 
·         SELECT *
·         FROM Northwind.dbo.Orders
·         WHERE OrderDate = CONVERT(DATETIME, '7/19/1996', 101)

SET DATEFORMAT

設定用於輸入 datetime 或 smalldatetime 資料的日期部分（月/日/年）的順序。

語法

SET DATEFORMAT { format | @format_var }

引數

format | @format_var

是日期部分的順序。可以是 Unicode 或轉換為 Unicode 的 DBCS。有效引數包括 mdy、dmy、ymd、ydm、myd 和 dym。美國英語預設值是 mdy。

註釋

該設定僅用在將字串轉換為日期值時的解釋中。它對日期值的顯示沒有影響。

SET DATEFORMAT 的設定是在執行或執行時設定，而不是在分析時設定。

許可權

SET DATEFORMAT 許可權預設授予所有使用者。

示例

下例使用不同的日期格式處理不同格式的日期字串。

SET DATEFORMAT mdy
GO
DECLARE @datevar datetime
SET @datevar = '12/31/98'
SELECT @datevar
GO
 
SET DATEFORMAT ydm
GO
DECLARE @datevar datetime
SET @datevar = '98/31/12'
SELECT @datevar
GO
 
SET DATEFORMAT ymd
GO
DECLARE @datevar datetime
SET @datevar = '98/12/31'
SELECT @datevar
GO

返回當前連線的活動（設定）的 SET 選項

DBCC USEROPTIONS

報告某個備用語言或所有語言的資訊

sp_helplanguage

或者

use master

go

select * from syslanguages

go

在使用SQL Server下如何程式能在不同的國家(地區)中執行正常

在使用SQL Server下如何讓您的程式能在不同的國家(地區)中咦髡??/span>撇開Unicode不談, 日期格式的差異會是撰寫程式時另一個最需要重視的地方不同地區與文化日期各有其差異, 如:美國us: M/d/y H:m:d, 義大利it: d/M/y H.m.d, 英國uk: d/M/y H:m:d, 臺灣tw: y/M/d H:m:d, 中國cn: y-M-d H:m:d首先必須統一OS日期寫入格式如: Now.ToString("yyyy/MM/dd HH:mm:ss") 在SQL Server預設中日期格式為 Mdy, 此時在預設未修正程式的情況下寫入SQL
it/uk 會因為如:13/02/2004 無法正確判斷是月或日而寫入失敗, tw/cn 則能夠自動轉換而不會出錯這時我們在SQL的執行時期更改SET DATEFORMAT ymd, 如此一來就與Now.ToString("yyyy/MM/dd HH:mm:ss")是一致了, uk已可正常寫入但 it 卻仍失敗, 探索原因在於it的region時間格式是 H.m.d, 雖然 Now.ToString("yyyy/MM/dd HH:mm:ss")已指定:為時間分隔符號, 但仍會因文化特性而自動轉換為OS內建格式(Region and Language)為此我們再做更改 Now.ToString("yyyy/MM/dd HH:mm:ss", System.Globalization.DateTimeFormatInfo.InvariantInfo))使其日期輸出與文化特性無關(即不受OS日期格式設定所影響), 總結上面的說法:
1. 統一日期輸入格式 Now.ToString("yyyy/MM/dd HH:mm:ss")
2. 更改 SQL 執行時期日期格式 SET DATEFORMAT ymd
3. 統一日期輸入格式與文化無關 Now.ToString("yyyy/MM/dd HH:mm:ss", System.Globalization.DateTimeFormatInfo.InvariantInfo))如此一來所有地區的日期與時間型態都能夠正常支援了.註: yyyy/MM/dd HH:mm:ss只是一個範例參考, 主要只需與SQL的 DATEFORMAT一致即可。

另外，使用AddParameter將能自動的把.net的日期格式轉換成與SQL Server伺服器一致的日期格式。更加方便快捷。AddParameter("@delete_date", (long)System.Data.SqlDbType.DateTime, System.DateTime.Now);

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/66009/viewspace-800869/，如需轉載，請註明出處，否則將追究法律責任。

相關文章