Click here to Skip to main content
Click here to Skip to main content

SQL Server Date Format Language

, 23 May 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
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

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:

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: 

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:

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:

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)

Share

About the Author

Cristhian Selah
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

 
GeneralProgramming is fun... Pinmemberii_noname_ii24-May-12 0:22 
Programming is fun...
One exception is working with dates.
I haven't looked through the entire code yet, but, I already have one comment:
there is usually no good reason to retrieve dates as strings from the database.
I used to do all those crazy things with dates to strings...
But now, I like to keep a date as a date. If I really (reeeaaaally..: last resort) need a formatted string, I'll give it an alias and use it for display only.
 
The absolute best (IMO) is still to keep the real date type and work with that from the server or client side (and NOT the SQL).
PS: again, not fully analyzed, but if the split function used is the one I assume it us, you might need to handle the case where the splitted "thing" ends with the delimiter.

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.141015.1 | Last Updated 23 May 2012
Article Copyright 2012 by Cristhian Selah
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid