How to get a date in YYYY-MM-DD format from a TSQL datetime field

Question

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

User · Answer

I would use   CONVERT char 10  GETDATE   126

User · Answer

This solution works for me  simple and effective  with 126 too   CONVERT NVARCHAR MAX   CAST GETDATE   as date   120

User · Answer

From SQL Server 2008 you can do this  CONVERT date getdate

User · Answer

SELECT CONVERT NVARCHAR 20   GETDATE    23

User · Answer

SELECT CONVERT char 10   GetDate   126    Limiting the size of the varchar chops of the hour portion that you don t want

User · Answer

For those who would want the time part as well  I did   the following snippet may help  SELECT convert varchar  getdate    120  -- yyyy-mm-dd hh mm ss 24h  SELECT convert varchar  getdate    121  -- yyyy-mm-dd hh mm ss mmm SELECT convert varchar  getdate    126  -- yyyy-mm-ddThh mm ss mmm                               --example -- 2008-10-02T10 52 47 513

User · Answer

replace convert varchar  getdate    111        -     Will also do trick without  chopping anything off

User · Answer

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

User · Answer

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

User · Answer

If you want to use it as a date instead of a varchar again afterwards  don t forget to convert it back   select convert datetime CONVERT char 10   GetDate   126

User · Answer

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

User · Answer

If your source date format is all messed up  try something along the lines of   select convert nvarchar 50  year a messedupDate    -    case when len convert nvarchar 50  month a messedupDate    1      then  0   convert nvarchar 50  month a messedupDate    -       else convert nvarchar 50  month a messedupDate   end    case when len convert nvarchar 50  day a messedupDate    1      then  0   convert nvarchar 50  day a messedupDate    -      else convert nvarchar 50  day a messedupDate   end   from messytable a

User · Answer

Starting with SQL Server 2012  original question is for 2000    SELECT FORMAT GetDate     yyyy-MM-dd

User · Answer

One other way     CONVERT varchar  DATEPART yyyy   datetime           CONVERT varchar  DATEPART mm   datetime           CONVERT varchar  DATEPART dd   datetime

User · Answer

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  gt   CONVERT DATETIME   2010-02-14   126  AND ToDate  lt   DATEADD dd  1  CONVERT DATETIME   2010-03-14   126    -- This will Include the whole day of 14th March 2010 --WHERE ID  1 AND FromDate  gt   CONVERT DATETIME   2010-02-14   126  AND ToDate  lt  DATEADD dd  1  CONVERT DATETIME   2010-03-14   126

User · Answer

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

User · Answer

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 https   docs microsoft com en-us sql t-sql functions current-timestamp-transact-sql view sql-server-ver15

User · Answer

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

User · Answer

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

User · Answer

For YYYYMMDD try  select convert varchar getDate   112    I have only tested on SQLServer2008

User · Answer

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

User · Answer

SELECT convert varchar  getdate    100  -- mon dd yyyy hh mmAM  SELECT convert varchar  getdate    101  -- mm dd yyyy     10 02 2008                    SELECT convert varchar  getdate    102  -- yyyy mm dd     2008 10 02             SELECT convert varchar  getdate    103  -- dd mm yyyy  SELECT convert varchar  getdate    104  -- dd mm yyyy  SELECT convert varchar  getdate    105  -- dd-mm-yyyy  SELECT convert varchar  getdate    106  -- dd mon yyyy  SELECT convert varchar  getdate    107  -- mon dd  yyyy  SELECT convert varchar  getdate    108  -- hh mm ss  SELECT convert varchar  getdate    109  -- mon dd yyyy hh mm ss mmmAM  or PM   SELECT convert varchar  getdate    110  -- mm-dd-yyyy  SELECT convert varchar  getdate    111  -- yyyy mm dd  SELECT convert varchar  getdate    112  -- yyyymmdd  SELECT convert varchar  getdate    113  -- dd mon yyyy hh mm ss mmm  SELECT convert varchar  getdate    114  -- hh mm ss mmm 24h   SELECT convert varchar  getdate    120  -- yyyy-mm-dd hh mm ss 24h   SELECT convert varchar  getdate    121  -- yyyy-mm-dd hh mm ss mmm  SELECT convert varchar  getdate    126  -- yyyy-mm-ddThh mm ss mmm

User · Answer

Using a CASE statement for each of the convert   cast functions always works for me      Please replace tableXXXXY with your table name  and issueDate dat with the name of your datetime field in that table    SELECT  issueDate dat  CONVERT varchar  DATEPART yyyy  issuedate dat    AS issueDateYYYY   CASE WHEN  len CONVERT varchar  DATEPART mm  issuedate dat     lt  2  THEN  0   CONVERT varchar  DATEPART mm  issuedate dat   ELSE CONVERT varchar  DATEPART mm  issuedate dat   END AS  issueDateMM   CASE WHEN  len CONVERT varchar  DATEPART dd  issuedate dat     lt 2  THEN  0   CONVERT varchar  DATEPART dd  issuedate dat   ELSE CONVERT varchar  DATEPART dd  issuedate dat   END AS issueDateDD FROM            tableXXXXY   Hope this was helpful  chagbert

User · Answer

IFormatProvider culture   new System Globalization CultureInfo  fr-FR   true    cmdGetPaymentStatement Parameters AddWithValue   pStartDate   DateTime Parse  22 12 2017   culture  System Globalization DateTimeStyles AssumeLocal   IsNullable   true

[sql-server] How to get a date in YYYY-MM-DD format from a TSQL datetime field?

Examples related to sql-server

Examples related to tsql