isDate in SELECT to validate / correct date

Question

Post reply

isDate in SELECT to validate / correct date

Jeffery Williams

SSCertifiable

Points: 6441
More actions
December 6, 2014 at 7:22 pm

#292689

I am importing data from a legacy system. The date column is a varchar and in the destination DB it is a proper DateTime.
That said I have invalid data and need to clean / accomodate for it as I go. What I need is if the date is valid then insert it, if not then ue a default of '01/01/1900' I am doing the following and it is not working.
CASE
WHEN isDATE(RatifiedDate) = 1 THEN RatifiedDate
WHEN isDate(RatifiedDate) = 0 THEN '01/01/1900'
END as RatDate
Any help would be appreciated.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

What is the full statement you are using for the INSERT?

Are you using SSIS to do this? It has other capabilities that could be beneficial.

Since you are on SQL 2012, check out TRY_CONVERT: http://msdn.microsoft.com/en-us/library/hh230993.aspx

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 2

No this will be a stored procedure

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

So what does the rest of the insert statement look like and what is the error that you're getting? If it's an actual error, please post the actual error message.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 4

Here is a code snip that should help you get passed this hurdle

😎

USE tempdb;

GO

SET NOCOUNT ON;

/* Never trust the defaults ;-) */

SET DATEFORMAT 'mdy';

DECLARE @TDATE TABLE (TDSTR VARCHAR(25) NOT NULL);

INSERT INTO @TDATE(TDSTR) VALUES

('01/02/2010'),('01/22/2010'),('12/10/2010'),('2010/12/31');

SELECT

T.TDSTR

,ISDATE(T.TDSTR) AS IS_DATE

,CASE

WHEN ISDATE(T.TDSTR) = 1 THEN CAST(T.TDSTR AS DATETIME)

ELSE CAST(0 AS DATETIME)

END AS DATE_OUT

FROM @TDATE T;

SET DATEFORMAT 'dmy';

SELECT

T.TDSTR

,ISDATE(T.TDSTR) AS IS_DATE

,CASE

WHEN ISDATE(T.TDSTR) = 1 THEN CAST(T.TDSTR AS DATETIME)

ELSE CAST(0 AS DATETIME)

END AS DATE_OUT

FROM @TDATE T;

SET DATEFORMAT 'ymd';

SELECT

T.TDSTR

,ISDATE(T.TDSTR) AS IS_DATE

,CASE

WHEN ISDATE(T.TDSTR) = 1 THEN CAST(T.TDSTR AS DATETIME)

ELSE CAST(0 AS DATETIME)

END AS DATE_OUT

FROM @TDATE T;

Results

TDSTR IS_DATE DATE_OUT

------------------------- ----------- -----------------------

01/02/2010 1 2010-01-02 00:00:00.000

01/22/2010 1 2010-01-22 00:00:00.000

12/10/2010 1 2010-12-10 00:00:00.000

2010/12/31 1 2010-12-31 00:00:00.000

TDSTR IS_DATE DATE_OUT

------------------------- ----------- -----------------------

01/02/2010 1 2010-02-01 00:00:00.000

01/22/2010 0 1900-01-01 00:00:00.000

12/10/2010 1 2010-10-12 00:00:00.000

2010/12/31 0 1900-01-01 00:00:00.000

TDSTR IS_DATE DATE_OUT

------------------------- ----------- -----------------------

01/02/2010 1 2010-01-02 00:00:00.000

01/22/2010 1 2010-01-22 00:00:00.000

12/10/2010 1 2010-12-10 00:00:00.000

2010/12/31 1 2010-12-31 00:00:00.000

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 5

Thank you, I'm all set

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 6

Actually the mistake was on my part and it was so completely stupid I would rather forget about it lol.

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh