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

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 programatically 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.

how to convert date from yyyyMMdd format to mm-dd-yyyy format using DateTime.TryParse

I have a string which contains date in yyyyMMdd format. I want to convert that date into system date format, using ConvertTo.DateTime() method or any other simple method. I’ve used: string time = 198

In Javascript how can i get todays date in this format yyyymmdd

Like i need to get todays date in format like 20120924 (yyyymmdd).How can i get this in javascript.

how to convert date from yyyyMMdd format to mm-dd-yyyy fomrat

I want to convert a string which contains date in yyyyMMdd formate to mm-dd-yyyy DateTime format how do i get it. thanks in advance.

c#.net convert date yyyyMMdd to system.datetime format.? [duplicate]

Possible Duplicate: how to convert date from yyyyMMdd format to mm-dd-yyyy fomrat i have string which contains date in yyyyMMdd format . I want to convert that date in to system date format. using C

How to get date from datetime format

I have a datetime for eg:23/9/2009 10:00:00 AM .I want to get date from datetime.Means 23/9/2009 .Can anybody help

Converting date/time in YYYYMMDD/HHMMSS format to Python datetime

I have a date in YYYYMMDD format and a time in HHMMSS format as strings in the 4th and 5th elements in a list. I.E.: data[4] = ‘20100304’ data[5] = ‘082835’ I am creating an instance of datetime (in

How to parse a date in format “YYYYmmdd” in JavaScript?

This is a noob question: How to parse a date in format YYYYmmdd without external libraries ? If the input string is not in this format I would like to get invalid Date (or undefined if it will be ea

How to extract date from a DateNow() in YYYYMMDD format in SQL Server

Got a specific requirement that we should extract only date and in YYYYMMDD format from GETDATE() in Sql-Server 2005. How can I accomplish this. Thanks

Get String in YYYYMMDD format from JS date object?

I’m trying to use JS to turn a date object into a String in YYYMMDD format. Is there an easier way than concatenating Date.getYear() Date.getMonth(), and Date.getDay()?

Get Date in YYYYMMDD format in windows batch file [duplicate]

This question already has an answer here: Windows batch: formatted date into variable 4 answers I need to get the date in YYYYMMDD format in batch file. I am doing this using : set mydate=%dat

Answers

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)
SELECT CONVERT(char(10), GetDate(),126)

Limiting the size of the varchar chops of the hour portion that you don’t want.

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.

SELECT CONVERT(NVARCHAR(20), GETDATE(), 23)

I would use:

CONVERT(char(10),GETDATE(),126)

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")

The BOL description for 126 is a bit confusion (never found an explanation for “T”).

For YYYYMMDD try

select convert(varchar,getDate(),112)

I have only tested on SQLServer2008.

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))

One other way…

CONVERT(varchar, DATEPART(yyyy, @datetime)) + '/' + CONVERT(varchar, DATEPART(mm, @datetime)) + '/' + CONVERT(varchar, DATEPART(dd, @datetime)) 
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))
replace(convert(varchar, getdate(), 111), '/','-')

Will also do trick without “chopping anything off”.

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

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

Using a CASE statement for each of the convert / cast functions always works for me:

SELECT issueDate_dat

, CONVERT(varchar, DATEPART(yyyy, issuedate_dat))  AS issueDateYYYY
, CASE WHEN (len(CONVERT(varchar, DATEPART(mm, issuedate_dat))) < 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))) <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.

Try this for convert your date:

   $date="07/22/2014";

    $get = date("Y-m-d", strtotime(stripslashes($date)));
echo $get; //Your result is: 2014-07-22

Starting with SQL Server 2012 (original question is for 2000):

SELECT FORMAT(GetDate(), ‘yyyy-MM-dd’)

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

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

This solution works for me, simple and effective (with 126 too)

CONVERT(NVARCHAR(MAX), CAST(GETDATE() as date), 120)