Format DateTime

SQL Server's built-in CONVERT styles cover a lot of date formats, but not all of the ones you actually want in a report. This scalar function takes a datetime and one of about twenty named format strings and returns the value formatted accordingly - including a UNIX timestamp (seconds since 1970-01-01), which is a common need when handing data off to an application.

Example: SELECT dbo.FormatDateTime('2024-03-15 14:30:45', 'YYYY-MM-DD') -- returns 2024-03-15 SELECT dbo.FormatDateTime('2024-03-15 14:30:45', 'UNIXTIMESTAMP')

Supported formats: LONGDATE, LONGDATEANDTIME, SHORTDATE, SHORTDATEANDTIME, UNIXTIMESTAMP, YYYYMMDD, YYYY-MM-DD, YYMMDD, YY-MM-DD, MMDDYY, MM-DD-YY, MM/DD/YY, MM/DD/YYYY, DDMMYY, DD-MM-YY, DD/MM/YY, DD/MM/YYYY, HH:MM:SS 24, HH:MM 24, HH:MM:SS 12, HH:MM 12.

CREATE FUNCTION FormatDateTime 
( 
    @DateTime DATETIME, 
    @Format VARCHAR(16) 
) 
RETURNS VARCHAR(64) 
AS 
BEGIN 
    DECLARE @DateTimeVC VARCHAR(64) 
    SELECT @DateTimeVC = CASE @Format
 
    WHEN 'LONGDATE' THEN 
         DATENAME(dw, @DateTime) 
        + ',' + SPACE(1) + DATENAME(m, @DateTime) 
        + SPACE(1) + CAST(DAY(@DateTime) AS VARCHAR(2)) 
        + ',' + SPACE(1) + CAST(YEAR(@DateTime) AS CHAR(4)) 
    WHEN 'LONGDATEANDTIME' THEN 
         DATENAME(dw, @DateTime) 
        + ',' + SPACE(1) + DATENAME(m, @DateTime) 
        + SPACE(1) + CAST(DAY(@DateTime) AS VARCHAR(2)) 
        + ',' + SPACE(1) + CAST(YEAR(@DateTime) AS CHAR(4)) 
        + SPACE(1) + RIGHT(CONVERT(CHAR(20), 
        @DateTime - CONVERT(DATETIME, CONVERT(CHAR(8), 
        @DateTime, 112)), 22), 11) 
    WHEN 'SHORTDATE' THEN 
         LEFT(CONVERT(CHAR(19), @DateTime, 0), 11) 
    WHEN 'SHORTDATEANDTIME' THEN 
         REPLACE(REPLACE(CONVERT(CHAR(19), @DateTime, 0), 
            'AM', ' AM'), 'PM', ' PM') 
    WHEN 'UNIXTIMESTAMP' THEN 
         CAST(DATEDIFF(SECOND, '19700101', @DateTime) 
        AS VARCHAR(64)) 
    WHEN 'YYYYMMDD' THEN 
         CONVERT(CHAR(8), @DateTime, 112) 
    WHEN 'YYYY-MM-DD' THEN 
         CONVERT(CHAR(10), @DateTime, 23) 
    WHEN 'YYMMDD' THEN 
         CONVERT(VARCHAR(8), @DateTime, 12) 
    WHEN 'YY-MM-DD' THEN 
        STUFF(STUFF(CONVERT(VARCHAR(8), @DateTime, 12), 
        5, 0, '-'), 3, 0, '-') 
    WHEN 'MMDDYY' THEN 
         REPLACE(CONVERT(CHAR(8), @DateTime, 10), '-', SPACE(0)) 
    WHEN 'MM-DD-YY' THEN 
         CONVERT(CHAR(8), @DateTime, 10) 
    WHEN 'MM/DD/YY' THEN 
         CONVERT(CHAR(8), @DateTime, 1) 
    WHEN 'MM/DD/YYYY' THEN 
         CONVERT(CHAR(10), @DateTime, 101) 
    WHEN 'DDMMYY' THEN 
         REPLACE(CONVERT(CHAR(8), @DateTime, 3), '/', SPACE(0)) 
    WHEN 'DD-MM-YY' THEN 
         REPLACE(CONVERT(CHAR(8), @DateTime, 3), '/', '-') 
    WHEN 'DD/MM/YY' THEN 
         CONVERT(CHAR(8), @DateTime, 3) 
    WHEN 'DD/MM/YYYY' THEN 
         CONVERT(CHAR(10), @DateTime, 103) 
    WHEN 'HH:MM:SS 24' THEN 
         CONVERT(CHAR(8), @DateTime, 8) 
    WHEN 'HH:MM 24' THEN 
         LEFT(CONVERT(VARCHAR(8), @DateTime, 8), 5) 
    WHEN 'HH:MM:SS 12' THEN 
         LTRIM(RIGHT(CONVERT(VARCHAR(20), @DateTime, 22), 11)) 
    WHEN 'HH:MM 12' THEN 
         LTRIM(SUBSTRING(CONVERT( 
        VARCHAR(20), @DateTime, 22), 10, 5) 
        + RIGHT(CONVERT(VARCHAR(20), @DateTime, 22), 3)) 
     ELSE 
         'Invalid format specified' 
     END 
    RETURN @DateTimeVC 
END 
GO