Convert YYYYMMDD to DATE

Question

I have a bunch of dates in varchar like this:

20080107
20090101
20100405
...

How do I convert them to a date format like this:

2008-01-07
2009-01-01
2010-04-05

I've tried using this:

SELECT [FIRST_NAME]
      ,[MIDDLE_NAME]
      ,[LAST_NAME]      
      ,cast([GRADUATION_DATE] as date)      
  FROM mydb

But get this message:

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.

Slating

Hmmm.. a littleconfused here. Isn't that conversion automatically handled? – Abhenry

try SELECT CONVERT(DATETIME,'20080107') – Aramaic

@Pavanred why would CONVERT work when CAST fails? There is bad data in the table. – Verse

@AaronBertrand I don't see your point about trying to convert 20130232, that's not even a date, so why would you want to? – Fiftieth

@Jack that's my point. There's bad data in the table (such as 20130232). You can't convert it to a date. I was demonstrating to PM 77-1 that using CONVERT(,112) won't fix that, either. – Verse

Answer 1

The error is happening because you (or whoever designed this table) have a bunch of dates in VARCHAR. Why are you (or whoever designed this table) storing dates as strings? Do you (or whoever designed this table) also store salary and prices and distances as strings?

To find the values that are causing issues (so you (or whoever designed this table) can fix them):

SELECT GRADUATION_DATE FROM mydb
  WHERE ISDATE(GRADUATION_DATE) = 0;

Bet you have at least one row. Fix those values, and then FIX THE TABLE. Or ask whoever designed the table to FIX THE TABLE. Really nicely.

ALTER TABLE mydb ALTER COLUMN GRADUATION_DATE DATE;

Now you don't have to worry about the formatting - you can always format as YYYYMMDD or YYYY-MM-DD on the client, or using CONVERT in SQL. When you have a valid date as a string literal, you can use:

SELECT CONVERT(CHAR(10), CONVERT(datetime, '20120101'), 120);

...but this is better done on the client (if at all).

There's a popular term - garbage in, garbage out. You're never going to be able to convert to a date (never mind convert to a string in a specific format) if your data type choice (or the data type choice of whoever designed the table) inherently allows garbage into your table. Please fix it. Or ask whoever designed the table (again, really nicely) to fix it.

Verse

You're correct, there were some missing values I didn't see in the middle of the table. Thanks for reminding me about the 'isdate()' function. – Slating

I see this all the time with mainframe data. Db2 for iSeries/AS400. The post is kind of a rant that assumes the user has control over dealing with dates as strings. Too much rant, not enough answer. – Calcifuge

@SQLDBA I must have missed where the OP - who two years ago accepted this answer - stated they were using mainframe data they couldn't change. If it were mainframe data they can't change, where's your answer about how to solve that? If the mainframe has stored 20130232, can you provide more answer, less rant? – Verse

The OP described the data he/she has to work with; you inferred from nothing that the OP had actively decided to store data in this format, and also needlessly asked if the OP "also" repeatedly made the same mistake elsewhere (assuming that OP is responsible for the first mistake). Downvote earned. But keep giving yourself credit because the OP accepted your answer (the only one available that year)! – Subterrane

Inferring "OP caused the problem" from "OP is asking how to deal with the problem" is wrong. Inferring "OP has permissions to alter the table" from "OP is querying the table" is wrong. Thinking that I had to "infer" anything in order to make my post is wrong. (I know you don't really think I was inferring anything - you were just trying to quote my own comment back at me, because that seems to be your habit.) – Subterrane

@Subterrane As a general thing, give people who are answering questions and helping people solve their problems - and have done so extensively on this site - the benefit of the doubt that when they say "you" they mean the Royal you, the people responsible for the problem, and not the "you stupid user" you. I think you'll get a lot more out of this site not assuming everyone who points out a problem with code, design, etc. is being a total jerk. If you start requiring everyone to elaborate ad nauseum about what they mean by "you" this place will clear out quick. – Verse

What is the 120 for in the convert function? – Portis

@Portis Style 120, which correlates to yyyy-mm-dd. For a full list see mssqltips.com/sqlservertip/4052/… ... it makes a lot more sense when you have a date that is at some point expressed as a literal, e.g. dbfiddle.uk/… – Verse

@ChristopherEberle I didn't design sysjobhistory but have been complaining about it my entire career. Most people who design a table incorrectly and allow garbage in because of a bad data type choice don't know they've made a mistake. If you're not willing to read constructive criticism about this without calling me derisive and pedantic, then I don't know what to tell you. If you want to see some tone that will make you leave, let me know, and I can point you to some other web sites... – Verse

Answer 2

Use SELECT CONVERT(date, '20140327')

In your case,

SELECT [FIRST_NAME],
       [MIDDLE_NAME],
       [LAST_NAME],
       CONVERT(date, [GRADUATION_DATE])     
FROM mydb

Magistrate

Answer 3

In your case it should be:

Select convert(datetime,convert(varchar(10),GRADUATION_DATE,120)) as
'GRADUATION_DATE' from mydb

Pompei

OP mentions the dates are varchars, so the inner conversion would be unnecessary for them, but in my case they are dates stored as numbers, so your solution is perfect for me. – Aladdin

Answer 4

I was also facing the same issue where I was receiving the Transaction_Date as YYYYMMDD in bigint format. So I converted it into Datetime format using below query and saved it in new column with datetime format. I hope this will help you as well.

SELECT
convert( Datetime, STUFF(STUFF(Transaction_Date, 5, 0, '-'), 8, 0, '-'), 120) As [Transaction_Date_New]
FROM mydb

Cuttle

Answer 5

Just to add more info about all solution above:

SELECT [FIRST_NAME],
       [MIDDLE_NAME],
       [LAST_NAME],
       CONVERT(date, [GRADUATION_DATE])     
FROM mydb

Assuming you don't have a WHERE clause, it is ok, the Convert will try to return all dates even if it is not a valid date like '00000000' (it was in my case).

But, if you need a WHERE clause, so you can see a message like this:

So I tested a mix of some approaches mentioned above like:

DECLARE @DateStart datetime = '2021-02-18'
DECLARE @DateEnd datetime = '2021-02-19'
SELECT [FIRST_NAME],
       [MIDDLE_NAME],
       [LAST_NAME],
       CONVERT(date, [GRADUATION_DATE]) 
FROM mydb
WHERE 
--THIS LINE SHOULD BE ENOUGTH TO AVOID WRONG DATES, BUT IT IS NOT
ISDATE([GRADUATION_DATE]) = 1 AND
CONVERT(char(10), [GRADUATION_DATE], 120) BETWEEN @DateStart and @DateEnd

And Finally I used this way with success:

DECLARE @DateStart datetime = '2021-02-18'
DECLARE @DateEnd datetime = '2021-02-19'
SELECT [FIRST_NAME],
       [MIDDLE_NAME],
       [LAST_NAME],
       CONVERT(date, [GRADUATION_DATE]) 
FROM mydb
WHERE 
CONVERT(char(10), 
-- I ADDED THIS LINE TO IGNORE WRONG DATES
CASE WHEN ISDATE([GRADUATION_DATE]) = 1 THEN [GRADUATION_DATE] ELSE '1900-01-01' END, 120) 
BETWEEN @DateStart and @DateEnd

Winterfeed