Date Format

  • 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

Viewing post 16 (of 15 total)

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