T-SQL Code: Date Format User-Defined Function; dbo.fDateFormat()

IF OBJECT_ID('fDateFormat') IS NOT NULL BEGIN DROP FUNCTION fDateFormat IF OBJECT_ID('fDateFormat') IS NOT NULL PRINT '<<< FAILED DROPPING FUNCTION fDateFormat >>>' ELSE PRINT '<<< DROPPED FUNCTION fDateFormat >>>' END ; /* Based on dbo.FormatDateTime at http://www.aspfaq.com/show.asp?id=2460.

&quot;You cannot use a non-deterministic function
within a UDF, e.g. GETDATE().&quot;
(http://www.aspfaq.com/show.asp?id=2439)

*/ CREATE FUNCTION dbo.fDateFormat ( @cmdName varchar(16) = NULL , @dt datetime = NULL ) RETURNS varchar(64) AS BEGIN DECLARE @dtStr VARCHAR(64)

IF @cmdName = 'LONGDATE'
BEGIN
    SET @dtStr = DATENAME(dw, @dt) 
    + ',' + SPACE(1) + DATENAME(m, @dt) 
    + SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2)) 
    + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4)) 
END

IF @cmdName = 'LONGDATEANDTIME'
BEGIN
    SET @dtStr = 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)
END

IF @cmdName = 'SHORTDATE'
BEGIN 
    SET @dtStr = LEFT(CONVERT(CHAR(19), @dt, 0), 11)
END

IF @cmdName = 'SHORTDATEANDTIME'
BEGIN
    SET @dtStr = REPLACE(REPLACE(CONVERT(CHAR(19), @dt, 0), 
        'AM', ' AM'), 'PM', ' PM') 
END

IF @cmdName = 'UNIXTIMESTAMP'
BEGIN
    SET @dtStr = CAST(DATEDIFF(SECOND, '19700101', @dt) AS VARCHAR(64)) 
END

IF @cmdName = 'YYYYMMDD'
BEGIN
    SET @dtStr = CONVERT(CHAR(8), @dt, 112) 
END

IF @cmdName = 'YYYY-MM-DD'
BEGIN
    SET @dtStr = CONVERT(CHAR(10), @dt, 23) 
END

IF @cmdName = 'YYMMDD'
BEGIN
    SET @dtStr = CONVERT(VARCHAR(8), @dt, 12) 
END

IF @cmdName = 'YY-MM-DD'
BEGIN
    SET @dtStr = STUFF(STUFF(CONVERT(VARCHAR(8), @dt, 12),5, 0, '-'), 3, 0, '-')
END

IF @cmdName = 'MMDDYY'
BEGIN
    SET @dtStr = REPLACE(CONVERT(CHAR(8), @dt, 10), '-', SPACE(0)) 
END

IF @cmdName = 'MM-DD-YY'
BEGIN
    SET @dtStr = CONVERT(CHAR(8), @dt, 10) 
END

IF @cmdName = 'MM/DD/YY' 
BEGIN
    SET @dtStr = CONVERT(CHAR(8), @dt, 1) 
END

IF @cmdName = 'MM/DD/YYYY'
BEGIN
    SET @dtStr = CONVERT(CHAR(10), @dt, 101) 
END
IF @cmdName = 'DDMMYY'
BEGIN
    SET @dtStr = REPLACE(CONVERT(CHAR(8), @dt, 3), '/', SPACE(0)) 
END
IF @cmdName = 'DD-MM-YY'
BEGIN
    SET @dtStr = REPLACE(CONVERT(CHAR(8), @dt, 3), '/', '-') 
END

IF @cmdName = 'DD/MM/YY'
BEGIN
    SET @dtStr = CONVERT(CHAR(8), @dt, 3) 
END

IF @cmdName = 'DD/MM/YYYY'
BEGIN
    SET @dtStr = CONVERT(CHAR(10), @dt, 103) 
END

IF @cmdName = 'HH:MM:SS 24'
BEGIN
    SET @dtStr = CONVERT(CHAR(8), @dt, 8) 
END

IF @cmdName = 'HH:MM 24'
BEGIN
    SET @dtStr = LEFT(CONVERT(VARCHAR(8), @dt, 8), 5) 
END

IF @cmdName = 'HH:MM:SS 12'
BEGIN
    SET @dtStr = LTRIM(RIGHT(CONVERT(VARCHAR(20), @dt, 22), 11)) 
END

IF @cmdName = 'HH:MM 12'
BEGIN
    SET @dtStr = LTRIM(SUBSTRING(CONVERT( 
    VARCHAR(20), @dt, 22), 10, 5) 
    + RIGHT(CONVERT(VARCHAR(20), @dt, 22), 3)) 
END

IF @cmdName = 'RFC822'
BEGIN
    IF LEN(DATENAME(dd,@dt)) &gt; 1
    BEGIN
        SET @dtStr = SUBSTRING(DATENAME(dw,@dt),1,3)
        + ', '
        + DATENAME(dd,@dt)
        + ' '
        + SUBSTRING(DATENAME(m,@dt),1,3)
        + ' '
        + DATENAME(yy,@dt)
        + ' '
        + CONVERT(CHAR(8), @dt, 8)
        + ' +0000'
    END
    ELSE
    BEGIN
        SET @dtStr = SUBSTRING(DATENAME(dw,@dt),1,3)
        + ', 0'
        + DATENAME(dd,@dt)
        + ' '
        + SUBSTRING(DATENAME(m,@dt),1,3)
        + ' '
        + DATENAME(yy,@dt)
        + ' '
        + CONVERT(CHAR(8), @dt, 8)
        + ' +0000'
    END

END

RETURN @dtStr

END ; GRANT EXECUTE ON fDateFormat TO public ; IF OBJECT_ID('fDateFormat') IS NOT NULL PRINT '<<< CREATED FUNCTION fDateFormat >>>' ELSE PRINT '<<< FAILED CREATING FUNCTION fDateFormat >>>' ;

mod date: 2006-01-14T17:47:32.000Z