Is this SQL statenent correct for dates ranges 2

[zishan619]

Hi:
I want to do a date range statement in my SQL query. I am using:
WHERE ESC.discov_date=Between date('9/18/03') AND date('9/19/03')
and it doesn't work. Does anyone know the correct statement Thanks
Z

 

[olded]

First, informix doesn't require the "date" internal function like other databases. If informix can't do a legal conversion it does it. Also, your syntax is off somewhat:

WHERE ESC.discov_date between "9/18/03" AND "9/19/03"

The above 2-digit year works only if shell variable DBDATE equals MDY2/

The default for DBDATE is MDY4/ and if it's set this way it's:

WHERE ESC.discov_date between "9/18/2003" AND "9/19/2003"

Regards,

Ed

 

[vpshriyan]

Hi,

The BETWEEN syntax of SQL would look like:

WHERE discov_date BETWEEN '9/18/03' AND '9/19/03';

One thing I noted from your SQL using table name "ESC" ! It is highly recommended NOT to use any of the reserved words as an user object name or an identifier.

Here are link for more information, ebooks in pdf format:
SQL Quick Syntax Guide:

http://www-3.ibm.com/software/data/informix/pubs/library/interim/7880a-pdf.html

SQL User Guide:

http://www-3.ibm.com/software/data/informix/pubs/library/interim/5470a-pdf.html

Regards,
Shriyan
"No amount of careful planning will ever replace dumb luck."

 

[zishan619]

Thank You Ed and Shriyan...It did work.