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)

Answer 2

For any date-time field in sybase, instead of going through the convert function, there is a more direct approach.

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

The date has to be in the form 'YYYY-MM-DD'

If you want to add a time, it can be included along with the date. The date and the time have to be separated by a T.

Any date time field can be directly used using the format 'YYYY-MM-DDTHH:MM:SS'

Using the functions is too lengthy. Noone needs a bazooka to shoot a squirrel! :)