Sybase date comparison - Correct format?

Question

I'm pretty new to Sybase and am writing a query to return results after a specified date, and also before a specified date. MM/DD/YYYY format

At the moment im doing..

SELECT * 
    From aTable
      WHERE afterDate >= 08/07/2013
        AND beforeDate <= 08/08/2013

I'm getting records back, but as I'm a Sybase newbie, I want to be sure Sybase is interpreting these dates correctly..

Their online doc is pretty bad for basic explanations on things like this! Anyone able to confirm if what I have works, or does it need some formatting round the dates?

Answer 1

You'll need to convert the dates into DATETIME and tell sybase what the format is to be sure.
According to this documentation the code for MM/DD/YYYY is 101, so something like this:

SELECT * 
FROM aTable
WHERE afterDate >= CONVERT(DATETIME,'08/07/2013',101)
      AND beforeDate <= CONVERT(DATETIME,'08/08/2013',101)

You can see the difference by running the following select statements:

SELECT CONVERT(DATETIME,'08/07/2013',101)  --MM/DD/YYYY (2013-08-07 00:00:00.000)
SELECT CONVERT(DATETIME,'08/07/2013',103)  --DD/MM/YYYY (2013-07-08 00:00:00.000)