CODEWITHSUNDEEP
sqlt-sql
Sanjna Malpani
Sanjna Malpani

Reputation: 175

Convert to DATE T-SQL

I have a field with date in the format dd.mm.yyyy E.x. 29.05.2016. I want to SELECT it as DATE but I get an error when I try the following:

CAST([PublishedDate] AS DATE) AS PublishedOn

Conversion failed when converting date and/or time from character string.

Reading this I tried the following:

 CONVERT(VARCHAR(10), [PublishedDate], 126)  AS PublishedDate

But it doesn't change the format. How do I SELECT to have it in YYYY-MM-DD format.

EDIT: I have 3 different date fields with all three having different formats: dd.mm.yyyy | yyyy-mm-dd | yyyy/mm/dd. I want to select all of them with the same format. Since I use this query to build reports, right now I have dates in different formats. Doing the following:

CONVERT(VARCHAR(10), [PublishedDate], 104) AS PublishedOn
CONVERT(VARCHAR(10), [ValidFromDate], 104)  AS ValidFrom

Gives me the following output

Upvotes: 1

Views: 6566

Answers (3)

Henrik Staun Poulsen
Henrik Staun Poulsen

Reputation: 13864

If you have a newer version of SQL Server, you may try

SELECT COALESCE(TRY_CAST(adate AS DATE), TRY_convert(DATE, adate, 126), TRY_convert(DATE, adate, 104)), format
FROM (
VALUES ('26.04.2017', 'dkformat') , 
('01.01.2017', 'EU format'), 
('12.12.12', 'unknown format')
) a(aDate, format)

But you are going down a dangerous path, when you are trying to guess what format your source is in. Been there, done that, had the t-shirt.

I still hate the 0.1% conversions that are wrong.

This solution will result in NULLs, so you can see where your conversion did not succeed.

Upvotes: 1

HoneyBadger
HoneyBadger

Reputation: 15140

You should use the right format, in your case it would be:

CONVERT(date, [PublishedDate], 104)  AS PublishedDate

Also, once it's in a date, datetime or other date datatype, it doesn't have a format at all.

edit: Once you have your values in a date datatype, of course you can recast to a varchar to get the visual representation of the date you need.

edit2: If you want a date datatype, you should convert to date: CONVERT(DATE, [your column], [your format]). If you want a nvarchar datatype, you should convert to nvarchar: CONVERT(nvarchar(x), [your column], [your format]).

You have an nvarchar that you want to display in a certain format, so you should first convert to date, then back to varchar (I doubt you need unicode):

CONVERT(VARCHAR(10), CONVERT(date, [PublishedDate], 104), 126)

The 104 you have to change for columns that are currently in a different format.

The best solution by far, is to change the datatypes to date. That is a bit of work, but definitely worthwhile.

Upvotes: 4

berserck
berserck

Reputation: 500

If you really want the string do like this:

CONVERT(char(10), CONVERT(date,[date], 104),126)  AS PublishedDate

Convert the string to date, using the 104 format (dd.mm.yyyy), as it is your original format, then convert the date into string, using the 126 format (yyy-mm-dd)

Upvotes: 1

Related Questions