Multiple methods for converting datetime to varchar struct in SQL

I wrote this little desktop program to help me remember what style 112 didn't, or how to get a DATETIME column in HH: MM AM/PM. Basically, it is a valid style number to fill a table, and then generate the results of each style through these loops (and the syntax that generates this result ), in view of the current date and time.
Yes, it uses a cursor. This is a secondary feature, not part of your production environment, so I don't think the impact on performance should be a big problem. But if you have

The code is as follows:

Copy code

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 The 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 The union select 114, 12 Union select 120, 19 Union select 121, 23 Union select 126, 23 The 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 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 shoshould look like this: 0 Feb 22 2006 PM 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 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 PM 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,200 6 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)