Multiple methods for converting datetime to varchar struct in SQL

Source: Internet
Author: User
This article is from a foreign website that describes how to convert the datetime Date and Time type in SQL to the varchar character type. If you need it, refer to.

This article is from a foreign website that describes how to convert the datetime Date and Time type in SQL to the varchar character type. If you need it, refer to.

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, then through these loops, and generate the results for each style (and the syntax for generating this result), given the current.
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:

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.