Home Forums SQL Server 2008 T-SQL (SS2K8) GetDate() 5 Years ago without time formated 'YYYY-MM-DD' RE: GetDate() 5 Years ago without time formated 'YYYY-MM-DD'

  • I didn't invent the code use in this function so can't take credit (or blame), but it comes in handy sometimes. I did turn it into an itvf and added a few extra formatting options so it can be used in a CROSS APPLY to quickly format any column of dates into any format you need. See additional example usage with a CROSS APPLY at bottom of post.

    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

    )

    /*

    TESTING SCRIPTS

    IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

    DROP TABLE #TempTable

    CREATE TABLE #TempTable (

    [ID] INT IDENTITY(1,1) NOT NULL,

    [RandomDate] DATETIME NULL,

    PRIMARY KEY (ID))

    INSERT INTO #TempTable

    SELECT RandomDate

    FROM (

    SELECT TOP 100

    RandomDate = DATEADD(second,ABS(CHECKSUM(NEWID()))%36000, DATEADD(day,ABS(CHECKSUM(NEWID()))%3653+36524,'2000-01-01 00:00:01'))

    FROM Master.dbo.SysColumns t1

    )d

    SELECT *

    FROM #TempTable AS tt

    CROSS APPLY

    dbo.itvfFormatDateWithMask(tt.RandomDate,'MMMM Dx, YYYY h:mtt') AS d1

    CROSS APPLY

    dbo.itvfFormatDateWithMask(tt.RandomDate,'MM-DD-YYYY HH:mm') AS d2

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

    SELECT * FROM dbo.itvfFormatDateWithMask(GETDATE(),'MMMM Dx, YYYY h:mtt')

    */