Sybase Date Comparison - Correct Format?

Question

I am new to Sybase and am writing a query to return results after a specified date, as well as before a specified date. MM / DD / YYYY format

Right now I'm doing ..

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

I get the records back, but since I'm new to Sybase, I want to make sure that Sybase correctly interprets these dates.

Their online doc is pretty bad for basic explanations on such things! Anyone who can confirm that I have work, or some formatting is required for dates?

Answer 1

You will need to convert the dates to DATETIME and tell sybase that the format must 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 selection commands:

 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

 CAST( '2000-10-31' AS DATE ) 

converts text to date format ....

I assume that your two fields (afterDate and beforeDate) are in Date format.

Your example:

 SELECT * From aTable WHERE afterDate >= CAST( '08/07/2013' AS DATE ) AND beforeDate <= CAST( '08/08/2013' AS DATE ) 

In addition, usually (but not always) the date range is in the SAME field. As I said, this is not always the case, and you may have a good reason.

Answer 3

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

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

The date must be in the form of 'YYYY-MM-DD'

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

Any date date field can be directly used using the format "YYYY-MM-DDH: MM: SS"

Using functions is too long. No one needs a bazooka to shoot a squirrel! :)

Answer 4

A better approach is to use the ANSI standard, which does not require any conversion: yyyymmdd (you can also include hh: mm: ss), for example:

DateField1> = "20150101" and DateFile1 <= "20150102"

Answer 5

You have to decide which input lines the user will use as a parameter, and then convert them and combine them the way you want, unless it is a Datetime, it doesn’t matter what source format it had, you can use it between -condition.

E. G. the user is from Europe and uses "DD.MM.YY" and "hh: mm" as an input parameter, I would convert the concatenation as follows:

  WHERE dateCol between convert(DATETIME, convert(char(11), convert(DATETIME, '01.06.14', 4), 16) || ' ' || '00:00', 8) AND convert(DATETIME, convert(char(11), convert(DATETIME, '01.07.14', 4), 16) || ' ' || '16:00', 8)