SQL Server 日期格式语言

Cristhian Selah

4.67/5 (3投票s)

2012年5月23日

CPOL

38281

在select、视图和存储过程中执行转换。

介绍

有时，我们需要将长日期转换为另一种语言。

背景

几天前，我需要转换一个长日期格式为另一种语言，这只需要使用命令'setlanguage'和函数'cast'或'convert'

使用代码

set language German
select DATENAME(dw, getdate()) 
        + ',' + SPACE(1) + DATENAME(m, getdate()) 
        + SPACE(1) + CAST(DAY(getdate()) AS VARCHAR(2)) 
        + ',' + SPACE(1) + CAST(YEAR(getdate()) AS CHAR(4)) 
set language English

最后，在Google上搜索我找到了一个函数来格式化DateTime类型和Split函数来使用sys.syslanguagesSQLServer表分割字符串。基本想法是格式化字符串，然后通过查询语言表转换目标语言。在这里发布找到的函数：FormatDateTime&Split,然后用TQSL代码转换为另一种语言

FormatDateTime 函数

CREATE FUNCTION [dbo].[FormatDateTime] 
( 
    @dt DATETIME, 
    @format VARCHAR(50) 
) 
RETURNS VARCHAR(100) 
AS 
/*
select dbo.FormatDateTime(getdate(), 'LONGDATE')
select dbo.FormatDateTime(getdate(), 'ODBC')
*/
BEGIN 
    DECLARE @dtVC VARCHAR(100) 
    SELECT @dtVC = CASE @format 
 
    WHEN 'LONGDATE' THEN 
 
        DATENAME(dw, @dt) 
        + ',' + SPACE(1) + DATENAME(m, @dt) 
        + SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2)) 
        + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4)) 

    WHEN 'LONGDATE2' THEN 
 
        DATENAME(m, @dt) 
        + SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2)) 
        + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4)) 
 
    WHEN 'LONGDATE3' THEN 
 
        CAST(DAY(@dt) AS VARCHAR(2))
        + SPACE(1) +  DATENAME(m, @dt) 
        + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4)) 
 
    WHEN 'LONGDATEANDTIME' THEN 
 
        DATENAME(dw, @dt) 
        + ',' + SPACE(1) + DATENAME(m, @dt) 
        + SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2)) 
        + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4)) 
        + SPACE(1) + RIGHT(CONVERT(CHAR(20), 
        @dt - CONVERT(DATETIME, CONVERT(CHAR(8), 
        @dt, 112)), 22), 11) 
 
    WHEN 'SHORTDATE' THEN 
 
        LEFT(CONVERT(CHAR(19), @dt, 0), 11) 
 
    WHEN 'SHORTDATEANDTIME' THEN 
 
        REPLACE(REPLACE(CONVERT(CHAR(19), @dt, 0), 
            'AM', ' AM'), 'PM', ' PM') 
 
    WHEN 'UNIXTIMESTAMP' THEN 
 
        CAST(DATEDIFF(SECOND, '19700101', @dt) 
        AS VARCHAR(64)) 
 
    WHEN 'YYYYMMDD' THEN 
 
        CONVERT(CHAR(8), @dt, 112) 

    WHEN 'YYYY-DD-MM' THEN 

        CONVERT(VARCHAR(10), RIGHT(SPACE(4) + CONVERT(VARCHAR(4), YEAR(@dt)), 4)+ '-' +
                            (RIGHT('00' + CONVERT(varchar(2), DAY(@dt)), 2)+ '-' +
                             RIGHT('00' + CONVERT(varchar(2),MONTH(@dt)), 2)))
 
    WHEN 'YYYY-MM-DD' THEN 
 
        CONVERT(CHAR(10), @dt, 23) 
 
    WHEN 'YYMMDD' THEN 
 
        CONVERT(VARCHAR(8), @dt, 12) 
 
    WHEN 'YY-MM-DD' THEN 
 
        STUFF(STUFF(CONVERT(VARCHAR(8), @dt, 12), 
        5, 0, '-'), 3, 0, '-') 
 
    WHEN 'MMDDYY' THEN 
 
        REPLACE(CONVERT(CHAR(8), @dt, 10), '-', SPACE(0)) 
 
    WHEN 'MM-DD-YY' THEN 
 
        CONVERT(CHAR(8), @dt, 10) 
 
    WHEN 'MM/DD/YY' THEN 
 
        CONVERT(CHAR(8), @dt, 1) 
 
    WHEN 'MM/DD/YYYY' THEN 
 
        CONVERT(CHAR(10), @dt, 101) 
 
    WHEN 'DDMMYY' THEN 
 
        REPLACE(CONVERT(CHAR(8), @dt, 3), '/', SPACE(0)) 
 
    WHEN 'DD-MM-YY' THEN 
 
        REPLACE(CONVERT(CHAR(8), @dt, 3), '/', '-') 
 
    WHEN 'DD/MM/YY' THEN 
 
        CONVERT(CHAR(8), @dt, 3) 
 
    WHEN 'DD/MM/YYYY' THEN 
 
        CONVERT(CHAR(10), @dt, 103) 

    WHEN 'ODBC' THEN
 
        CONVERT(varchar(50), @dt,120)
         
    WHEN 'HH:MM:SS 24' THEN 
    
        CONVERT(CHAR(8), @dt, 8) 
 
    WHEN 'HH:MM 24' THEN 
 
        LEFT(CONVERT(VARCHAR(8), @dt, 8), 5) 

    WHEN 'HHMM 24' THEN 

        REPLACE(LEFT(CONVERT(VARCHAR(8), @dt, 8), 5),':','') 
 
    WHEN 'HH:MM:SS 12' THEN 
 
        LTRIM(RIGHT(CONVERT(VARCHAR(20), @dt, 22), 11)) 
 
    WHEN 'HH:MM 12' THEN 
 
        LTRIM(SUBSTRING(CONVERT( 
        VARCHAR(20), @dt, 22), 10, 5) 
        + RIGHT(CONVERT(VARCHAR(20), @dt, 22), 3)) 

    WHEN 'DD/MM/YYYY HH:MM 24' THEN 
 
        CONVERT(CHAR(10), @dt, 103) + ' ' + LEFT(CONVERT(VARCHAR(8), @dt, 8), 5)
 
    WHEN 'DD/MM/YYYY HH:MM 12' THEN 
    
        CONVERT(CHAR(10), @dt, 103) + ' ' + LTRIM(SUBSTRING(CONVERT(VARCHAR(20), @dt, 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20), @dt, 22), 3))
        
    WHEN 'DDMMYYYYHHMM' THEN 
    
        REPLACE(CONVERT(CHAR(10), @dt, 103), '/', '') + REPLACE(LEFT(CONVERT(VARCHAR(8), @dt, 8), 5), ':', '')

    WHEN 'DDMMYYYYHHMMSS' THEN 
    
        REPLACE(CONVERT(CHAR(10), @dt, 103), '/', '') + REPLACE(CONVERT(CHAR(8), @dt, 8) , ':', '')
        
    WHEN 'BINARY' THEN 
    
        CAST(@dt AS BINARY(8))
        
    ELSE 
 
        'Invalid format specified' 
 
    END 
    RETURN @dtVC 
