Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server / SQL-Server-2008

SQL Server Date Format Language

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)