Date Format

• zulmanclock

  SSC Eights!

  Points: 968

  More actions

  April 19, 2013 at 6:56 am

  #1608190

  vishnus (4/18/2013)

  ---

  There is a bug in DateFormat function

  E.g.: SELECT dbo.fnDateFormat('5/5/2013','MON')

  O/P: 5AY

  Please make sure you have the revised function code, there was a bug found and i adjusted the code. See below:

  SET @DateValue = @Format

  IF (CHARINDEX ('YYYY',@DateValue) > 0)

  SET @DateValue = REPLACE(@DateValue, 'YYYY',

  DATENAME(YY, @Datetime))

  IF (CHARINDEX ('YY',@DateValue) > 0)

  SET @DateValue = REPLACE(@DateValue, 'YY',

  RIGHT(DATENAME(YY, @Datetime),2))

  IF (CHARINDEX ('Month',@DateValue) > 0)

  SET @DateValue = REPLACE(@DateValue, 'Month',

  DATENAME(MM, @Datetime))

  IF (CHARINDEX ('MON',@DateValue COLLATE LATIN1_GENERAL_CS_AS)>0)

  SET @DateValue = REPLACE(@DateValue, 'MON',

  LEFT(UPPER(DATENAME(MM, @Datetime)),3))

  IF (CHARINDEX ('Mon',@DateValue) > 0)

  SET @DateValue = REPLACE(@DateValue, 'Mon',

  LEFT(DATENAME(MM, @Datetime),3))

  IF (CHARINDEX ('MM',@DateValue) > 0)

  SET @DateValue = REPLACE(@DateValue,'MM',

  RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))

  IF (CHARINDEX ('M',@DateValue) > 0) AND (CHARINDEX('MA',@DateValue)=0)

  SET @DateValue = REPLACE(@DateValue,'M',

  CONVERT(VARCHAR,DATEPART(MM, @Datetime)))

  IF (CHARINDEX ('DD',@DateValue) > 0)

  SET @DateValue = REPLACE(@DateValue, 'DD',

  RIGHT('0'+DATENAME(DD, @Datetime),2))

  IF (CHARINDEX ('D',@DateValue) > 0)

  SET @DateValue = REPLACE(@DateValue,'D',

  DATENAME(DD, @Datetime))

  RETURN @DateValue

  END

  Make sure that your code has the highlighted row in it, if not just copy and paste this code into your function and it should be fine.

  Thank you