How to convert DateTime to VarChar How to convert DateTime to VarChar sql-server sql-server

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