CONVERT on only NOT NULL records

Question

I am working in SQL Server 2008. I have a table with two columns that store date values, but the columns themselves are varchar. (I know, this is bad practice, but I don't own the table.) Some of the records in these two columns are NULL. I am trying to do a comparison of the two columns against each other. So, to enable the comparison, I need to do a CONVERT. But, CONVERT fails when it encounters NULLs. How do I essentially prevent the CONVERT from happening when it encounters a NULL? My core query is as follows:

SELECT
col1
FROM table_a
WHERE
CONVERT(date, col2, 101) > CONVERT(date, col3, 101)

I tried an inner query with an IN clause (i.e., the inner query returns only non-NULL records), but this failed because it seems that the query optimizer runs both queries independently, i.e., it runs the CONVERT on all records, which causes the failure. I also tried a self join (i.e., return only records in the first instance of the table where the records aren't null in the second instance of the table). But, this failed as well because of the same problem in the inner query scenario.

`where case when col2 is not null then CONVERT(date, col2, 101) end > case when col3 is not null then CONVERT(date, col3, 101) end`? — GSerg, Oct 29 '14 at 17:20
This isn't working. SQL throws the following error: Conversion failed when converting date and/or time from character string. I have verified (via ISDATE) that, for the records that are populated, the values are indeed dates. — skyline01, Oct 29 '14 at 17:51
possible duplicate of [SQL Server 2008: Error converting data type nvarchar to float](http://stackoverflow.com/questions/9136722/sql-server-2008-error-converting-data-type-nvarchar-to-float) — GSerg, Oct 29 '14 at 18:12
Re *Conversion failed*: [`ISDATE` is not perfect](http://stackoverflow.com/q/11745650/11683). — GSerg, Oct 29 '14 at 18:16
I figured out my issue. The problem was in my date values. For col2 and col3, I had to used SUBSTRING to construct them in the form YYYY-MM-DD. But, date style 101 is MM/DD/YYYY. So, I simply changed my SUBSTRING to produce the correct format. — skyline01, Oct 30 '14 at 18:02

SlimsGhost · Accepted Answer · 2014-10-30T13:50:28.700

4

Try this:

SELECT
col1
FROM table_a
WHERE 
    case 
    when col2 is null or col3 is null then 0
    when CONVERT(date, col2, 101) > CONVERT(date, col3, 101) then 1
    end = 1

This method prevents the null strings from being converted (or attempted to be) and lets you decide what else you might want to do if one or the other cols is null.

EDITS: first version contained syntax errors in the case statement - these should be fixed now. Apologies for the hasty typing and not SQLFiddling it.

edited Oct 30 '14 at 13:50

answered Oct 29 '14 at 17:23

SlimsGhost

2,849
1
10
16

This isn't working for me. SQL keeps throwing an error stating: Incorrect syntax near '>'. – skyline01 Oct 29 '14 at 17:38
@user3100444 I had initially left off the second "then" in the case statement. Since edited, please try it now. Sorry for that! – SlimsGhost Oct 29 '14 at 18:18
I have tried the update, but it still isn't working (for the same reason). – skyline01 Oct 29 '14 at 18:22
I don't think ELSE can handle a Boolean expression after it. Shouldn't it be WHEN? – skyline01 Oct 29 '14 at 19:47

score 0 · Answer 2 · edited Apr 04 '18 at 10:00

0

Try

SELECT * FROM #Tmp
WHERE 
col2 IS NULL OR col3 IS NULL OR 
CONVERT(DATETIME, col2, 101) > CONVERT(DATETIME, col3, 101)

I have tried below query in SQL 2005 its working fine without any error, but it returns all the records without null. Above query return null record too

SELECT * FROM #Tmp WHERE CONVERT(DATETIME, col2, 101) > CONVERT(DATETIME, col3, 101)

edited Apr 04 '18 at 10:00

Roshna Omer

687
1
11
20

answered Oct 29 '14 at 17:52

Sanjay Goswami

1,386
6
13

score 0 · Answer 3 · answered Oct 30 '14 at 16:44

Try this: (Sample Data Included)

DECLARE @TABLE TABLE
 (
    SomeText VARCHAR(50),
    Date01 VARCHAR(50),
    Date02 VARCHAR(50)
)

INSERT INTO @TABLE VALUES('Good Date 01','10/01/2014','12/30/2014')
INSERT INTO @TABLE VALUES('Early Date 01','10/01/2014','12/30/2013')
INSERT INTO @TABLE VALUES('Good Date 02','10/01/2013','10/01/2014')
INSERT INTO @TABLE VALUES('Bad Data 01',NULL,'12/30/2014')
INSERT INTO @TABLE VALUES('Bad Data 02','10/01/2014',NULL)
INSERT INTO @TABLE VALUES('Bad Data 03',NULL,NULL)

SELECT  
    SomeText,
    DATEDIFF(D,CONVERT (DATE, Date01, 101) , CONVERT (DATE, Date02, 101))  AS DELTA
FROM 
    @TABLE 
WHERE
    1 = 1
    AND ISDATE(Date01) = 1 
    AND ISDATE(Date02) = 1 
    and DATEDIFF(D,CONVERT (DATE, Date01, 101) , CONVERT (DATE, Date02, 101)) < 0

The ISDATE(Date01) and ISDATE(Date02) remove missing data points from being attempted by the convert function.

Because how the optimizer works, my example solves all data problems except invalid data conversion such as April 31st. If an additional row was added with this error it fails. Working on how to fix this issue short of returning the results to a secondary temp table which fundamentally bothers me. — Stephen Bodine, Oct 30 '14 at 16:48

CONVERT on only NOT NULL records

3 Answers3