Format Date Time Function

• Kraai

  SSC Veteran

  Points: 255

  More actions

  April 6, 2016 at 9:34 am

  #317683

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

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  April 26, 2016 at 6:24 am

  #1874541

  Always can use more date functions, thanks.

• x

  SSC-Insane

  Points: 23660

  More actions

  April 26, 2016 at 7:30 am

  #1874601

  Make it a case statement!

  CREATE FUNCTION [dbo].[KDT_FN_FORMATDATETIME](

  @DateFormatvarchar(50),

  @DateDATETIME

  )

  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