CODEWITHSUNDEEP
sql-serversql-server-2008
Angelina
Angelina

Reputation: 2265

Filter out bad date values

I have a table in SQL Server that was given to me to take the data and convert expirationDate (varchar(4000)) to DATE.

This table was populated by an Excel sheet that's why it has different dates.

I need to somehow convert all of them into one format and cast as DATE with a SELECT statement.

Sample data in expirationDate:

2099-09-09
NULL
NA
8/25/12
12/21/11
na
2010-11-07
2013-10-07
NULL
3/25/12
tbd
NA
N/A
2009-04-17
2011-02-21
02/15/2011
08/31/11
NULL
2012-11-06
na
2011-12-21
2011-10-09
NA
2009-04-17
NA
2/29/12

I tried:

select CASE WHEN ISDATE(expirationDate) = 1 THEN CAST(expirationDate AS DATE) 
       ELSE cast(null as DATE) END
 FROM [dbo].[ComponentData]

But it is throwing error:

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

I also tried:

CONVERT(varchar(4000),CAST(expirationDate AS DATE),100)

But same thing... :(

Can anyone help me fix this?

Upvotes: 0

Views: 1353

Answers (1)

Angelina
Angelina

Reputation: 2265

i got it:

Testing:

select CAST(REPLACE(CONVERT(NVARCHAR, componentExpirationDate,103), '/', '-') as date)
 from [dbo].[BatchComponent]
 where ISDATE(componentExpirationDate) = 1

Output:

2099-09-09
2012-01-11
2010-09-27
2012-08-25
2011-12-21
2010-09-01
2016-02-02
2009-05-08
2010-11-07
2013-10-07

Change to CASE WHEN:

select CASE WHEN ISDATE(expirationDate) = 1 THEN CAST(REPLACE(CONVERT(NVARCHAR, expirationDate,103), '/', '-') as date) 
        ELSE cast(null as date) END
     FROM [dbo].[ComponentData]

Thank you all for your help.

Upvotes: 3

Related Questions