0

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.

  • 4
    Please edit and tag your question to tell us which database product you are using (e.g. MySQL and version). Also, since it is pretty much always better to store dates as dates rather than strings, is there some reason you cannot change the table definition now? Or are you also asking for help doing that? – Paul White Aug 7 '15 at 16:52
  • 1
    Are you able to change the date attribute to datetime? Storing dates as varchar is problematic. You will run into situations where data is stored that is not a valid date. You will also be continuously converting to and from datetime. – datagod Aug 7 '15 at 16:58
3

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

  • 4
    and it's usually recommended to use a standard fromat for dates, like 'YYYY-mm-dd' or even better 'YYYYmmdd' and not the 'mm/dd/YYYY' which is ambiguous and its interpretation by SQL-Server depends on local settings. – ypercubeᵀᴹ Aug 7 '15 at 18:24
  • 1
    @ypercube, Agreed, updated. – davidgarrison Aug 7 '15 at 18:25
0

There's a question of "Which database are you using?"

Each RDBMS has it's own way of handling the problem.

If it's MySQL use str_to_date:

https://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date

This will convert a varchar to a date, and makes it possible to sort the date correctly. Sorting a date as a varchar datatype will lead to some unexpected behavior.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.