I wrote this little table and procedure to help me remember what style 104 did, or how to get HH:MM AM/PM out of a DATETIME column. Basically, it populates a table with the valid style numbers, then loops through those, and produces the result (and the syntax for producing that result) for each style, given the current date and time.
It uses also a cursor. This is designed to be a helper function, not something you would use as part of a production environment, so I don’t think the performance implications should be a big concern.
USE tempdb;
GO
CREATE PROCEDURE dbo.help_DateTimeFormats
@styleID TINYINT = NULL
AS
BEGIN
SET NOCOUNT ON;
IF OBJECTPROPERTY
(
OBJECT_ID(‘dbo.DateTimeFormats’),
‘IsUserTable’
) = 1
BEGIN
DROP TABLE dbo.DateTimeFormats;
END
CREATE TABLE dbo.DateTimeFormats
(
styleID TINYINT PRIMARY KEY,
outputLength TINYINT,
outputSyntax VARCHAR(64),
outputSample VARCHAR(255)
);
INSERT dbo.DateTimeFormats(styleID, outputLength)
SELECT style = 0, outputLength = 19
UNION SELECT 1, 8
UNION SELECT 2, 8
UNION SELECT 3, 8
UNION SELECT 4, 8
UNION SELECT 5, 8
UNION SELECT 6, 9
UNION SELECT 7, 10
UNION SELECT 8, 8
UNION SELECT 9, 26
UNION SELECT 10, 8
UNION SELECT 11, 8
UNION SELECT 12, 6
UNION SELECT 13, 24
UNION SELECT 14, 12
UNION SELECT 20, 19
UNION SELECT 21, 23
UNION SELECT 22, 20
UNION SELECT 23, 10
UNION SELECT 24, 8
UNION SELECT 25, 23
UNION SELECT 100, 19
UNION SELECT 101, 10
UNION SELECT 102, 10
UNION SELECT 103, 10
UNION SELECT 104, 10
UNION SELECT 105, 10
UNION SELECT 106, 11
UNION SELECT 107, 12
UNION SELECT 108, 8
UNION SELECT 109, 26
UNION SELECT 110, 10
UNION SELECT 111, 10
UNION SELECT 112, 8
UNION SELECT 113, 24
UNION SELECT 114, 12
UNION SELECT 120, 19
UNION SELECT 121, 23
UNION SELECT 126, 23
UNION SELECT 130, 32
UNION SELECT 131, 25;
IF CHARINDEX(‘SQL Server 2005’, @@VERSION) > 0
INSERT dbo.DateTimeFormats(styleID, outputLength)
SELECT 127, 23; — 127 is new in 2005
UPDATE dbo.DateTimeFormats
SET outputSyntax = ‘CONVERT(CHAR(‘
+ RTRIM(outputLength) + ‘), CURRENT_TIMESTAMP, ‘
+ RTRIM(styleID) + ‘)’;
DECLARE
@sql VARCHAR(1024),
@style TINYINT,
@syntax VARCHAR(64);
DECLARE c CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT styleID, outputSyntax
FROM dbo.DateTimeFormats;
OPEN c; FETCH NEXT FROM c INTO @style, @syntax;
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql = ‘UPDATE dbo.DateTimeFormats
SET outputSample = ‘ + @syntax + ‘
WHERE styleID = ‘ + RTRIM(@style) + ‘;’;
EXEC(@sql);
FETCH NEXT FROM c INTO @style, @syntax;
END
CLOSE c; DEALLOCATE c;
SELECT
styleID,
outputSample,
outputSyntax
FROM
dbo.DateTimeFormats
WHERE
styleID = COALESCE(@styleID, styleID);
DROP TABLE dbo.DateTimeFormats;
END
GO
EXEC dbo.help_DateTimeFormats;
EXEC dbo.help_DateTimeFormats @styleID = 112;
— DROP PROCEDURE dbo.help_DateTimeFormats;
The output should look like this:
| 0 | Feb 22 2006 4:26PM | CONVERT(CHAR(19), CURRENT_TIMESTAMP, 0) |
| 1 | 02/22/06 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 1) |
| 2 | 06.02.22 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 2) |
| 3 | 22/02/06 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 3) |
| 4 | 22.02.06 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 4) |
| 5 | 22-02-06 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 5) |
| 6 | 22 Feb 06 | CONVERT(CHAR(9), CURRENT_TIMESTAMP, 6) |
| 7 | Feb 22, 06 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 7) |
| 8 | 16:26:08 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 8) |
| 9 | Feb 22 2006 4:26:08:020PM | CONVERT(CHAR(26), CURRENT_TIMESTAMP, 9) |
| 10 | 02-22-06 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 10) |
| 11 | 06/02/22 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 11) |
| 12 | 060222 | CONVERT(CHAR(6), CURRENT_TIMESTAMP, 12) |
| 13 | 22 Feb 2006 16:26:08:020 | CONVERT(CHAR(24), CURRENT_TIMESTAMP, 13) |
| 14 | 16:26:08:037 | CONVERT(CHAR(12), CURRENT_TIMESTAMP, 14) |
| 20 | 2006-02-22 16:26:08 | CONVERT(CHAR(19), CURRENT_TIMESTAMP, 20) |
| 21 | 2006-02-22 16:26:08.037 | CONVERT(CHAR(23), CURRENT_TIMESTAMP, 21) |
| 22 | 02/22/06 4:26:08 PM | CONVERT(CHAR(20), CURRENT_TIMESTAMP, 22) |
| 23 | 2006-02-22 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 23) |
| 24 | 16:26:08 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 24) |
| 25 | 2006-02-22 16:26:08.037 | CONVERT(CHAR(23), CURRENT_TIMESTAMP, 25) |
| 100 | Feb 22 2006 4:26PM | CONVERT(CHAR(19), CURRENT_TIMESTAMP, 100) |
| 101 | 02/22/2006 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 101) |
| 102 | 2006.02.22 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 102) |
| 103 | 22/02/2006 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 103) |
| 104 | 22.02.2006 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 104) |
| 105 | 22-02-2006 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 105) |
| 106 | 22 Feb 2006 | CONVERT(CHAR(11), CURRENT_TIMESTAMP, 106) |
| 107 | Feb 22, 2006 | CONVERT(CHAR(12), CURRENT_TIMESTAMP, 107) |
| 108 | 16:26:08 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 108) |
| 109 | Feb 22 2006 4:26:08:067PM | CONVERT(CHAR(26), CURRENT_TIMESTAMP, 109) |
| 110 | 02-22-2006 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 110) |
| 111 | 2006/02/22 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 111) |
| 112 | 20060222 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112) |
| 113 | 22 Feb 2006 16:26:08:067 | CONVERT(CHAR(24), CURRENT_TIMESTAMP, 113) |
| 114 | 16:26:08:067 | CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114) |
| 120 | 2006-02-22 16:26:08 | CONVERT(CHAR(19), CURRENT_TIMESTAMP, 120) |
| 121 | 2006-02-22 16:26:08.080 | CONVERT(CHAR(23), CURRENT_TIMESTAMP, 121) |
| 126 | 2006-02-22T16:26:08.080 | CONVERT(CHAR(23), CURRENT_TIMESTAMP, 126) |
| 127 | 2006-02-22T16:26:08.080 | CONVERT(CHAR(23), CURRENT_TIMESTAMP, 127) |
| 130 | 24 ???? 1427 4:26:08:080PM | CONVERT(CHAR(32), CURRENT_TIMESTAMP, 130) |
| 131 | 24/01/1427 4:26:08:080PM | CONVERT(CHAR(25), CURRENT_TIMESTAMP, 131) |