CODEWITHSUNDEEP
sqlsql-server
greg
greg

Reputation: 1873

SQl Server Converting to Date fails , DateTime works

I have a table with a varchar(25) column that holds a date value. A typical value is '11/04/2017'.

This query returns 0 rows 

select * 
from myTable  
where isdate(inputDate) = 0

I am trying to find a max on this, using a date sort.

This query returns the expected result

;with gooddates as 
(
    select 
        medcomfolder, PatientId, PatientBirthday, InputDate 
    from 
        myTable 
    where 
        isdate(inputDate) = 1
)
select max(convert(datetime, inputDate)) 
from gooddates

This query returns an error. 

;with gooddates as 
(
     select 
         medcomfolder, PatientId, PatientBirthday, InputDate 
     from 
         dwhFuData   
     where 
         isdate(inputdate) = 1
)
select max(convert(date, inputdate)) 
from gooddates

This is the returned error

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

The difference between the 2 queries is that the first is converting to a dateTime while the latter is converting to a date.

At this point, I can move forward w/ the dateTime option, but I am left wondering what I am missing.

I have checked that there are no embedded spaces, and all the columns have a len(InputDate) = 10 (there is NO time data included)

I selected distinct values,put them in excel, and did a date function on each row. I was hoping to get a #VALUE on 1 row. All the rows worked.

So there is nothing silly like '02/31/2019' going on.

How can a dateTime conversion pass when a simple date conversion does not?

Upvotes: 0

Views: 165

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

My guess is that you have values that include a time stamp following the date (based on the fact that isdate() is always zero).

If so, one simple solution would be to use convert(date, left(inputdate, 10)). Another solution uses try_convert():

try_convert(date, inputdate)

To find the offending values:

select inputdate
from dwhFuData
where try_convert(date, inputdate) is null and inputdate is not null;

Upvotes: 1

Related Questions