Date Formatting, SQL Server

Question

I have a problem with my ultimate formula of:

ALTER TABLE tempTestProject 
ADD [T12 Backdated jnls]  varchar(50)

update tempTestProject
set [T12 Backdated jnls] = 'X' where cast([Created Date] as date) > cast([Effective Date] as date)

I get the error:

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

Now before I could get to the above I had to rearrange the date I had as I was having issues working with the dates before. I did the following to get my dates into the desired format: (Bare in mind my dates were varchar (50) and in the format 18/01/2014 intially)

ALTER TABLE tempTestProject 
ADD [Created Date]  varchar(50)

update tempTestProject
set [Created Date] = (RIGHT(["Created Date"],4) + '/' + SUBSTRING(["Created Date"],4,2) + '/'+ LEFT(["Created Date"],2))

ALTER TABLE tempTestProject 
ADD [Effective Date]  varchar(50)

update tempTestProject
set [Effective Date] = (RIGHT(["Effective Date"],4) + '/' + SUBSTRING(["Effective Date"],4,2) + '/'+ LEFT(["Effective Date"],2))

Hope I explained this enough, thanks for the help!

Did you see this question? http://stackoverflow.com/questions/207190/sql-server-string-to-date-conversion — Marcelo, May 12 '15 at 13:58
Don't store dates in strings it is absurd. From what you posted this wouldn't work because all the values in your new columns would be NULL. — Sean Lange, May 12 '15 at 14:00
1) Use one of the datetime datatypes whenever you are storing date, time, or date & time values in a database. — Philip Kelley, May 12 '15 at 14:02
2) Never create SQL objects (such as tables or columns) with names containing embedded spaces. — Philip Kelley, May 12 '15 at 14:02
Created Date Effective Date 2014/01/07 2014/01/07 2014/01/07 2014/01/07 — user4242750, May 12 '15 at 14:04
Oh ok, haven't had this issue before, where I am starting with varchar. Thanks for the help — user4242750, May 12 '15 at 14:05
Can you post the current format of your stored dates? Before you began to re-arrange them? — John Bell, May 12 '15 at 14:13
Ok so I did actually manage to get that "Results" column to have the 'X' indicator in this scenario. There were blanks in both columns. So although it isn't ideal, this is what got me to the answer of a journal "back posted": update tempTestProject set [T12 Backdated jnls] = 'X' where cast([Created Date] as date) > cast([Effective Date] as date) AND [Created Date] <> '//' AND [Effective Date] <> '//' — user4242750, May 12 '15 at 14:15
I mean edit your post with a sample of your original dates. I know they are strings. — John Bell, May 12 '15 at 14:19
if your "Created Date" and"Effective Date" are already in varchar format, try to build your query directly using varchar format. Use "cast(" in the left side of your Where clause can be very expensive. It's also dangerous since one abnormal value will break your query. In fact, if all your dates are formated as "yyyy/mm/dd" , "Where CreateDate>EffectiveDate" is just fine. — Tim3880, May 12 '15 at 14:29
@Tim3880, true, i over complicated it. Hopefully will not be starting from varchar next time — user4242750, May 12 '15 at 14:38

score 0 · Accepted Answer · answered May 12 '15 at 14:24

If you cannot implement all the of good advice in the comments, you can get around this using ISDATE to test if a string can be cast as a date. EDIT: Just noticed how you are storing your date. Before using is date, execute this line:

SET DATEFORMAT DMY;

select
case when ISDATE([CREATED DATE]) = 1
  then cast([CREATED DATE] as date)
else null
end as <Your date name here>

Date Formatting, SQL Server

1 Answers1