mysql datetime to varchar_sql中datetime转换成varchar字符型多种方法

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)