<p>I need to convert a value which is in a <code>DateTime</code> variable into a <code>varchar</code> variable formatted as <code>yyyy-mm-dd</code> format (without time part).</p>
<p>How do I do that?</p>

[{'answer_id': 74394, 'author': 'Joel Coehoorn', 'author_id': 3043, 'author_profile': 'https://Stackoverflow.com/users/3043', 'pm_score': 8, 'selected': False, 'text': '<p>Try the following:</p>\n\n<pre><code>CONVERT(varchar(10), [MyDateTimecolumn], 20)\n</code></pre>\n\n<p>For a full date time and not just date do:   </p>\n\n<pre><code>CONVERT(varchar(23), [MyDateTimecolumn], 121)\n</code></pre>\n\n<p>See this page for convert styles:</p>\n\n<p><a href="http://msdn.microsoft.com/en-us/library/ms187928.aspx" rel="noreferrer">http://msdn.microsoft.com/en-us/library/ms187928.aspx</a><br>\nOR<br>\n<a href="http://www.w3schools.com/sql/func_convert.asp" rel="noreferrer">SQL Server CONVERT() Function</a></p>\n'}, {'answer_id': 74404, 'author': 'Per Hornshøj-Schierbeck', 'author_id': 11619, 'author_profile': 'https://Stackoverflow.com/users/11619', 'pm_score': 3, 'selected': False, 'text': '<p>Either <code>Cast</code> or <code>Convert</code>:</p>\n\n<p>Syntax for <code>CAST</code>:</p>\n\n<pre><code>CAST ( expression AS data_type [ (length ) ])\n</code></pre>\n\n<p>Syntax for <code>CONVERT</code>:</p>\n\n<pre><code>CONVERT ( data_type [ ( length ) ] , expression [ , style ] )\n</code></pre>\n\n<p><a href="http://msdn.microsoft.com/en-us/library/ms187928.aspx" rel="noreferrer">http://msdn.microsoft.com/en-us/library/ms187928.aspx</a></p>\n\n<p>Actually since you asked for a specific format:</p>\n\n<pre><code>REPLACE(CONVERT(varchar(10), Date, 102), \'.\', \'-\')\n</code></pre>\n'}, {'answer_id': 74431, 'author': 'Allan Wind', 'author_id': 9706, 'author_profile': 'https://Stackoverflow.com/users/9706', 'pm_score': 1, 'selected': False, 'text': '<p>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):</p>\n\n<pre><code>mysql&gt; select date(now());\n+-------------+\n| date(now()) |\n+-------------+\n| 2008-09-16  | \n+-------------+\n1 row in set (0.00 sec)\n</code></pre>\n'}, {'answer_id': 74438, 'author': 'Amy Patterson', 'author_id': 300930, 'author_profile': 'https://Stackoverflow.com/users/300930', 'pm_score': 2, 'selected': False, 'text': '<p>Try the following:</p>\n\n<pre><code>CONVERT(VARCHAR(10),GetDate(),102)\n</code></pre>\n\n<p>Then you would need to replace the "." with "-".</p>\n\n<p>Here is a site that helps\n<a href="http://www.mssqltips.com/tip.asp?tip=1145" rel="nofollow noreferrer">http://www.mssqltips.com/tip.asp?tip=1145</a></p>\n'}, {'answer_id': 74469, 'author': 'TonyOssa', 'author_id': 3276, 'author_profile': 'https://Stackoverflow.com/users/3276', 'pm_score': 9, 'selected': True, 'text': "<p>With Microsoft Sql Server:</p>\n\n<pre><code>--\n-- Create test case\n--\nDECLARE @myDateTime DATETIME\nSET @myDateTime = '2008-05-03'\n\n--\n-- Convert string\n--\nSELECT LEFT(CONVERT(VARCHAR, @myDateTime, 120), 10)\n</code></pre>\n"}, {'answer_id': 74473, 'author': 'Johnny Bravado', 'author_id': 12222, 'author_profile': 'https://Stackoverflow.com/users/12222', 'pm_score': -1, 'selected': False, 'text': '<p>You don\'t say what language but I am assuming <code>C#/.NET</code> because it has a native <code>DateTime</code> data type. In that case just convert it using the <code>ToString</code> method and use a format specifier such as:</p>\n\n<pre><code>DateTime d = DateTime.Today;\nstring result = d.ToString("yyyy-MM-dd");\n</code></pre>\n\n<p>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.</p>\n'}, {'answer_id': 138476, 'author': 'Andy Jones', 'author_id': 5096, 'author_profile': 'https://Stackoverflow.com/users/5096', 'pm_score': 2, 'selected': False, 'text': '<pre><code>declare @dt datetime\n\nset @dt = getdate()\n\nselect convert(char(10),@dt,120) \n</code></pre>\n\n<p>I have fixed data length of <code>char(10)</code> as you want a specific string format.</p>\n'}, {'answer_id': 6254392, 'author': 'Arek Bee', 'author_id': 664252, 'author_profile': 'https://Stackoverflow.com/users/664252', 'pm_score': 2, 'selected': False, 'text': '<p>Try:</p>\n\n<pre><code>select replace(convert(varchar, getdate(), 111),\'/\',\'-\');\n</code></pre>\n\n<p>More on <a href="http://www.mssqltips.com/tip.asp?tip=1145" rel="nofollow">ms sql tips</a></p>\n'}, {'answer_id': 6430393, 'author': "P's-SQL", 'author_id': 809074, 'author_profile': 'https://Stackoverflow.com/users/809074', 'pm_score': 3, 'selected': False, 'text': "<p>-- This gives you the time as 0 in format 'yyyy-mm-dd 00:00:00.000'</p>\n\n<pre><code>\nSELECT CAST( CONVERT(VARCHAR, GETDATE(), 101) AS DATETIME) ; \n</code></pre>\n"}, {'answer_id': 7040880, 'author': 'OldBuildingAndLoan', 'author_id': 70870, 'author_profile': 'https://Stackoverflow.com/users/70870', 'pm_score': 2, 'selected': False, 'text': "<p>The OP mentioned <strong>datetime</strong> format.  For me, the time part gets in the way.<br>\nI think it's a bit cleaner to remove the time portion (by casting datetime to date) before formatting.</p>\n\n<pre><code>convert( varchar(10), convert( date, @yourDate ) , 111 )\n</code></pre>\n"}, {'answer_id': 10819689, 'author': 'dmunozpa', 'author_id': 1017892, 'author_profile': 'https://Stackoverflow.com/users/1017892', 'pm_score': 3, 'selected': False, 'text': '<p>With Microsoft SQL Server:</p>\n\n<p>Use Syntax for CONVERT:</p>\n\n<pre><code>CONVERT ( data_type [ ( length ) ] , expression [ , style ] )\n</code></pre>\n\n<p>Example:</p>\n\n<pre><code>SELECT CONVERT(varchar,d.dateValue,1-9)\n</code></pre>\n\n<p>For the style you can find more info here: <a href="http://msdn.microsoft.com/en-us/library/ms187928.aspx" rel="noreferrer">MSDN - Cast and Convert (Transact-SQL)</a>.</p>\n'}, {'answer_id': 11587309, 'author': 'FCKOE', 'author_id': 1541843, 'author_profile': 'https://Stackoverflow.com/users/1541843', 'pm_score': 3, 'selected': False, 'text': '<p>You can use <code>DATEPART(DATEPART, VARIABLE)</code>. For example:</p>\n\n<pre class="lang-sql prettyprint-override"><code>DECLARE @DAY INT \nDECLARE @MONTH INT\nDECLARE @YEAR INT\nDECLARE @DATE DATETIME\n@DATE = GETDATE()\nSELECT @DAY = DATEPART(DAY,@DATE)\nSELECT @MONTH = DATEPART(MONTH,@DATE)\nSELECT @YEAR = DATEPART(YEAR,@DATE)\n</code></pre>\n'}, {'answer_id': 15621120, 'author': 'IvanSnek', 'author_id': 1899696, 'author_profile': 'https://Stackoverflow.com/users/1899696', 'pm_score': 2, 'selected': False, 'text': '<p>This is how I do it: <code>CONVERT(NVARCHAR(10), DATE1, 103) )</code></p>\n'}, {'answer_id': 17713768, 'author': 'Zar Shardan', 'author_id': 913845, 'author_profile': 'https://Stackoverflow.com/users/913845', 'pm_score': 5, 'selected': False, 'text': '<p>SQL Server 2012 has a new function , FORMAT: \n<a href="http://msdn.microsoft.com/en-us/library/ee634924.aspx">http://msdn.microsoft.com/en-us/library/ee634924.aspx</a></p>\n\n<p>and you can use custom date time format strings: <a href="http://msdn.microsoft.com/en-us/library/ee634398.aspx">http://msdn.microsoft.com/en-us/library/ee634398.aspx</a></p>\n\n<p>These pages imply it is also available on SQL2008R2, but I don\'t have one handy to test if that\'s the case.</p>\n\n<p>Example usage (Australian datetime): </p>\n\n<pre><code>FORMAT(VALUE,\'dd/MM/yyyy h:mm:ss tt\')\n</code></pre>\n'}, {'answer_id': 19537658, 'author': 'Colin', 'author_id': 150342, 'author_profile': 'https://Stackoverflow.com/users/150342', 'pm_score': 9, 'selected': False, 'text': "<p>Here's some test sql for all the styles.</p>\n\n<pre><code>DECLARE @now datetime\nSET @now = GETDATE()\nselect convert(nvarchar(MAX), @now, 0) as output, 0 as style \nunion select convert(nvarchar(MAX), @now, 1), 1\nunion select convert(nvarchar(MAX), @now, 2), 2\nunion select convert(nvarchar(MAX), @now, 3), 3\nunion select convert(nvarchar(MAX), @now, 4), 4\nunion select convert(nvarchar(MAX), @now, 5), 5\nunion select convert(nvarchar(MAX), @now, 6), 6\nunion select convert(nvarchar(MAX), @now, 7), 7\nunion select convert(nvarchar(MAX), @now, 8), 8\nunion select convert(nvarchar(MAX), @now, 9), 9\nunion select convert(nvarchar(MAX), @now, 10), 10\nunion select convert(nvarchar(MAX), @now, 11), 11\nunion select convert(nvarchar(MAX), @now, 12), 12\nunion select convert(nvarchar(MAX), @now, 13), 13\nunion select convert(nvarchar(MAX), @now, 14), 14\n--15 to 19 not valid\nunion select convert(nvarchar(MAX), @now, 20), 20\nunion select convert(nvarchar(MAX), @now, 21), 21\nunion select convert(nvarchar(MAX), @now, 22), 22\nunion select convert(nvarchar(MAX), @now, 23), 23\nunion select convert(nvarchar(MAX), @now, 24), 24\nunion select convert(nvarchar(MAX), @now, 25), 25\n--26 to 99 not valid\nunion select convert(nvarchar(MAX), @now, 100), 100\nunion select convert(nvarchar(MAX), @now, 101), 101\nunion select convert(nvarchar(MAX), @now, 102), 102\nunion select convert(nvarchar(MAX), @now, 103), 103\nunion select convert(nvarchar(MAX), @now, 104), 104\nunion select convert(nvarchar(MAX), @now, 105), 105\nunion select convert(nvarchar(MAX), @now, 106), 106\nunion select convert(nvarchar(MAX), @now, 107), 107\nunion select convert(nvarchar(MAX), @now, 108), 108\nunion select convert(nvarchar(MAX), @now, 109), 109\nunion select convert(nvarchar(MAX), @now, 110), 110\nunion select convert(nvarchar(MAX), @now, 111), 111\nunion select convert(nvarchar(MAX), @now, 112), 112\nunion select convert(nvarchar(MAX), @now, 113), 113\nunion select convert(nvarchar(MAX), @now, 114), 114\nunion select convert(nvarchar(MAX), @now, 120), 120\nunion select convert(nvarchar(MAX), @now, 121), 121\n--122 to 125 not valid\nunion select convert(nvarchar(MAX), @now, 126), 126\nunion select convert(nvarchar(MAX), @now, 127), 127\n--128, 129 not valid\nunion select convert(nvarchar(MAX), @now, 130), 130\nunion select convert(nvarchar(MAX), @now, 131), 131\n--132 not valid\norder BY style\n</code></pre>\n\n<p>Here's the result</p>\n\n<pre><code>output                   style\nApr 28 2014  9:31AM          0\n04/28/14                     1\n14.04.28                     2\n28/04/14                     3\n28.04.14                     4\n28-04-14                     5\n28 Apr 14                    6\nApr 28, 14                   7\n09:31:28                     8\nApr 28 2014  9:31:28:580AM   9\n04-28-14                     10\n14/04/28                     11\n140428                       12\n28 Apr 2014 09:31:28:580     13\n09:31:28:580                 14\n2014-04-28 09:31:28          20\n2014-04-28 09:31:28.580      21\n04/28/14  9:31:28 AM         22\n2014-04-28                   23\n09:31:28                     24\n2014-04-28 09:31:28.580      25\nApr 28 2014  9:31AM          100\n04/28/2014                   101\n2014.04.28                   102\n28/04/2014                   103\n28.04.2014                   104\n28-04-2014                   105\n28 Apr 2014                  106\nApr 28, 2014                 107\n09:31:28                     108\nApr 28 2014  9:31:28:580AM   109\n04-28-2014                   110\n2014/04/28                   111\n20140428                     112\n28 Apr 2014 09:31:28:580     113\n09:31:28:580                 114\n2014-04-28 09:31:28          120\n2014-04-28 09:31:28.580      121\n2014-04-28T09:31:28.580      126\n2014-04-28T09:31:28.580      127\n28 جمادى الثانية 1435  9:31:28:580AM    130\n28/06/1435  9:31:28:580AM    131\n</code></pre>\n\n<p>Make <code>nvarchar(max)</code> shorter to trim the time. For example:</p>\n\n<pre><code>select convert(nvarchar(11), GETDATE(), 0)\nunion select convert(nvarchar(max), GETDATE(), 0)\n</code></pre>\n\n<p>outputs:</p>\n\n<pre><code>May 18 2018\nMay 18 2018  9:57AM\n</code></pre>\n"}, {'answer_id': 23369972, 'author': 'Gabriel', 'author_id': 3112707, 'author_profile': 'https://Stackoverflow.com/users/3112707', 'pm_score': 1, 'selected': False, 'text': '<pre><code>CONVERT(VARCHAR, GETDATE(), 23)\n</code></pre>\n'}, {'answer_id': 27231940, 'author': 'Konstantin', 'author_id': 1665649, 'author_profile': 'https://Stackoverflow.com/users/1665649', 'pm_score': 2, 'selected': False, 'text': '<p>The shortest and the simplest way is :</p>\n\n<pre><code>DECLARE @now AS DATETIME = GETDATE()\n\nSELECT CONVERT(VARCHAR, @now, 23)\n</code></pre>\n'}, {'answer_id': 41594909, 'author': 'Ema.H', 'author_id': 2630447, 'author_profile': 'https://Stackoverflow.com/users/2630447', 'pm_score': 2, 'selected': False, 'text': '<p>You can convert your date in many formats, the syntaxe is simple to use :</p>\n\n<pre><code>CONVERT(\'TheTypeYouWant\', \'TheDateToConvert\', \'TheCodeForFormating\' * )\nCONVERT(NVARCHAR(10), DATE_OF_DAY, 103) =&gt; 15/09/2016\n</code></pre>\n\n<ul>\n<li>The code is an integer, here 3 is the third formating without century, if you want the century just change the code to 103.</li>\n</ul>\n\n<p><strong>In your case</strong>, i\'ve just converted and restrict size by nvarchar(10) like this :</p>\n\n<pre><code>CONVERT(NVARCHAR(10), MY_DATE_TIME, 120) =&gt; 2016-09-15\n</code></pre>\n\n<p>See more at : <a href="http://www.w3schools.com/sql/func_convert.asp" rel="nofollow noreferrer">http://www.w3schools.com/sql/func_convert.asp</a></p>\n\n<p><strong>Another solution</strong> (if your date is a Datetime) is a simple <strong>CAST</strong> :</p>\n\n<pre><code>CAST(MY_DATE_TIME as DATE) =&gt; 2016-09-15\n</code></pre>\n'}, {'answer_id': 45005103, 'author': 'Dilkhush', 'author_id': 7384154, 'author_profile': 'https://Stackoverflow.com/users/7384154', 'pm_score': 2, 'selected': False, 'text': "<p>Try this SQL:</p>\n\n<pre><code>select REPLACE(CONVERT(VARCHAR(24),GETDATE(),103),'/','_') + '_'+ \n       REPLACE(CONVERT(VARCHAR(24),GETDATE(),114),':','_')\n</code></pre>\n"}, {'answer_id': 53440678, 'author': 'Dilkhush', 'author_id': 7384154, 'author_profile': 'https://Stackoverflow.com/users/7384154', 'pm_score': 1, 'selected': False, 'text': "<pre><code>DECLARE @DateTime DATETIME\nSET @DateTime = '2018-11-23 10:03:23'\nSELECT CONVERT(VARCHAR(100),@DateTime,121 )\n</code></pre>\n"}, {'answer_id': 53486217, 'author': 'Peter Majko', 'author_id': 4528229, 'author_profile': 'https://Stackoverflow.com/users/4528229', 'pm_score': 2, 'selected': False, 'text': "<p>For SQL Server 2008+ You can use CONVERT and FORMAT together.</p>\n\n<p>For example, for European style (e.g. Germany) timestamp:</p>\n\n<pre><code>CONVERT(VARCHAR, FORMAT(GETDATE(), 'dd.MM.yyyy HH:mm:ss', 'de-DE'))\n</code></pre>\n"}, {'answer_id': 57322855, 'author': 'Beyhan', 'author_id': 2599859, 'author_profile': 'https://Stackoverflow.com/users/2599859', 'pm_score': 0, 'selected': False, 'text': "<p>Write a function</p>\n\n<pre><code>CREATE FUNCTION dbo.TO_SAP_DATETIME(@input datetime)\nRETURNS VARCHAR(14)\nAS BEGIN\n    DECLARE @ret VARCHAR(14)\n    SET @ret = COALESCE(SUBSTRING(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(26), @input, 25),'-',''),' ',''),':',''),1,14),'00000000000000');\n    RETURN @ret\nEND\n</code></pre>\n"}, {'answer_id': 61617060, 'author': 'Andres Galindo', 'author_id': 13475919, 'author_profile': 'https://Stackoverflow.com/users/13475919', 'pm_score': 1, 'selected': False, 'text': "<pre><code>select REPLACE(CONVERT(VARCHAR, FORMAT(GETDATE(), N'dd/MM/yyyy hh:mm:ss tt')),'.', '/')\n</code></pre>\n\n<p>will give <code>05/05/2020 10:41:05 AM</code> as a result</p>\n"}, {'answer_id': 67225951, 'author': 'Zain', 'author_id': 4281423, 'author_profile': 'https://Stackoverflow.com/users/4281423', 'pm_score': 0, 'selected': False, 'text': "<p>Simple use &quot;Convert&quot; and then use &quot;Format&quot; to get your desire date format</p>\n<pre><code>DECLARE @myDateTime DATETIME\nSET @myDateTime = '2008-05-03'\n\nSELECT FORMAT(CONVERT(date, @myDateTime ),'yyyy-MM-dd')\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/74385', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/7604/']