# VBForums CodeBank > CodeBank - Other >  SQL Server - Date Format Function in English and French

## CVMichael

Let me know if you have any problems with the function

This function can be easily modified for other languages. You may have problems if the words in Month or Day have letters the same as the date format, so you may have to make modifications for that.

sql Code:
-- =============================================
-- Author:      <Michael Ciurescu>
-- Create date: <20081105>
-- Description: Format a date the way you want for English and French, see examples below
--                1 / NULL - for English
--                2        - for French
-- =============================================
CREATE FUNCTION dbo.fnFormatDate
(
      @MyDate DATETIME
    , @Format NVARCHAR(50)
    , @Language INT = NULL
)
RETURNS NVARCHAR(255)
AS
BEGIN
    /*
        SELECT dbo.fnFormatDate(GetDate(), 'MMMM DDDD DD, YYYY HH:NN:SS AMPM', NULL)
        SELECT dbo.fnFormatDate(GetDate(), 'MMMM DDDD DD, YYYY HH:NN:SS AMPM', 2)
        SELECT dbo.fnFormatDate(GetDate(), 'YYYYMMDD HHNNSS', NULL)
        SELECT dbo.fnFormatDate(GetDate(), 'MM-DD-YY HH:NN:SS', NULL)
        SELECT dbo.fnFormatDate(GetDate(), 'M-D-YY H:N:S', NULL)
        SELECT dbo.fnFormatDate(GetDate(), 'M-D-YY H:N:S:MS', NULL)
        SELECT dbo.fnFormatDate('nov 1, 2008 15:01:02', 'MMMM DDDD D, YYYY H:N:S AMPM', 1)
        SELECT dbo.fnFormatDate('nov 1, 2008 15:01:02', 'MMMM DDDD D, YYYY H:N:S AMPM', 2)
        SELECT dbo.fnFormatDate('nov 1, 2008 15:01:02', 'MMMM DDDD DD, YYYY HH:NN:SS AMPM', NULL)
        SELECT dbo.fnFormatDate('nov 1, 2008 5:01:02', 'MMM DD, YYYY HH:NN:SS AMPM', NULL)
    */
    
    DECLARE @RetStr NVARCHAR(255)
    DECLARE @tMonth TABLE(MonthID INT, MonthStr NVARCHAR(20), Language INT)
    DECLARE @tWeekDay TABLE(WeekDayID INT, WeekDayStr NVARCHAR(20), Language INT)
    
    DECLARE @Year INT
    DECLARE @Month INT
    DECLARE @WeekDay INT
    DECLARE @Day INT
    
    DECLARE @Hour INT
    DECLARE @Minute INT
    DECLARE @Second INT
    DECLARE @MS INT
    
    IF @Language < 1 OR @Language > 2 RETURN NULL
    
    INSERT INTO @tWeekDay VALUES(1, 'Monday', 1)
    INSERT INTO @tWeekDay VALUES(2, 'Tuesday', 1)
    INSERT INTO @tWeekDay VALUES(3, 'Wednesday', 1)
    INSERT INTO @tWeekDay VALUES(4, 'Thursday', 1)
    INSERT INTO @tWeekDay VALUES(5, 'Friday', 1)
    INSERT INTO @tWeekDay VALUES(6, 'Saturday', 1)
    INSERT INTO @tWeekDay VALUES(7, 'Sunday', 1)
    
    INSERT INTO @tMonth VALUES(1, 'January', 1)
    INSERT INTO @tMonth VALUES(2, 'February', 1)
    INSERT INTO @tMonth VALUES(3, 'March', 1)
    INSERT INTO @tMonth VALUES(4, 'April', 1)
    INSERT INTO @tMonth VALUES(5, 'May', 1)
    INSERT INTO @tMonth VALUES(6, 'June', 1)
    INSERT INTO @tMonth VALUES(7, 'July', 1)
    INSERT INTO @tMonth VALUES(8, 'August', 1)
    INSERT INTO @tMonth VALUES(9, 'September', 1)
    INSERT INTO @tMonth VALUES(10, 'October', 1)
    INSERT INTO @tMonth VALUES(11, 'November', 1)
    INSERT INTO @tMonth VALUES(12, 'December', 1)
    
    INSERT INTO @tWeekDay VALUES(1, 'lundi', 2)
    INSERT INTO @tWeekDay VALUES(2, 'mardi', 2)
    INSERT INTO @tWeekDay VALUES(3, 'mercredi', 2)
    INSERT INTO @tWeekDay VALUES(4, 'jeudi', 2)
    INSERT INTO @tWeekDay VALUES(5, 'vendredi', 2)
    INSERT INTO @tWeekDay VALUES(6, 'samedi', 2)
    INSERT INTO @tWeekDay VALUES(7, 'dimanche', 2)
    
    INSERT INTO @tMonth VALUES(1, 'janvier', 2)
    INSERT INTO @tMonth VALUES(2, 'fevrier', 2)
    INSERT INTO @tMonth VALUES(3, 'mars', 2)
    INSERT INTO @tMonth VALUES(4, 'avril', 2)
    INSERT INTO @tMonth VALUES(5, 'mai', 2)
    INSERT INTO @tMonth VALUES(6, 'juin', 2)
    INSERT INTO @tMonth VALUES(7, 'juillet', 2)
    INSERT INTO @tMonth VALUES(8, 'aout', 2)
    INSERT INTO @tMonth VALUES(9, 'septembre', 2)
    INSERT INTO @tMonth VALUES(10, 'octobre', 2)
    INSERT INTO @tMonth VALUES(11, 'novembre', 2)
    INSERT INTO @tMonth VALUES(12, 'decembre', 2)
    
    SET @RetStr = @Format
    
    SET @Year = DATEPART(year, @MyDate)
    SET @Month = DATEPART(month, @MyDate)
    SET @WeekDay = DATEPART(weekday, @MyDate)
    SET @Day = DATEPART(day, @MyDate)
    
    SET @Hour = DATEPART(hour, @MyDate)
    SET @Minute = DATEPART(minute, @MyDate)
    SET @Second = DATEPART(second, @MyDate)
    SET @MS = DATEPART(millisecond, @MyDate)
    
    SELECT @RetStr = REPLACE(@RetStr, 'YYYY' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(4), @Year))
    SELECT @RetStr = REPLACE(@RetStr, 'YY' COLLATE Latin1_General_CS_AS, RIGHT(CONVERT(NVARCHAR(4), @Year), 2))
    SELECT @RetStr = REPLACE(@RetStr, ' MS' COLLATE Latin1_General_CS_AS, ' ' + CONVERT(NVARCHAR(5), @MS))
    SELECT @RetStr = REPLACE(@RetStr, ':MS' COLLATE Latin1_General_CS_AS, ':' + CONVERT(NVARCHAR(5), @MS))
    SELECT @RetStr = REPLACE(@RetStr, '.MS' COLLATE Latin1_General_CS_AS, '.' + CONVERT(NVARCHAR(5), @MS))
    SELECT @RetStr = REPLACE(@RetStr, 'MS' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(5), @MS))
    SELECT @RetStr = REPLACE(@RetStr, ' M ' COLLATE Latin1_General_CS_AS, ' ' + CONVERT(NVARCHAR(2), @Month) + ' ')
    SELECT @RetStr = REPLACE(@RetStr, ' M,' COLLATE Latin1_General_CS_AS, ' ' + CONVERT(NVARCHAR(2), @Month) + ',')
    SELECT @RetStr = REPLACE(@RetStr, '-M-' COLLATE Latin1_General_CS_AS, '-' + CONVERT(NVARCHAR(2), @Month) + '-')
    SELECT @RetStr = REPLACE(@RetStr, ' D ' COLLATE Latin1_General_CS_AS, ' ' + CONVERT(NVARCHAR(2), @Day) + ' ')
    SELECT @RetStr = REPLACE(@RetStr, ' D,' COLLATE Latin1_General_CS_AS, ' ' + CONVERT(NVARCHAR(2), @Day) + ',')
    SELECT @RetStr = REPLACE(@RetStr, '-D-' COLLATE Latin1_General_CS_AS, '-' + CONVERT(NVARCHAR(2), @Day) + '-')
    SELECT @RetStr = REPLACE(@RetStr, ':N:' COLLATE Latin1_General_CS_AS, ':' + CONVERT(NVARCHAR(2), @Minute) + ':')
    SELECT @RetStr = REPLACE(@RetStr, ':S ' COLLATE Latin1_General_CS_AS, ':' + CONVERT(NVARCHAR(2), @Second) + ' ')
    SELECT @RetStr = REPLACE(@RetStr, 'NN' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Minute), 2))
    SELECT @RetStr = REPLACE(@RetStr, 'SS' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Second), 2))
    SELECT @RetStr = REPLACE(@RetStr, ':S' COLLATE Latin1_General_CS_AS, ':' + CONVERT(NVARCHAR(2), @Second))
    
    SELECT @RetStr = REPLACE(@RetStr, 'MMMM' COLLATE Latin1_General_CS_AS, m.MonthStr)
    FROM @tMonth AS m
    WHERE m.MonthID = @Month AND m.Language = ISNULL(@Language, 1)
    
    SELECT @RetStr = REPLACE(@RetStr, 'MMM' COLLATE Latin1_General_CS_AS, LEFT(m.MonthStr, 3))
    FROM @tMonth AS m
    WHERE m.MonthID = @Month AND m.Language = ISNULL(@Language, 1)
    
    SELECT @RetStr = REPLACE(@RetStr, 'DDDD' COLLATE Latin1_General_CS_AS, w.WeekDayStr)
    FROM @tWeekDay AS w
    WHERE w.WeekDayID = @WeekDay AND w.Language = ISNULL(@Language, 1)
    
    SELECT @RetStr = REPLACE(@RetStr, 'DDD' COLLATE Latin1_General_CS_AS, LEFT(w.WeekDayStr, 3))
    FROM @tWeekDay AS w
    WHERE w.WeekDayID = @WeekDay AND w.Language = ISNULL(@Language, 1)
    
    SELECT @RetStr = REPLACE(@RetStr, 'MM' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Month), 2))
    SELECT @RetStr = REPLACE(@RetStr, 'M-' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(2), @Month) + '-')
    SELECT @RetStr = REPLACE(@RetStr, 'DD' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Day), 2))
    
    IF CHARINDEX('AMPM', @RetStr) > 0 BEGIN
        IF @Hour < 12 BEGIN
            SELECT @RetStr = REPLACE(@RetStr, 'HH' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Hour), 2))
            SELECT @RetStr = REPLACE(@RetStr, 'H' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(2), @Hour))
            SELECT @RetStr = REPLACE(@RetStr, 'AMPM' COLLATE Latin1_General_CS_AS, 'AM')
        END ELSE BEGIN
            SELECT @RetStr = REPLACE(@RetStr, 'HH' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Hour - 12), 2))
            SELECT @RetStr = REPLACE(@RetStr, 'H' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(2), @Hour - 12))
            SELECT @RetStr = REPLACE(@RetStr, 'AMPM' COLLATE Latin1_General_CS_AS, 'PM')
        END
    END ELSE BEGIN
        SELECT @RetStr = REPLACE(@RetStr, 'HH' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Hour), 2))
        SELECT @RetStr = REPLACE(@RetStr, 'H' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(2), @Hour))
    END
    
    RETURN @RetStr
END

----------

