How to convert DateTime to VarChar
Here's some test sql for all the styles.
DECLARE @now datetimeSET @now = GETDATE()select convert(nvarchar(MAX), @now, 0) as output, 0 as style union select convert(nvarchar(MAX), @now, 1), 1union select convert(nvarchar(MAX), @now, 2), 2union select convert(nvarchar(MAX), @now, 3), 3union select convert(nvarchar(MAX), @now, 4), 4union select convert(nvarchar(MAX), @now, 5), 5union select convert(nvarchar(MAX), @now, 6), 6union select convert(nvarchar(MAX), @now, 7), 7union select convert(nvarchar(MAX), @now, 8), 8union select convert(nvarchar(MAX), @now, 9), 9union select convert(nvarchar(MAX), @now, 10), 10union select convert(nvarchar(MAX), @now, 11), 11union select convert(nvarchar(MAX), @now, 12), 12union select convert(nvarchar(MAX), @now, 13), 13union select convert(nvarchar(MAX), @now, 14), 14--15 to 19 not validunion select convert(nvarchar(MAX), @now, 20), 20union select convert(nvarchar(MAX), @now, 21), 21union select convert(nvarchar(MAX), @now, 22), 22union select convert(nvarchar(MAX), @now, 23), 23union select convert(nvarchar(MAX), @now, 24), 24union select convert(nvarchar(MAX), @now, 25), 25--26 to 99 not validunion select convert(nvarchar(MAX), @now, 100), 100union select convert(nvarchar(MAX), @now, 101), 101union select convert(nvarchar(MAX), @now, 102), 102union select convert(nvarchar(MAX), @now, 103), 103union select convert(nvarchar(MAX), @now, 104), 104union select convert(nvarchar(MAX), @now, 105), 105union select convert(nvarchar(MAX), @now, 106), 106union select convert(nvarchar(MAX), @now, 107), 107union select convert(nvarchar(MAX), @now, 108), 108union select convert(nvarchar(MAX), @now, 109), 109union select convert(nvarchar(MAX), @now, 110), 110union select convert(nvarchar(MAX), @now, 111), 111union select convert(nvarchar(MAX), @now, 112), 112union select convert(nvarchar(MAX), @now, 113), 113union select convert(nvarchar(MAX), @now, 114), 114union select convert(nvarchar(MAX), @now, 120), 120union select convert(nvarchar(MAX), @now, 121), 121--122 to 125 not validunion select convert(nvarchar(MAX), @now, 126), 126union select convert(nvarchar(MAX), @now, 127), 127--128, 129 not validunion select convert(nvarchar(MAX), @now, 130), 130union select convert(nvarchar(MAX), @now, 131), 131--132 not validorder BY style
Here's the result
output styleApr 28 2014 9:31AM 004/28/14 114.04.28 228/04/14 328.04.14 428-04-14 528 Apr 14 6Apr 28, 14 709:31:28 8Apr 28 2014 9:31:28:580AM 904-28-14 1014/04/28 11140428 1228 Apr 2014 09:31:28:580 1309:31:28:580 142014-04-28 09:31:28 202014-04-28 09:31:28.580 2104/28/14 9:31:28 AM 222014-04-28 2309:31:28 242014-04-28 09:31:28.580 25Apr 28 2014 9:31AM 10004/28/2014 1012014.04.28 10228/04/2014 10328.04.2014 10428-04-2014 10528 Apr 2014 106Apr 28, 2014 10709:31:28 108Apr 28 2014 9:31:28:580AM 10904-28-2014 1102014/04/28 11120140428 11228 Apr 2014 09:31:28:580 11309:31:28:580 1142014-04-28 09:31:28 1202014-04-28 09:31:28.580 1212014-04-28T09:31:28.580 1262014-04-28T09:31:28.580 12728 جمادى الثانية 1435 9:31:28:580AM 13028/06/1435 9:31:28:580AM 131
Make nvarchar(max)
shorter to trim the time. For example:
select convert(nvarchar(11), GETDATE(), 0)union select convert(nvarchar(max), GETDATE(), 0)
outputs:
May 18 2018May 18 2018 9:57AM
With Microsoft Sql Server:
---- Create test case--DECLARE @myDateTime DATETIMESET @myDateTime = '2008-05-03'---- Convert string--SELECT LEFT(CONVERT(VARCHAR, @myDateTime, 120), 10)
Try the following:
CONVERT(varchar(10), [MyDateTimecolumn], 20)
For a full date time and not just date do:
CONVERT(varchar(23), [MyDateTimecolumn], 121)
See this page for convert styles:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
OR
SQL Server CONVERT() Function