SQL Server Date Format Language

Cristhian Selah

4.67/5 (3 votes)

23 May 2012CPOL

37.8K

Perform a conversion in a select, view, and Stored Procedure.

Introduction

Sometimes, we need to traslate a long date to another language.

Background

A few days ago, I needed to convert a long date format to another language which simply used the command 'set language' and functions 'cast' or 'convert'

Using the code

SQL

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

Finally, searching on Google I found a function to format DateTime types and Split function to divide strings using the sys.syslanguages SQLServer table. The basic idea was to format the string and then convert the target language by consulting the table of languages. Posting the functions found here: FormatDateTime & Split, and then finish with TQSL code conversion to another language

FormatDateTime function:

SQL

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 function:

SQL

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

Finally, the convert language function FormatDateTimeLang:

SQL

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

Using function:

SQL

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

Points of Interest

Using these functions we can perform a conversion in a select, view and stored procedure, because the command 'set language' can not be used in functions. Not supported by SQL Server.

History

I will apreciate any comments.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)