How to compare dates in SQL

date

In a database table I have made a date attribute, but I have set its type to varchar and not Date. My question is: will I still be able to compare such dates in a SQL Query?

Dates in my DB are stored in this format:

dd/mm/yyyy hh:mm:ss

I have to do a SQL Query in PHP that looks something like this:

SELECT *
FROM DBtable
WHERE DBname='$name' AND date>='31/01/2015' AND date<='31/09/2015';

'date' looks like this: 07/08/2015 15:15:02

I would appreciate an example of how to do this.

Best Answer

Depending on what Database you are using, you should be able to cast the string to a date and then do your comparison.

i.e. in Sql Server

cast(date as datetime) >= '20150131' ...

That said, I'd typically recommend storing the date as a date if at all possible, and if you do need to use strings, It'd be best to use a more standardized format

i.e. 'YYYY-mm-dd' or even better 'YYYYmmdd' instead of 'mm/dd/YYYY' which, for example, can easily be mixed up with 'dd/mm/YYYY' which is common in many cultures. See this blog post: Bad habits to kick : mis-handling date / range queries

Related Question