Format Date Time Function

  • Kraai

    SSC Veteran

    Points: 255

    Comments posted to this topic are about the item Format Date Time Function

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Always can use more date functions, thanks.

  • x

    SSC-Insane

    Points: 23578

    Make it a case statement!

    CREATE FUNCTION [dbo].[KDT_FN_FORMATDATETIME](

    @DateFormat varchar(50),

    @Date DATETIME

    )

    RETURNS VARCHAR(30)

    AS

    BEGIN

    RETURN CASE

    WHEN @DateFormat = 'dd-mm-yyyy' THEN Cast(day(@Date) as varchar(2))+'-'+(substring(datename(m, @Date),1,3))+'-'+cast(year(@Date) as varchar(4))

    WHEN @DateFormat = 'MMM DD YYYY HH:mm Am/pm' THEN CONVERT(VARCHAR(30),@Date,100)

    WHEN @DateFormat = 'MM/DD/YYYY' THEN CONVERT(VARCHAR(30),@Date,101)

    WHEN @DateFormat = 'YYYY.MM.DD' THEN CONVERT(VARCHAR(30),@Date,102)

    WHEN @DateFormat = 'DD/MM/YYYY' THEN CONVERT(VARCHAR(30),@Date,103)

    WHEN @DateFormat = 'DD.MM.YYYY' THEN CONVERT(VARCHAR(30),@Date,104)

    WHEN @DateFormat = 'DD-MM-YYYY' THEN CONVERT(VARCHAR(30),@Date,105)

    WHEN @DateFormat = 'DD MMM YYYY' THEN CONVERT(VARCHAR(30),@Date,106)

    WHEN @DateFormat = 'MMM DD,YYYY' THEN CONVERT(VARCHAR(30),@Date,107)

    WHEN @DateFormat = 'HH:mm:SS' THEN CONVERT(VARCHAR(30),@Date,108)

    WHEN @DateFormat = 'MMM DD YYYY H:mm:SS:MSAM/PM' THEN CONVERT(VARCHAR(30),@Date,109)

    WHEN @DateFormat = 'DD-MM-YYYY' THEN CONVERT(VARCHAR(30),@Date,110)

    WHEN @DateFormat = 'YYYY/MM/DD' THEN CONVERT(VARCHAR(30),@Date,111)

    WHEN @DateFormat = 'YYYYMMDD' THEN CONVERT(VARCHAR(30),@Date,112)

    WHEN @DateFormat = 'DD MMM YYYY HH:mm:SS:MS' THEN CONVERT(VARCHAR(30),@Date,113)

    WHEN @DateFormat = 'HH:mm:SS:MS' THEN CONVERT(VARCHAR(30),@Date,114)

    WHEN @DateFormat = 'Julion' THEN CONVERT(VARCHAR(30), (DATEDIFF (day, CONVERT(datetime, '1900-01-01', 110)

    , @Date))) -- LOL WHO AM I TO CORRECT SPELLING?

    WHEN @DateFormat = 'Day' THEN CONVERT(VARCHAR(30),DAY(@Date))

    WHEN @DateFormat = 'Month' THEN CONVERT(VARCHAR(30),Month(@Date))

    WHEN @DateFormat = 'Year' THEN CONVERT(VARCHAR(30),Year(@Date))

    WHEN @DateFormat = 'YYYY-MM' THEN CONVERT(VARCHAR(30),Year(@Date))+'-'+CONVERT(VARCHAR(30),Month(@Date))

    WHEN @DateFormat = 'MM-YYYY' THEN CONVERT(VARCHAR(30),Month(@Date))+'-'+CONVERT(VARCHAR(30),Year(@Date))

    --- OK I'M TIRED NOW

    ELSE CONVERT(VARCHAR(30),@Date,121)

    END

    END

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply