Using IsNull and Convert on DateTime field

[fionama]

Hi,
I have a varchar field (DischargeDate) that I am using in a where clause. I firstly want to convert this to a DateTime datatype and then use the IsNull function on it.
The syntax I'm using is: -

SELECT *
FROM EpisodeE
WHERE Convert(DateTime,(IsNull(EpisodeE.DischargeDate,'')),101)
Between '2009-01-01 00:00:00' AND '2009-01-31 23:59:59'

but it is giving me the error: -

Error: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. (State:22008, Native Code: F2)

I'd appreciate any suggestions. Thank you!

 

[RiverGuy]

You have one or more records with a string value which cannot be converted to a date. That's the issue with storing dates as varchar. It wreaks havoc on analysis later on down the road. So, either fix those dates, or filter them out using ISDATE()

 

[IanWaterman]

Is it because DateTime of '' fails

Try
SELECT *
FROM EpisodeE
WHERE Convert(DateTime,(IsNull(EpisodeE.DischargeDate,'2000-01-01 00:00:00')),101)
Between '2009-01-01 00:00:00' AND '2009-01-31 23:59:59'

Ian

 

[RiverGuy]

Is it because DateTime of '' fails

The following code returns 1900-01-01 for me

SELECT CONVERT(DATETIME, '')

 

[gmmastros]

I would suggest this:

SELECT *
FROM EpisodeE
WHERE Case When IsDate(EpisodeE.DischargeDate) = 1 
           Then Convert(DateTime, EpisodeE.DischargeDate)
           End
      Between '2009-01-01 00:00:00' AND '2009-01-31 23:59:59'

Invalid dates and NULL values for DischargeDate will NOT be returned. But, if the DischargeDate can be converted to date and it falls in the range, then it will be returned.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fionama]

I'm going with the last suggestion until we get get the datafield cleaned.
Thanks very much for your help.
Fiona