How do I retrieve a date from SQL Server in YYYY-MM-DD format? I need this to work with SQL Server 2000 and up. Is there a simple way to perform this in SQL Server or would it be easier to convert it programmatically after I retrieve the result set?
I've read the CAST and CONVERT on Microsoft Technet, but the format I want isn't listed and changing the date format isn't an option.
SELECT CONVERT(char(10), GetDate(),126)
Limiting the size of the varchar chops of the hour portion that you don't want.
The form you are after is listed in the books online documentation.
http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx
For example, try the following:
select convert(varchar,getDate(),120)
select convert(varchar(10),getDate(),120)
The convert function with the format specifier 120 will give you the format "yyyy-MM-dd HH:mm:ss", so you just have to limit the length to 10 to get only the date part:
convert(varchar(10), theDate, 120)
However, formatting dates is generally better to do in the presentation layer rather than in the database or business layer. If you return the date formatted from the database, then the client code has to parse it to a date again if it needs to do any calculations on it.
Example in C#:
theDate.ToString("yyyy-MM-dd")
I'm not sure why the simplest way has been ignored/omitted in the answers above:
SELECT FORMAT(GetDate(),'yyyy-MM-dd');--= 2020-01-02
SELECT FORMAT(GetDate(),'dd MMM yyyy HH:mm:ss');-- = 02 Jan 2020 08:08:08
I prefer the second one because whichever language you speak, you will understand what date it is!
Also SQL Server always 'understands' it when you send that to your save procedure, regardless of which regional formats are set in the computers - I always use full year (yyyy), month name (MMM) and 24 hour format (capital HH) for hour in my programming.
In case someone wants to do it the other way around and finds this.
select convert(datetime, '12.09.2014', 104)
This converts a string in the German date format to a datetime object.
Why 104? See here: http://msdn.microsoft.com/en-us/library/ms187928.aspx
In your cast and convert link, use style 126 thus:
CONVERT (varchar(10), DTvalue, 126)
This truncates the time. Your requirement to have it in yyyy-mm-dd means it must be a string datatype and datetime.
Frankly though, I'd do it on the client unless you have good reasons not to.
You may also use. This is by using the new datatype DATE. May not work in all previous versions, but greatly simplified to use in later version.
SELECT CAST(getdate() AS DATE)
SELECT LEFT(CAST(getdate() AS DATE), 7)
SELECT Code,Description FROM TABLE
-- This will Include only date part of 14th March 2010. Any date with date companents will not be considered.
WHERE ID= 1 AND FromDate >= CONVERT(DATETIME, '2010-02-14', 126) AND ToDate <= DATEADD(dd, 1, CONVERT(DATETIME, '2010-03-14', 126))
-- This will Include the whole day of 14th March 2010
--WHERE ID= 1 AND FromDate >= CONVERT(DATETIME, '2010-02-14', 126) AND ToDate < DATEADD(dd, 1, CONVERT(DATETIME, '2010-03-14', 126))
Seems unnecessary to do any strange things, if you want your date to be seperated by slash. Just escape it with a backslash. Otherwise you will end up with a dot.
SELECT FORMAT(GETDATE(),'yyyy\/MM');
Tested on SQL Server 2016
As string processing is expensive, and FORMAT more so, I am surprised that Asher/Aaron Dietz response is not higher, if not top; the question is seeking ISO 8601 date, and isn't specifically requesting it as a string type.
The most efficient method would be any of these (I've included the answer Asher/Aaron Dietz have already suggested for completeness):
All versions
select cast(getdate() as date)
select convert(date, getdate())
2008 and higher
select convert(date, current_timestamp)
ANSI SQL equivalent 2008 and higher
select cast(current_timestamp as date)
References:
https://sqlperformance.com/2015/06/t-sql-queries/format-is-nice-and-all-but
https://en.wikipedia.org/wiki/ISO_8601
https://www.w3schools.com/sql/func_sqlserver_current_timestamp.asp