3

I know this is kinda stupid to ask but is there a Transact-SQL function or anything that returns all CONVERT(datetime) styles (formats)? Something like fnDateFormats will return all date formats:

Output:

Dateformat | SQL code
MM/DD/YYYY | 101

and so on.

  • 1
    I don't know if is possible with T-SQL, but here you see the style codes from the official MS docs: docs.microsoft.com/en-us/sql/t-sql/functions/… – HEDMON Aug 17 '17 at 6:27
  • I think it's possible to check all the CONVERT style numbers and store the results in a table, I've had a stab at some code that does that below. – Oreo Aug 17 '17 at 10:59
11

There is no such built-in function – or system table, or view – that would give you a list of all styles (formats). Otherwise people would not have been creating custom-made style lists (like this one, for instance) – as a way of a reminder, I guess, because the same information can be found in the online documentation, even if slightly differently arranged.

Go ahead and create one for yourself. New styles are sometimes introduced with new major releases, but those do not happen too often, so maintaining such a list should not be much of a burden.

Another thing is whether you really need to have such a table. Unless you are still using a pre-2012 version, you can format your datetime values arbitrarily with the FORMAT() function.

  • Thanks for the reply! I was actually looking for something to save time. Creating a table to cater my needs for the date format is my last resort tho. Its a hassle to look into the web just to know "what was the code again?" or something. :D – Jack Frost Aug 17 '17 at 8:59
2

Even though there is no built-in function to do this in SQL Server, there is nothing preventing you from writing your own function, outputting something like this:

OriginalValue                   Style   ConvertedValue

2017-12-31 23:59:29.123456789   1       12/31/17
2017-12-31 23:59:29.123456789   2       17.12.31
2017-12-31 23:59:29.123456789   3       31/12/17
2017-12-31 23:59:29.123456789   4       31.12.17
2017-12-31 23:59:29.123456789   5       31-12-17
2017-12-31 23:59:29.123456789   6       31 Dec 17
2017-12-31 23:59:29.123456789   7       Dec 31, 17
2017-12-31 23:59:29.123456789   8       23:59:29
2017-12-31 23:59:29.123456789   9       Dec 31 2017 11:59:29.1234568PM
2017-12-31 23:59:29.123456789   10      12-31-17
...

Here is my code for this, it displays attempts at converting a DateTime2(7) field to a NVarChar for Styles 1 up to 150.

DECLARE
     @OriginalDate datetime2 = N'2017-12-31 23:59:29.123456789'
    ,@Try_Top_X_Styles smallint = 150
    ,@ConvertFromType nvarchar(50) = 'DATETIME2(7)'
    ,@ConvertToType nvarchar(50) = 'NVARCHAR(500)';


IF OBJECT_ID('tempdb.dbo.##DateFormats') IS NOT NULL
    DROP TABLE ##DateFormats;

SELECT @OriginalDate AS OriginalDate
    ,CONVERT(BIGINT,0) AS Style
    ,CONVERT(NVARCHAR(500),@OriginalDate,120) AS ConvertedDate
INTO ##DateFormats
WHERE 0 = 1;

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT
    TOP (@Try_Top_X_Styles)
    @sql = @sql + N'BEGIN TRY
INSERT INTO ##DateFormats
SELECT N''' + CONVERT(NVARCHAR(50),@OriginalDate,127) + N'''
,CONVERT(BIGINT,' + CONVERT(NVARCHAR(20),ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) + N')
,CONVERT(' + @ConvertToType + ',CONVERT(' + @ConvertFromType + ',''' + CONVERT(NVARCHAR(50),@OriginalDate,127) + N'''),' + CONVERT(NVARCHAR(20),ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) + N');
END TRY
BEGIN CATCH
    INSERT INTO ##DateFormats
    SELECT N''' + CONVERT(NVARCHAR(50),@OriginalDate,127) + N'''
    ,CONVERT(BIGINT,' + CONVERT(NVARCHAR(20),ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) + N')
    ,ERROR_MESSAGE()
END CATCH
'
FROM sys.columns;

--PRINT (@sql); --For The Debug!
EXEC (@sql);
GO
SELECT *
FROM ##DateFormats
WHERE ConvertedDate NOT LIKE '%not a valid style number%'
ORDER BY Style;
  • 1
    Man... This is something! It may not the thing I need but still I never thought this is possible. Can I keep a copy of it? :D – Jack Frost Aug 18 '17 at 7:33
  • Yup! You can apply similar techniques to other SQL Internals problems. Just "test what works and what doesn't". Error logging is your friend most of the time, too. – Oreo Aug 18 '17 at 9:27
0

Just for the fun of it...

DECLARE @n INT = 0, @v nvarchar(100);
WHILE @n < 10000
BEGIN
    BEGIN TRY 
        SELECT @v = CONCAT(@n, N'       ', CONVERT(NVARCHAR(50), GETDATE(), @n));
        RAISERROR(@v, 0, 1);
        SET @n += 1;
    END TRY
    BEGIN CATCH
        SET @n += 1;
    END CATCH;
END; 

Output...

0       May  2 2019  1:42PM
1       05/02/19
2       19.05.02
3       02/05/19
4       02.05.19
5       02-05-19
6       02 May 19
7       May 02, 19
8       13:42:19
9       May  2 2019  1:42:19:633PM
10      05-02-19
11      19/05/02
12      190502
13      02 May 2019 13:42:19:633
14      13:42:19:633
20      2019-05-02 13:42:19
21      2019-05-02 13:42:19.633
22      05/02/19  1:42:19 PM
23      2019-05-02
24      13:42:19
25      2019-05-02 13:42:19.633
100     May  2 2019  1:42PM
101     05/02/2019
102     2019.05.02
103     02/05/2019
104     02.05.2019
105     02-05-2019
106     02 May 2019
107     May 02, 2019
108     13:42:19
109     May  2 2019  1:42:19:633PM
110     05-02-2019
111     2019/05/02
112     20190502
113     02 May 2019 13:42:19:633
114     13:42:19:633
120     2019-05-02 13:42:19
121     2019-05-02 13:42:19.633
126     2019-05-02T13:42:19.633
127     2019-05-02T13:42:19.633
130     27 شعبان 1440  1:42:19:633PM
131     27/08/1440  1:42:19:633PM

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.