CODEWITHSUNDEEP
sqlsql-server-2005datetimeformatting
TheTechGuy
TheTechGuy

Reputation: 17354

Formatting date in SQL?

I would like my date to be YYYY-MM-DD (DD-MM-YYYY), I can't seem to convert into this form. I have already looked at CAST and Convert T-SQL.

As a general question can I format the date according to my need such as DD-MM (YEAR) etc. You can easily do that in MYSQL.

SELECT GETDATE() // start with this query

Upvotes: 1

Views: 271

Answers (4)

John Fisher
John Fisher

Reputation: 22721

This is definitely NOT the fastest option, but it matches what you requested and may be useful to someone else in the future.

DECLARE @date DATETIME
SET @date = GETDATE()
SELECT CAST(DATEPART(YYYY, @date) AS NVARCHAR(4)) + '-' 
 + CAST(DATEPART(MM, @date) AS NVARCHAR(2)) + '-'
 + CAST(DATEPART(DD, @date) AS NVARCHAR(2)) + ' ('
 + CAST(DATEPART(DD, @date) AS NVARCHAR(2)) + '-'
 + CAST(DATEPART(MM, @date) AS NVARCHAR(2)) + '-'
 + CAST(DATEPART(YYYY, @date) AS NVARCHAR(4)) + ')'

It outputs:

2011-11-7 (7-11-2011)

Upvotes: 0

James Johnson
James Johnson

Reputation: 46047

Try format 120:

SELECT CONVERT(VARCHAR, GETDATE(), 120) -- result: 2011-11-07 13:48:45

If you want to truncate this so only the date part is returned, specify a size constraint on the VARCHAR conversion:

SELECT CONVERT(VARCHAR(10), GETDATE(), 120) -- result: 2011-11-07

Upvotes: 0

James Hill
James Hill

Reputation: 61793

To get the date without the time:

--char(10) limits the length to 10 characters thereby removing the time stamp
SELECT CONVERT(char(10), GetDate(),126) -- Output: 2011-11-07

To get the date with the time stamp:

SELECT CONVERT(VARCHAR, GetDate(), 126) --Output: 2011-11-07T13:42:25.913

See MSDN docs for all possible formats.

Upvotes: 1

Nelson Miranda
Nelson Miranda

Reputation: 5554

SELECT CONVERT(VARCHAR(19), GETDATE(), 120) /*YYYY-MM-DD HH:MI:SS(24h)*/

SELECT CONVERT(VARCHAR(23), GETDATE(), 126) /*YYYY-MM-DDTHH:MM:SS:MMM*/

SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]

SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]

Upvotes: 0

Related Questions