Re: filtering records between 2 dates

I agree that you don't need to use *ISO format.  But since I deal in a multi-national environment, by using ISO I don't have to remember that 02/01/2020 and 02.01.2020 are different dates (and which is which!).  Also, by using ISO date format, timestamps are just an extension of the date: '2020-02-01-13.30.00'.  It's just easier for me. :)


On 7/18/2020 3:25 AM, Birgitta Hauser wrote:

WHERE ORDDAT BETWEEN 01/01/1960 AND 31/12/2000

What data typ is ORDDAT? Is it a (real) date or a numeric date?
If it is a real date, you have to embed your Dates into single quotes
(otherwise it calculates 1/1/1960 !:
WHERE ORDDAT BETWEEN '01/01/1960' AND '31/12/2000'

But you need to use a valid date format for the character representation,
i.e.
ISO: YYYY-MM-DD
USA: MM/DD/YYYY
EUR: DD.MM.YYYY
There is no need to convert the character representation of the dates into
the *ISO format (YYYY-MM-DD).
SQL is smart enough to interpret the date representation, i.e. it looks for
the position of the 4 digit year and the separator (/= USA and . = EUR).

Under the cover a date is a numeric value (Scaliger number) and SQL does not
care about any date formats specified in DDS described files.
It just takes the Scaliger number and uses the date format of the current
job/connection for displaying it.