Date Format

  • Comments posted to this topic are about the item Date Format

  • A very good function however it seems to miss the leading zero off dates that are in the first 9 days of the month when using a format of 'dd/mm/yy' although the code seems to try to manage that out.

  • Fear Naught (3/11/2013)


    A very good function however it seems to miss the leading zero off dates that are in the first 9 days of the month when using a format of 'dd/mm/yy' although the code seems to try to manage that out.

    I just tested this out and it seems to work when i run the function. I did however come across an issue in it where, when the month is March or May, it would throw the function off since it recognizes the M in the month name. I have made the fix, see below:

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER FUNCTION [dbo].[fnFormatDate] (@Datetime DATETIME, @Format VARCHAR(32))

    RETURNS VARCHAR(32)

    AS

    BEGIN

    DECLARE @DateValue VARCHAR(32)

    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)>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

  • Thanks for the prompt reply. The issue I get is as follows:

    select [dbo].[fnDateFormat](getdate()-3,'dd/mm/yyyy')

    -- today is the 11th so the minus 3 is to get a date in single figures.

    will return

    8/03/2013 and not what I would expect which would be 08/03/2013.

    Kevin

  • No problem. I just ran the same code and it is formatted correctly. Have you tried debugging the code to see where it is going haywire?

  • I thought it might be something to do with the version of SQL Server I am using (2000) so tried it on 2008R2 and get the same result.

    I'm not too familiar with debug having run it on my 2008R2 server the code runs OK but fails to apply the correct value to the "dd" or indeed "d" part of the @Datetime variable.

    Thanks.

  • Can you show me the function as it is on your system? I wonder if maybe when you copied and pasted from the article maybe it missed or added something that is skewing the results.

  • ALTER FUNCTION [dbo].[fnDateFormat] (@Datetime DATETIME, @Format VARCHAR(32))

    RETURNS VARCHAR(32)

    AS

    BEGIN

    DECLARE @DateValue VARCHAR(32)

    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)>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

  • Ok I see the problem. In the code you function lost the single quotes around the 0.

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

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

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

    If you place single quotes around the 0 that should do the trick.

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

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

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

  • Dohhh. Many thanks for your help. I couldn't see the wood for the trees obviously.

    A really useful function.

  • No problem. Thank you for your comments.

  • Here's a similar function with more options that is an inline tvf rather than scalar. The benefits of that should be obvious. I didn't write the original code (and can't remember who did), but I did add quite a few of the options and turned it into an itvf.

    CREATE FUNCTION [dbo].[itvfFormatDateWithMask]

    (

    @date AS DATETIME

    ,@format_string VARCHAR(50)

    )

    RETURNS TABLE

    AS

    RETURN

    (

    /*

    SELECT * FROM dbo.itvfFormatDateWithMask(GETDATE(),'YYYY-MM-DD HH:mm:ss.fff')

    SELECT * FROM dbo.itvfFormatDateWithMask(GETDATE(),'MMMM Dx, YYYY hh:mmtt')

    SELECT * FROM dbo.itvfFormatDateWithMask(GETDATE(),'MMM Dx, YYYY')

    'YYYY' - full year with century

    'YY' - year without century

    'Y' - last digit of year

    'MMMM' - month name

    'MMM' - month name first 3 chars only

    'MM' - month number with leading zero

    'M' - month number without leading zero)

    'DDDD' - day name

    'DD' - day number with leading zero

    'D' - day number without leading zero

    'HH' - hour with leading zero (24 hr format)

    'H' - hour without leading zero

    'hh' - hour with leading zero (12 hr format)

    'h' - hour without leading zero (12 hr format)

    'mm' - minutes with leading zero

    'm' - minutes without leading zero

    'ss' - seconds with leading zero

    's' - seconds without leading zero

    'fff' - milliseconds with leading zeros

    'f' - milliseconds without leading zeros

    'tt' - AM or PM

    't' - A or P

    'x' - st, nd, or th

    */

    WITH fourRows(N)

    AS (

    SELECT 1

    UNION ALL

    SELECT 2

    UNION ALL

    SELECT 3

    UNION ALL

    SELECT 4

    )

    ,cteTally(N)

    AS (

    SELECT TOP (50)

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum

    FROM

    fourRows AS A

    CROSS JOIN fourRows AS B

    CROSS JOIN fourRows AS C

    ORDER BY 1

    )

    ,tokenizedString

    AS (

    SELECT

    N

    ,REPLACE(C,SUBSTRING(CAST(C AS NVARCHAR(50)),CHARINDEX('""',CAST(C AS NVARCHAR(50)),1),CHARINDEX('""',CAST(C AS NVARCHAR(50)),2)),'ZZZZZZZ') AS C

    ,groupId = DENSE_RANK() OVER (ORDER BY C, _groupId)

    FROM

    (

    SELECT

    N

    ,SUBSTRING(@format_string COLLATE Latin1_General_CS_AS,N,1) AS C

    ,_groupId = N - ROW_NUMBER() OVER (PARTITION BY SUBSTRING(@format_string COLLATE Latin1_General_CS_AS,N,1) ORDER BY N)

    FROM

    cteTally

    WHERE

    N <= LEN(@format_string)

    ) AS fs)

    SELECT

    (SELECT

    CASE REPLICATE(MIN(C),COUNT(*))

    WHEN 'YYYY' THEN RIGHT('0000' + CAST(YEAR(@date) AS NVARCHAR(4)),4)

    WHEN 'YY' THEN RIGHT('00' + CAST(YEAR(@date) AS NVARCHAR(4)),2)

    WHEN 'Y' THEN CAST(CAST(RIGHT('00' + CAST(YEAR(@date) AS NVARCHAR(4)),1) AS INT) AS NVARCHAR(2))

    WHEN 'MMMM' THEN DATENAME(month,@date)

    WHEN 'MMM' THEN LEFT(DATENAME(month,@date),3)

    WHEN 'MM' THEN RIGHT('00' + CAST(MONTH(@date) AS NVARCHAR(2)),2)

    WHEN 'M' THEN CAST(MONTH(@date) AS NVARCHAR(2))

    WHEN 'DDDD' THEN DATENAME(weekday,@date)

    WHEN 'DD' THEN RIGHT('00' + CAST(DAY(@date) AS NVARCHAR(2)),2)

    WHEN 'D' THEN CAST(DAY(@date) AS NVARCHAR(2))

    WHEN 'HH' THEN RIGHT('00' + CAST(DATEPART(hour,@date) AS NVARCHAR(2)),2)

    WHEN 'H' THEN CAST(DATEPART(hour,@date) AS NVARCHAR(2))

    WHEN 'hh' THEN RIGHT('00' + CAST(

    CASE DATEPART(hour,@date)

    WHEN 12 THEN 12

    ELSE DATEPART(hour,@date) % 12

    END AS NVARCHAR(2)),2)

    WHEN 'h' THEN

    CASE WHEN LEFT(

    RIGHT('00' + CAST(

    CASE DATEPART(hour,@date)

    WHEN 12 THEN 12

    ELSE DATEPART(hour,@date) % 12

    END AS NVARCHAR(2)),2),1) = 0 THEN

    RIGHT('00' + CAST(

    CASE DATEPART(hour,@date)

    WHEN 12 THEN 12

    ELSE DATEPART(hour,@date) % 12

    END AS NVARCHAR(2)),1)

    ELSE

    RIGHT('00' + CAST(

    CASE DATEPART(hour,@date)

    WHEN 12 THEN 12

    ELSE DATEPART(hour,@date) % 12

    END AS NVARCHAR(2)),2)

    END

    WHEN 'mm' THEN RIGHT('00' + CAST(DATEPART(minute,@date) AS NVARCHAR(2)),2)

    WHEN 'm' THEN CAST(DATEPART(minute,@date) AS NVARCHAR(2))

    WHEN 'ss' THEN RIGHT('00' + CAST(DATEPART(second,@date) AS NVARCHAR(2)),2)

    WHEN 's' THEN CAST(DATEPART(second,@date) AS NVARCHAR(2))

    WHEN 'fff' THEN RIGHT('000' + CAST(DATEPART(millisecond,@date) AS NVARCHAR(3)),3)

    WHEN 'f' THEN CAST(DATEPART(millisecond,@date) AS NVARCHAR(3))

    WHEN 'tt' THEN

    CASE

    WHEN DATEPART(hour,@date) >= 12 THEN N'PM'

    ELSE N'AM'

    END

    WHEN 't' THEN

    CASE

    WHEN DATEPART(hour,@date) >= 12 THEN N'P'

    ELSE N'A'

    END

    WHEN 'x' THEN

    CASE

    WHEN CAST(DAY(@date) AS INT) IN (1,21,31) THEN N'st'

    WHEN CAST(DAY(@date) AS INT) IN (3,23) THEN N'rd'

    ELSE N'th'

    END

    ELSE

    MIN(C)

    END

    FROM

    tokenizedString

    GROUP BY

    groupId

    ORDER BY

    MIN(N)

    FOR

    XML PATH('')

    ,TYPE

    ).value('(./text())[1]','nvarchar(50)')

    AS FormattedDate

    )

  • This is a really simple and effective script!

    One question I had is how would the function know the difference between 'Mon' and 'MON' if the DB collation is case-insensitive?

    When I ran the following code on my case-insensitive DB it still gave me a 6...

    SELECT CHARINDEX('Mon','YYYY-MON-D')

  • In its current format it does not recognize the difference. If you chance the line

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

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

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

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

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

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

    That will force the function to distinguish between the upper case and the lower case. Tested and works on my database.

    Thank you for your comment and question.

  • There is a bug in DateFormat function

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

    O/P: 5AY

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

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