I need to convert a value which is in a DateTime variable into a varchar variable formatted as yyyy-mm-dd format (without time part).
How do I do that?
224 Answers
Here's some test sql for all the styles.
DECLARE @now datetime SET @now = GETDATE() select convert(nvarchar(MAX), @now, 0) as output, 0 as style union select convert(nvarchar(MAX), @now, 1), 1 union select convert(nvarchar(MAX), @now, 2), 2 union select convert(nvarchar(MAX), @now, 3), 3 union select convert(nvarchar(MAX), @now, 4), 4 union select convert(nvarchar(MAX), @now, 5), 5 union select convert(nvarchar(MAX), @now, 6), 6 union select convert(nvarchar(MAX), @now, 7), 7 union select convert(nvarchar(MAX), @now, 8), 8 union select convert(nvarchar(MAX), @now, 9), 9 union select convert(nvarchar(MAX), @now, 10), 10 union select convert(nvarchar(MAX), @now, 11), 11 union select convert(nvarchar(MAX), @now, 12), 12 union select convert(nvarchar(MAX), @now, 13), 13 union select convert(nvarchar(MAX), @now, 14), 14 --15 to 19 not valid union select convert(nvarchar(MAX), @now, 20), 20 union select convert(nvarchar(MAX), @now, 21), 21 union select convert(nvarchar(MAX), @now, 22), 22 union select convert(nvarchar(MAX), @now, 23), 23 union select convert(nvarchar(MAX), @now, 24), 24 union select convert(nvarchar(MAX), @now, 25), 25 --26 to 99 not valid union select convert(nvarchar(MAX), @now, 100), 100 union select convert(nvarchar(MAX), @now, 101), 101 union select convert(nvarchar(MAX), @now, 102), 102 union select convert(nvarchar(MAX), @now, 103), 103 union select convert(nvarchar(MAX), @now, 104), 104 union select convert(nvarchar(MAX), @now, 105), 105 union select convert(nvarchar(MAX), @now, 106), 106 union select convert(nvarchar(MAX), @now, 107), 107 union select convert(nvarchar(MAX), @now, 108), 108 union select convert(nvarchar(MAX), @now, 109), 109 union select convert(nvarchar(MAX), @now, 110), 110 union select convert(nvarchar(MAX), @now, 111), 111 union select convert(nvarchar(MAX), @now, 112), 112 union select convert(nvarchar(MAX), @now, 113), 113 union select convert(nvarchar(MAX), @now, 114), 114 union select convert(nvarchar(MAX), @now, 120), 120 union select convert(nvarchar(MAX), @now, 121), 121 --122 to 125 not valid union select convert(nvarchar(MAX), @now, 126), 126 union select convert(nvarchar(MAX), @now, 127), 127 --128, 129 not valid union select convert(nvarchar(MAX), @now, 130), 130 union select convert(nvarchar(MAX), @now, 131), 131 --132 not valid order BY style Here's the result
output style Apr 28 2014 9:31AM 0 04/28/14 1 14.04.28 2 28/04/14 3 28.04.14 4 28-04-14 5 28 Apr 14 6 Apr 28, 14 7 09:31:28 8 Apr 28 2014 9:31:28:580AM 9 04-28-14 10 14/04/28 11 140428 12 28 Apr 2014 09:31:28:580 13 09:31:28:580 14 2014-04-28 09:31:28 20 2014-04-28 09:31:28.580 21 04/28/14 9:31:28 AM 22 2014-04-28 23 09:31:28 24 2014-04-28 09:31:28.580 25 Apr 28 2014 9:31AM 100 04/28/2014 101 2014.04.28 102 28/04/2014 103 28.04.2014 104 28-04-2014 105 28 Apr 2014 106 Apr 28, 2014 107 09:31:28 108 Apr 28 2014 9:31:28:580AM 109 04-28-2014 110 2014/04/28 111 20140428 112 28 Apr 2014 09:31:28:580 113 09:31:28:580 114 2014-04-28 09:31:28 120 2014-04-28 09:31:28.580 121 2014-04-28T09:31:28.580 126 2014-04-28T09:31:28.580 127 28 جمادى الثانية 1435 9:31:28:580AM 130 28/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 2018 May 18 2018 9:57AM With Microsoft Sql Server:
-- -- Create test case -- DECLARE @myDateTime DATETIME SET @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:
OR
SQL Server CONVERT() Function
SQL Server 2012 has a new function , FORMAT:
and you can use custom date time format strings:
These pages imply it is also available on SQL2008R2, but I don't have one handy to test if that's the case.
Example usage (Australian datetime):
FORMAT(VALUE,'dd/MM/yyyy h:mm:ss tt') You can use DATEPART(DATEPART, VARIABLE). For example:
DECLARE @DAY INT DECLARE @MONTH INT DECLARE @YEAR INT DECLARE @DATE DATETIME @DATE = GETDATE() SELECT @DAY = DATEPART(DAY,@DATE) SELECT @MONTH = DATEPART(MONTH,@DATE) SELECT @YEAR = DATEPART(YEAR,@DATE) Either Cast or Convert:
Syntax for CAST:
CAST ( expression AS data_type [ (length ) ]) Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) Actually since you asked for a specific format:
REPLACE(CONVERT(varchar(10), Date, 102), '.', '-') -- This gives you the time as 0 in format 'yyyy-mm-dd 00:00:00.000'
SELECT CAST( CONVERT(VARCHAR, GETDATE(), 101) AS DATETIME) ; With Microsoft SQL Server:
Use Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) Example:
SELECT CONVERT(varchar,d.dateValue,1-9) For the style you can find more info here: MSDN - Cast and Convert (Transact-SQL).
For SQL Server 2008+ You can use CONVERT and FORMAT together.
For example, for European style (e.g. Germany) timestamp:
CONVERT(VARCHAR, FORMAT(GETDATE(), 'dd.MM.yyyy HH:mm:ss', 'de-DE')) Try:
select replace(convert(varchar, getdate(), 111),'/','-'); More on ms sql tips
The OP mentioned datetime format. For me, the time part gets in the way.
I think it's a bit cleaner to remove the time portion (by casting datetime to date) before formatting.
convert( varchar(10), convert( date, @yourDate ) , 111 ) This is how I do it: CONVERT(NVARCHAR(10), DATE1, 103) )
Try the following:
CONVERT(VARCHAR(10),GetDate(),102) Then you would need to replace the "." with "-".
declare @dt datetime set @dt = getdate() select convert(char(10),@dt,120) I have fixed data length of char(10) as you want a specific string format.
The shortest and the simplest way is :
DECLARE @now AS DATETIME = GETDATE() SELECT CONVERT(VARCHAR, @now, 23) You can convert your date in many formats, the syntaxe is simple to use :
CONVERT('TheTypeYouWant', 'TheDateToConvert', 'TheCodeForFormating' * ) CONVERT(NVARCHAR(10), DATE_OF_DAY, 103) => 15/09/2016 - The code is an integer, here 3 is the third formating without century, if you want the century just change the code to 103.
In your case, i've just converted and restrict size by nvarchar(10) like this :
CONVERT(NVARCHAR(10), MY_DATE_TIME, 120) => 2016-09-15 Another solution (if your date is a Datetime) is a simple CAST :
CAST(MY_DATE_TIME as DATE) => 2016-09-15 Try this SQL:
select REPLACE(CONVERT(VARCHAR(24),GETDATE(),103),'/','_') + '_'+ REPLACE(CONVERT(VARCHAR(24),GETDATE(),114),':','_') You did not say which database, but with mysql here is an easy way to get a date from a timestamp (and the varchar type conversion should happen automatically):
mysql> select date(now()); +-------------+ | date(now()) | +-------------+ | 2008-09-16 | +-------------+ 1 row in set (0.00 sec) CONVERT(VARCHAR, GETDATE(), 23) DECLARE @DateTime DATETIME SET @DateTime = '2018-11-23 10:03:23' SELECT CONVERT(VARCHAR(100),@DateTime,121 ) select REPLACE(CONVERT(VARCHAR, FORMAT(GETDATE(), N'dd/MM/yyyy hh:mm:ss tt')),'.', '/') will give 05/05/2020 10:41:05 AM as a result
Write a function
CREATE FUNCTION dbo.TO_SAP_DATETIME(@input datetime) RETURNS VARCHAR(14) AS BEGIN DECLARE @ret VARCHAR(14) SET @ret = COALESCE(SUBSTRING(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(26), @input, 25),'-',''),' ',''),':',''),1,14),'00000000000000'); RETURN @ret END Simple use "Convert" and then use "Format" to get your desire date format
DECLARE @myDateTime DATETIME SET @myDateTime = '2008-05-03' SELECT FORMAT(CONVERT(date, @myDateTime ),'yyyy-MM-dd') You don't say what language but I am assuming C#/.NET because it has a native DateTime data type. In that case just convert it using the ToString method and use a format specifier such as:
DateTime d = DateTime.Today; string result = d.ToString("yyyy-MM-dd"); However, I would caution against using this in a database query or concatenated into a SQL statement. Databases require a specific formatting string to be used. You are better off zeroing out the time part and using the DateTime as a SQL parameter if that is what you are trying to accomplish.
2