END

Split 函数：

CREATE FUNCTION [dbo].[FN_Split]
(
  @sInputList VARCHAR(8000),
  @sDelimiter CHAR(1) = ','
) RETURNS @List TABLE (row [int] identity(0,1) not null, item VARCHAR(8000))
/**
select * from FN_Split('hello,world,this,is,a,test',',')
****/
BEGIN

DECLARE @sItem VARCHAR(8000)

SET @sItem = ''

WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
    SET @sItem= RTRIM(LTRIM(SUBSTRING(@sInputList,1, CHARINDEX(@sDelimiter, @sInputList, 0) - 1)))
    SET @sInputList= RTRIM(LTRIM(SUBSTRING(@sInputList, CHARINDEX(@sDelimiter, @sInputList, 0)+LEN(@sDelimiter),LEN(@sInputList))))
    INSERT INTO @List SELECT @sItem
END

INSERT INTO @List SELECT @sInputList

RETURN


END

最后，转换语言函数 FormatDateTimeLang

CREATE FUNCTION [dbo].[FormatDateTimeLang] 
( 
    @dt DATETIME, 
    @format VARCHAR(50),
    @lang VARCHAR(100)
) 
RETURNS VARCHAR(100) 
AS 
/*
Lang supported: English,German,French,Japanese,Danish,Spanish,Italian,Dutch,Norwegian,Portuguese,
        Finnish,Swedish,Czech,Hungarian,Polish,Romanian,Croatian,Slovak,Slovenian,Greek,
        Bulgarian,Russian,Turkish,British English,Estonian,Latvian,Lithuanian,Brazilian,
        Traditional Chinese,Korean,Simplified Chinese,Arabic,Thai
                      
select dbo.FormatDateTimeLang(getdate(), 'LONGDATE', 'German')
select @@language
select * from sys.syslanguages
select * from dbo.FN_Split('January,February,March,April,May,June,July,August,September,October,November,December',',')
*/
BEGIN 

