Results 1 to 1 of 1

Thread: SQL Server - Date Format Function in English and French

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    SQL Server - Date Format Function in English and French

    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:
    1. -- =============================================
    2. -- Author:      <Michael Ciurescu>
    3. -- Create date: <20081105>
    4. -- Description: Format a date the way you want for English and French, see examples below
    5. --                1 / NULL - for English
    6. --                2        - for French
    7. -- =============================================
    8. CREATE FUNCTION dbo.fnFormatDate
    9. (
    10.       @MyDate DATETIME
    11.     , @Format NVARCHAR(50)
    12.     , @Language INT = NULL
    13. )
    14. RETURNS NVARCHAR(255)
    15. AS
    16. BEGIN
    17.     /*
    18.         SELECT dbo.fnFormatDate(GetDate(), 'MMMM DDDD DD, YYYY HH:NN:SS AMPM', NULL)
    19.         SELECT dbo.fnFormatDate(GetDate(), 'MMMM DDDD DD, YYYY HH:NN:SS AMPM', 2)
    20.         SELECT dbo.fnFormatDate(GetDate(), 'YYYYMMDD HHNNSS', NULL)
    21.         SELECT dbo.fnFormatDate(GetDate(), 'MM-DD-YY HH:NN:SS', NULL)
    22.         SELECT dbo.fnFormatDate(GetDate(), 'M-D-YY H:N:S', NULL)
    23.         SELECT dbo.fnFormatDate(GetDate(), 'M-D-YY H:N:S:MS', NULL)
    24.         SELECT dbo.fnFormatDate('nov 1, 2008 15:01:02', 'MMMM DDDD D, YYYY H:N:S AMPM', 1)
    25.         SELECT dbo.fnFormatDate('nov 1, 2008 15:01:02', 'MMMM DDDD D, YYYY H:N:S AMPM', 2)
    26.         SELECT dbo.fnFormatDate('nov 1, 2008 15:01:02', 'MMMM DDDD DD, YYYY HH:NN:SS AMPM', NULL)
    27.         SELECT dbo.fnFormatDate('nov 1, 2008 5:01:02', 'MMM DD, YYYY HH:NN:SS AMPM', NULL)
    28.     */
    29.    
    30.     DECLARE @RetStr NVARCHAR(255)
    31.     DECLARE @tMonth TABLE(MonthID INT, MonthStr NVARCHAR(20), Language INT)
    32.     DECLARE @tWeekDay TABLE(WeekDayID INT, WeekDayStr NVARCHAR(20), Language INT)
    33.    
    34.     DECLARE @Year INT
    35.     DECLARE @Month INT
    36.     DECLARE @WeekDay INT
    37.     DECLARE @Day INT
    38.    
    39.     DECLARE @Hour INT
    40.     DECLARE @Minute INT
    41.     DECLARE @Second INT
    42.     DECLARE @MS INT
    43.    
    44.     IF @Language < 1 OR @Language > 2 RETURN NULL
    45.    
    46.     INSERT INTO @tWeekDay VALUES(1, 'Monday', 1)
    47.     INSERT INTO @tWeekDay VALUES(2, 'Tuesday', 1)
    48.     INSERT INTO @tWeekDay VALUES(3, 'Wednesday', 1)
    49.     INSERT INTO @tWeekDay VALUES(4, 'Thursday', 1)
    50.     INSERT INTO @tWeekDay VALUES(5, 'Friday', 1)
    51.     INSERT INTO @tWeekDay VALUES(6, 'Saturday', 1)
    52.     INSERT INTO @tWeekDay VALUES(7, 'Sunday', 1)
    53.    
    54.     INSERT INTO @tMonth VALUES(1, 'January', 1)
    55.     INSERT INTO @tMonth VALUES(2, 'February', 1)
    56.     INSERT INTO @tMonth VALUES(3, 'March', 1)
    57.     INSERT INTO @tMonth VALUES(4, 'April', 1)
    58.     INSERT INTO @tMonth VALUES(5, 'May', 1)
    59.     INSERT INTO @tMonth VALUES(6, 'June', 1)
    60.     INSERT INTO @tMonth VALUES(7, 'July', 1)
    61.     INSERT INTO @tMonth VALUES(8, 'August', 1)
    62.     INSERT INTO @tMonth VALUES(9, 'September', 1)
    63.     INSERT INTO @tMonth VALUES(10, 'October', 1)
    64.     INSERT INTO @tMonth VALUES(11, 'November', 1)
    65.     INSERT INTO @tMonth VALUES(12, 'December', 1)
    66.    
    67.     INSERT INTO @tWeekDay VALUES(1, 'lundi', 2)
    68.     INSERT INTO @tWeekDay VALUES(2, 'mardi', 2)
    69.     INSERT INTO @tWeekDay VALUES(3, 'mercredi', 2)
    70.     INSERT INTO @tWeekDay VALUES(4, 'jeudi', 2)
    71.     INSERT INTO @tWeekDay VALUES(5, 'vendredi', 2)
    72.     INSERT INTO @tWeekDay VALUES(6, 'samedi', 2)
    73.     INSERT INTO @tWeekDay VALUES(7, 'dimanche', 2)
    74.    
    75.     INSERT INTO @tMonth VALUES(1, 'janvier', 2)
    76.     INSERT INTO @tMonth VALUES(2, 'fevrier', 2)
    77.     INSERT INTO @tMonth VALUES(3, 'mars', 2)
    78.     INSERT INTO @tMonth VALUES(4, 'avril', 2)
    79.     INSERT INTO @tMonth VALUES(5, 'mai', 2)
    80.     INSERT INTO @tMonth VALUES(6, 'juin', 2)
    81.     INSERT INTO @tMonth VALUES(7, 'juillet', 2)
    82.     INSERT INTO @tMonth VALUES(8, 'aout', 2)
    83.     INSERT INTO @tMonth VALUES(9, 'septembre', 2)
    84.     INSERT INTO @tMonth VALUES(10, 'octobre', 2)
    85.     INSERT INTO @tMonth VALUES(11, 'novembre', 2)
    86.     INSERT INTO @tMonth VALUES(12, 'decembre', 2)
    87.    
    88.     SET @RetStr = @Format
    89.    
    90.     SET @Year = DATEPART(year, @MyDate)
    91.     SET @Month = DATEPART(month, @MyDate)
    92.     SET @WeekDay = DATEPART(weekday, @MyDate)
    93.     SET @Day = DATEPART(day, @MyDate)
    94.    
    95.     SET @Hour = DATEPART(hour, @MyDate)
    96.     SET @Minute = DATEPART(minute, @MyDate)
    97.     SET @Second = DATEPART(second, @MyDate)
    98.     SET @MS = DATEPART(millisecond, @MyDate)
    99.    
    100.     SELECT @RetStr = REPLACE(@RetStr, 'YYYY' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(4), @Year))
    101.     SELECT @RetStr = REPLACE(@RetStr, 'YY' COLLATE Latin1_General_CS_AS, RIGHT(CONVERT(NVARCHAR(4), @Year), 2))
    102.     SELECT @RetStr = REPLACE(@RetStr, ' MS' COLLATE Latin1_General_CS_AS, ' ' + CONVERT(NVARCHAR(5), @MS))
    103.     SELECT @RetStr = REPLACE(@RetStr, ':MS' COLLATE Latin1_General_CS_AS, ':' + CONVERT(NVARCHAR(5), @MS))
    104.     SELECT @RetStr = REPLACE(@RetStr, '.MS' COLLATE Latin1_General_CS_AS, '.' + CONVERT(NVARCHAR(5), @MS))
    105.     SELECT @RetStr = REPLACE(@RetStr, 'MS' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(5), @MS))
    106.     SELECT @RetStr = REPLACE(@RetStr, ' M ' COLLATE Latin1_General_CS_AS, ' ' + CONVERT(NVARCHAR(2), @Month) + ' ')
    107.     SELECT @RetStr = REPLACE(@RetStr, ' M,' COLLATE Latin1_General_CS_AS, ' ' + CONVERT(NVARCHAR(2), @Month) + ',')
    108.     SELECT @RetStr = REPLACE(@RetStr, '-M-' COLLATE Latin1_General_CS_AS, '-' + CONVERT(NVARCHAR(2), @Month) + '-')
    109.     SELECT @RetStr = REPLACE(@RetStr, ' D ' COLLATE Latin1_General_CS_AS, ' ' + CONVERT(NVARCHAR(2), @Day) + ' ')
    110.     SELECT @RetStr = REPLACE(@RetStr, ' D,' COLLATE Latin1_General_CS_AS, ' ' + CONVERT(NVARCHAR(2), @Day) + ',')
    111.     SELECT @RetStr = REPLACE(@RetStr, '-D-' COLLATE Latin1_General_CS_AS, '-' + CONVERT(NVARCHAR(2), @Day) + '-')
    112.     SELECT @RetStr = REPLACE(@RetStr, ':N:' COLLATE Latin1_General_CS_AS, ':' + CONVERT(NVARCHAR(2), @Minute) + ':')
    113.     SELECT @RetStr = REPLACE(@RetStr, ':S ' COLLATE Latin1_General_CS_AS, ':' + CONVERT(NVARCHAR(2), @Second) + ' ')
    114.     SELECT @RetStr = REPLACE(@RetStr, 'NN' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Minute), 2))
    115.     SELECT @RetStr = REPLACE(@RetStr, 'SS' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Second), 2))
    116.     SELECT @RetStr = REPLACE(@RetStr, ':S' COLLATE Latin1_General_CS_AS, ':' + CONVERT(NVARCHAR(2), @Second))
    117.    
    118.     SELECT @RetStr = REPLACE(@RetStr, 'MMMM' COLLATE Latin1_General_CS_AS, m.MonthStr)
    119.     FROM @tMonth AS m
    120.     WHERE m.MonthID = @Month AND m.Language = ISNULL(@Language, 1)
    121.    
    122.     SELECT @RetStr = REPLACE(@RetStr, 'MMM' COLLATE Latin1_General_CS_AS, LEFT(m.MonthStr, 3))
    123.     FROM @tMonth AS m
    124.     WHERE m.MonthID = @Month AND m.Language = ISNULL(@Language, 1)
    125.    
    126.     SELECT @RetStr = REPLACE(@RetStr, 'DDDD' COLLATE Latin1_General_CS_AS, w.WeekDayStr)
    127.     FROM @tWeekDay AS w
    128.     WHERE w.WeekDayID = @WeekDay AND w.Language = ISNULL(@Language, 1)
    129.    
    130.     SELECT @RetStr = REPLACE(@RetStr, 'DDD' COLLATE Latin1_General_CS_AS, LEFT(w.WeekDayStr, 3))
    131.     FROM @tWeekDay AS w
    132.     WHERE w.WeekDayID = @WeekDay AND w.Language = ISNULL(@Language, 1)
    133.    
    134.     SELECT @RetStr = REPLACE(@RetStr, 'MM' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Month), 2))
    135.     SELECT @RetStr = REPLACE(@RetStr, 'M-' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(2), @Month) + '-')
    136.     SELECT @RetStr = REPLACE(@RetStr, 'DD' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Day), 2))
    137.    
    138.     IF CHARINDEX('AMPM', @RetStr) > 0 BEGIN
    139.         IF @Hour < 12 BEGIN
    140.             SELECT @RetStr = REPLACE(@RetStr, 'HH' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Hour), 2))
    141.             SELECT @RetStr = REPLACE(@RetStr, 'H' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(2), @Hour))
    142.             SELECT @RetStr = REPLACE(@RetStr, 'AMPM' COLLATE Latin1_General_CS_AS, 'AM')
    143.         END ELSE BEGIN
    144.             SELECT @RetStr = REPLACE(@RetStr, 'HH' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Hour - 12), 2))
    145.             SELECT @RetStr = REPLACE(@RetStr, 'H' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(2), @Hour - 12))
    146.             SELECT @RetStr = REPLACE(@RetStr, 'AMPM' COLLATE Latin1_General_CS_AS, 'PM')
    147.         END
    148.     END ELSE BEGIN
    149.         SELECT @RetStr = REPLACE(@RetStr, 'HH' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Hour), 2))
    150.         SELECT @RetStr = REPLACE(@RetStr, 'H' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(2), @Hour))
    151.     END
    152.    
    153.     RETURN @RetStr
    154. END
    Last edited by CVMichael; Nov 5th, 2008 at 04:21 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width