Click here to Skip to main content
15,867,141 members
Articles / Database Development / SQL Server / SQL Server 2008

SQL Server Date Format Language

Rate me:
Please Sign up or sign in to vote.
4.67/5 (3 votes)
23 May 2012CPOL 36.7K   13   2
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)


Written By
Software Developer (Senior)
Colombia Colombia
SQLServer DBA, Oracle programmer, and MySQL DBA.
Software Developer/Architect 12 years ago. Supported languajes: VS2003, VS2005, VS2010 (C#, VB, ASP, MVC3), JQuery, C++, Gcc, TC, Mono, Cobol.
IT PMI Projects.

"The best thing about a boolean is even if you are wrong, you are only off by a bit"

Comments and Discussions

 
QuestionThank you - saved me hours! Pin
sarahemmm31-Dec-15 0:08
sarahemmm31-Dec-15 0:08 
GeneralProgramming is fun... Pin
ii_noname_ii24-May-12 0:22
ii_noname_ii24-May-12 0:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.