DECLARE @Value VARCHAR(100)
DECLARE @ValueReplacement VARCHAR(100)
DECLARE @Ret VARCHAR(100)

IF NOT EXISTS(SELECT name
               FROM sys.syslanguages
                WHERE alias = @lang)
    RETURN dbo.FormatDateTime(@dt, @format)

SET @Ret = dbo.FormatDateTime(@dt, @format)

DECLARE curMonths CURSOR FOR
        SELECT Value = b.item, ValueReplacement = t.item
         FROM dbo.FN_Split((SELECT months FROM sys.syslanguages WHERE langid = @@langid),',') b
              INNER JOIN dbo.FN_Split((SELECT months FROM sys.syslanguages WHERE alias = @lang),',') t on b.row = t.row
OPEN curMonths
FETCH NEXT FROM curMonths INTO @Value, @ValueReplacement
WHILE @@FETCH_STATUS <> -1
BEGIN
    SET @Ret = REPLACE(@Ret, @Value, @ValueReplacement)
    FETCH NEXT FROM curMonths INTO @Value, @ValueReplacement
END
CLOSE curMonths
DEALLOCATE curMonths

DECLARE curShortMonths CURSOR FOR
        SELECT Value = b.item, ValueReplacement = t.item
         FROM dbo.FN_Split((SELECT shortmonths FROM sys.syslanguages WHERE langid = @@langid),',') b
              INNER JOIN dbo.FN_Split((SELECT shortmonths FROM sys.syslanguages WHERE alias = @lang),',') t on b.row = t.row
OPEN curShortMonths
FETCH NEXT FROM curShortMonths INTO @Value, @ValueReplacement
WHILE @@FETCH_STATUS <> -1
BEGIN
    SET @Ret = REPLACE(@Ret, @Value, @ValueReplacement)
    FETCH NEXT FROM curShortMonths INTO @Value, @ValueReplacement
END
CLOSE curShortMonths
DEALLOCATE curShortMonths

DECLARE curDays CURSOR FOR
        SELECT Value = b.item, ValueReplacement = t.item
         FROM dbo.FN_Split((SELECT days FROM sys.syslanguages WHERE langid = @@langid),',') b
              INNER JOIN dbo.FN_Split((SELECT days FROM sys.syslanguages WHERE alias = @lang),',') t on b.row = t.row
OPEN curDays
FETCH NEXT FROM curDays INTO @Value, @ValueReplacement
WHILE @@FETCH_STATUS <> -1
BEGIN
    SET @Ret = REPLACE(@Ret, @Value, @ValueReplacement)
    FETCH NEXT FROM curDays INTO @Value, @ValueReplacement
END
CLOSE curDays
DEALLOCATE curDays

RETURN @Ret

END

使用函数

select dbo.FormatDateTimeLang(getdate(), 'LONGDATE', 'German')

兴趣点

使用这些函数，我们可以执行 select、视图和存储过程中的转换，因为命令“set language”不能在函数中使用。 SQL Server 不支持。

历史

我会感谢任何评论。