CONVERT function examplified - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

CONVERT function examplified

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 06:12:06

Maybe it's easier to direct beginners here, rather than point them to Books Online?

SELECT	CURRENT_TIMESTAMP AS [Right now],
	CONVERT(VARCHAR, CURRENT_TIMESTAMP, Style) AS [Formatted text],
	Style AS [Style used],
	'CONVERT(VARCHAR, CURRENT_TIMESTAMP, ' + CAST(Style AS VARCHAR) + ')' AS Example
FROM	(
		SELECT	0 as Style UNION ALL
		SELECT	100 UNION ALL
		SELECT	1 UNION ALL
		SELECT	101 UNION ALL
		SELECT	2 UNION ALL
		SELECT	102 UNION ALL
		SELECT	3 UNION ALL
		SELECT	103 UNION ALL
		SELECT	4 UNION ALL
		SELECT	104 UNION ALL
		SELECT	5 UNION ALL
		SELECT	105 UNION ALL
		SELECT	6 UNION ALL
		SELECT	106 UNION ALL
		SELECT	7 UNION ALL
		SELECT	107 UNION ALL
		SELECT	8 UNION ALL
		SELECT	108 UNION ALL
		SELECT	9 UNION ALL
		SELECT	109 UNION ALL
		SELECT	10 UNION ALL
		SELECT	110 UNION ALL
		SELECT	11 UNION ALL
		SELECT	111 UNION ALL
		SELECT	12 UNION ALL
		SELECT	112 UNION ALL
		SELECT	13 UNION ALL
		SELECT	113 UNION ALL
		SELECT	14 UNION ALL
		SELECT	114 UNION ALL
		SELECT	20 UNION ALL
		SELECT	120 UNION ALL
		SELECT	21 UNION ALL
		SELECT	121 UNION ALL
		SELECT	126 UNION ALL
		SELECT	127 UNION ALL
		SELECT	130 UNION ALL
		SELECT	131
	) AS x

Output is

Right now			Formatted text			Style	Example
-----------------------		--------------------------	-----	----------------------------------------
2007-03-14 11:00:12.153		Mar 14 2007 11:00AM		    0	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 0)
2007-03-14 11:00:12.153		Mar 14 2007 11:00AM		  100	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 100)
2007-03-14 11:00:12.153		03/14/07			    1	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 1)
2007-03-14 11:00:12.153		03/14/2007			  101	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)
2007-03-14 11:00:12.153		07.03.14			    2	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 2)
2007-03-14 11:00:12.153		2007.03.14			  102	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 102)
2007-03-14 11:00:12.153		14/03/07			    3	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 3)
2007-03-14 11:00:12.153		14/03/2007			  103	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 103)
2007-03-14 11:00:12.153		14.03.07			    4	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 4)
2007-03-14 11:00:12.153		14.03.2007			  104	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 104)
2007-03-14 11:00:12.153		14-03-07			    5	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 5)
2007-03-14 11:00:12.153		14-03-2007			  105	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 105)
2007-03-14 11:00:12.153		14 Mar 07			    6	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 6)
2007-03-14 11:00:12.153		14 Mar 2007			  106	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 106)
2007-03-14 11:00:12.153		Mar 14, 07			    7	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 7)
2007-03-14 11:00:12.153		Mar 14, 2007			  107	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 107)
2007-03-14 11:00:12.153		11:00:12			    8	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 8)
2007-03-14 11:00:12.153		11:00:12			  108	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 108)
2007-03-14 11:00:12.153		Mar 14 2007 11:00:12:153AM	    9	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 9)
2007-03-14 11:00:12.153		Mar 14 2007 11:00:12:153AM	  109	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 109)
2007-03-14 11:00:12.153		03-14-07			   10	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 10)
2007-03-14 11:00:12.153		03-14-2007			  110	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 110)
2007-03-14 11:00:12.153		07/03/14			   11	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 11)
2007-03-14 11:00:12.153		2007/03/14			  111	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 111)
2007-03-14 11:00:12.153		070314				   12	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 12)
2007-03-14 11:00:12.153		20070314			  112	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 112)
2007-03-14 11:00:12.153		14 Mar 2007 11:00:12:153	   13	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 13)
2007-03-14 11:00:12.153		14 Mar 2007 11:00:12:153	  113	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 113)
2007-03-14 11:00:12.153		11:00:12:153			   14	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 14)
2007-03-14 11:00:12.153		11:00:12:153			  114	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 114)
2007-03-14 11:00:12.153		2007-03-14 11:00:12		   20	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 20)
2007-03-14 11:00:12.153		2007-03-14 11:00:12		  120	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 120)
2007-03-14 11:00:12.153		2007-03-14 11:00:12.153		   21	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 21)
2007-03-14 11:00:12.153		2007-03-14 11:00:12.153		  121	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 121)
2007-03-14 11:00:12.153		2007-03-14T11:00:12.153		  126	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 126)
2007-03-14 11:00:12.153		2007-03-14T11:00:12.153		  127	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 127)
2007-03-14 11:00:12.153		25 ??? 1428 11:00:12:153AM	  130	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 130)
2007-03-14 11:00:12.153		25/02/1428 11:00:12:153AM	  131	CONVERT(VARCHAR, CURRENT_TIMESTAMP, 131)

Peter Larsson
Helsingborg, Sweden

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-03-14 : 06:33:33

Excellent!!!!
Might be good to link to here from Kristens "checklist/FAQ"

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-01-16 : 06:11:19

Fabulous!

Many thanks to all for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources