sql server - Comparing dates stored as varchar

Question

I need to compare dates that are stored in my database as varchar against today's date.

Specifically, I need to exclude any records with a date that has passed.

I tried:

SELECT * FROM tblServiceUsersSchedule 
WHERE ScheduleEndDate !='' AND ScheduleEndDate < '2015/05/31'

This selected values such as 17/06/2012 and 19/04/2015, which have both already passed, along with 01/06/2015 which hasn't.

I then tried to cast the data with:

SELECT * 
FROM   tblServiceUsersSchedule 
WHERE  CAST(ScheduleEndDate as DATETIME)  < CAST('05/31/2015' as DATETIME) AND ScheduleEndDate !='' AND ScheduleEndDate is not null

But got the following error:

The coversion of a varchar data type to a datetime data type resulted in an out-of-range value.

I checked the data behind and none are null, none are blank white space. All are dates in the format of dd/mm/yyyy.

I can't figure out how to compare the varchar date stored with todays date.

Answer 1

Storing date values as varchar is simply wrong.

If possible, you should alter the table to store them as date data type.
You can do it in a few simple steps:

1. Rename the current columns (I'm guessing ScheduleStartDate is also varchar) to columnName_old. This can be easily done by using sp_rename.

2. Use alter table to add the columns with the appropriate data type.

3. Copy the values from the old columns to the new columns using an update statement. Since all of the dates are stored in the same format, you can use convert like this: set ScheduleStartDate = convert(date, NULLIF(ltrim(rtrim(ScheduleStartDate_old)), ''), 103) If your sql server version is 2012 or higher, use try_convert. Note i've used the nullif, ltrim and rtrim to convert values that only contains white spaces to null.
4. Drop and recreate indexes that is referencing these columns. The simplest way to do this is by right-clicking the index on SSMS and choose script index as -> drop and create.
5. Use alter table to remove the old columns.

Note: if these columns are being referenced in any other objects on the database you will have to change these objects as well. This includes stored procedures, foreign keys etc`.

If you can't change the data types of the columns, and your sql server version is lower then 2012, you need to use convert like this:

SELECT * FROM tblServiceUsersSchedule 
WHERE CONVERT(DATE, NULLIF(ScheduleEndDate, RTRIM(LTRIM('')), 103) 
      < CAST(GETDATE() As Date);
AND ScheduleEndDate IS NOT NULL

Note that if you have even a single row where the column's data is not in dd/MM/yyyy format this will raise an error.

For sql server versions 2012 or higher, use Try_convert. This function will simply return null if the conversion fails:

SELECT * FROM tblServiceUsersSchedule 
WHERE TRY_CONVERT(DATE, NULLIF(ScheduleEndDate, RTRIM(LTRIM('')), 103)
      < CAST(GETDATE() As Date);
AND ScheduleEndDate IS NOT NULL

Note: I've used CAST(GETDATE() as Date) to remove the time part of the current date. This means that you will only get records where the ScheduleEndDate is at least one day old. If you want to also get the records where the ScheduleEndDate is today, use <= instead of <.

One final thing: Using functions on columns in the where clause will prevent Sql Server to use any indexing on these columns.
This is yet another reason why you should change your columns to the appropriate data type.