A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Tuesday, February 2, 2010

Got, got, got, got no time

Sometimes I'm just too lazy to look in BOL to remember what the various date formats actually look like so I present my boring little TSQL script to Generate Date Formats. It's also an opportunity to verify I've actually set my home subversion server up correctly.

Given the following code, it would generate

SET NOCOUNT ON

DECLARE
    @formatter smallint
,   @previousFormat varchar(30)
,   @currentFormat varchar(30)
,   @currentDate datetime

SELECT
    @formatter = 0
,   @previousFormat = ''
,   @currentFormat = ''
,   @currentDate = getdate()

DECLARE @DATE_FORMAT TABLE
(
    formatter smallint
,   format varchar(30)
)

WHILE
    @formatter < 256
BEGIN
    BEGIN TRY
        SET
            @currentFormat = convert(varchar(8000), @currentDate, @formatter)
        IF (@previousFormat <> @currentFormat)
        BEGIN
            INSERT INTO
                @DATE_FORMAT
            SELECT
                @formatter AS formatter
            ,   @currentFormat AS format

            SELECT @previousFormat = @currentFormat
        END
    END TRY
    BEGIN CATCH
        -- Do nothing
    END CATCH

    SET @formatter = @formatter + 1
END


SELECT
    D.*
FROM
    @DATE_FORMAT D

Output
formatter format
--------- ------------------------------
0         Feb  2 2010  8:42PM
1         02/02/10
2         10.02.02
3         02/02/10
4         02.02.10
5         02-02-10
6         02 Feb 10
7         Feb 02, 10
8         20:42:44
9         Feb  2 2010  8:42:44:780PM
10        02-02-10
11        10/02/02
12        100202
13        02 Feb 2010 20:42:44:780
14        20:42:44:780
20        2010-02-02 20:42:44
21        2010-02-02 20:42:44.780
22        02/02/10  8:42:44 PM
23        2010-02-02
24        20:42:44
25        2010-02-02 20:42:44.780
100       Feb  2 2010  8:42PM
101       02/02/2010
102       2010.02.02
103       02/02/2010
104       02.02.2010
105       02-02-2010
106       02 Feb 2010
107       Feb 02, 2010
108       20:42:44
109       Feb  2 2010  8:42:44:780PM
110       02-02-2010
111       2010/02/02
112       20100202
113       02 Feb 2010 20:42:44:780
114       20:42:44:780
120       2010-02-02 20:42:44
121       2010-02-02 20:42:44.780
126       2010-02-02T20:42:44.780
130       18 ??? 1431  8:42:44:780PM
131       18/02/1431  8:42:44:780PM

No comments: