SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 CONVERT function examplified
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SwePeso
Patron Saint of Lost Yaks

Sweden
30186 Posts

Posted - 03/14/2007 :  06:12:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 03/14/2007 06:12:34

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 03/14/2007 :  06:33:33  Show Profile  Reply with Quote
Excellent!!!!
Might be good to link to here from Kristens "checklist/FAQ"
Go to Top of Page

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 01/16/2009 :  06:11:19  Show Profile  Reply with Quote
Fabulous!

Many thanks to all for your help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000