SQL Server CE date stored in string : how to compare

Question

I have a database in which, I see that dates are stored in the form of strings. Would it be possible for me to compare those date (in the form of string ) in a query?

For eg. column date1 stores 09-11-1992 00:00:00 and date2 stores 22-11-1992 00:00:00

Would it be sensible to execute a query as follows:

select * from tablename WHERE date1 > "06-11-1992 00:00:00";

Answer 1

If your strings are in a recognizable format, you should be able to cast them to datetimes and compare.

select *
from tablename
where cast(date1 as datetime) > cast('06-11-1992 00:00:00' as datetime)

However, I'd recommend ISO 8601 format for dates. Here's one possibility:

cast('1992-11-06T00:00:00' as datetime)

I'm assuming DD-MM-YYYY here because of your date2 example.

Just be sure you know which is the day and which is the month. Hopefully your strings are always the same format and are in-line with your regional settings.

Be careful: you may have to set the date format to get the right output from your data:

set dateformat dmy
select cast('06-11-1992 00:00:00' as datetime) -- returns 1992-11-06 00:00:00
set dateformat mdy
select cast('06-11-1992 00:00:00' as datetime) -- returns 1992-06-11 00:00:00