i've just started using DB2 and have not found IBM's help information all that helpful regarding my query. what i'd like to do is select a set of records based off a given beginDate and endDate range that is in MM/DD/YYYY format. i don't have any interest in changing the inputs to match the DB2 date format (it's storing the values in a timestamp value = '2004-04-28 15:14:59.323000') i'd like to know how to effectively use the DATE operator as i've seen some information indicating that it can be used for such things. unfortunately, there's no indication of the syntax that's required and the errors i'm getting from db2 aren't very helpful.

my inputs are:
USERID = 921265660866393984317036000000
STARTDT (beginDate) = 04/28/2004
STARTDT (endDate) = 04/29/2004

with Oracle, the following works:
select IDENTIFIER from BCHISTSESSN where USERID =
AND (STARTDT >= TO_DATE(? 'MM/DD/YYYY')
AND STARTDT <= TO_DATE(?, 'MM/DD/YYYY'));

with SQL Server, the following works:
select IDENTIFIER from BCHISTSESSN
where USERID =
AND (STARTDT >= ? AND STARTDT <= ?)

here's a DB2 query that works. i'm interesting in using just a MM/DD/YYYY or even a YYYY-MM-DD format:

select IDENTIFIER
from BCHISTSESSN
where USERID = 921265660866393984317036000000
and STARTDT >= '2004-04-28 15:14:59.323000'
and STARTDT <= '2004-04-29 15:14:59.323000'

any help here is greatly appreciated.

regards,
jason

Hey cheer up!

DB2 is a wonderful tool - you just have to
get to know it a little :-)

The DB2 function date(<myvariable>) extracts the date
from a number of supported date formats, among those the
US standard (which sucks), and the ISO standard (ISO Rules!!),
as you have specified.

Thus, your predicate should work using >'yyyy-mm-dd' or >'mm/dd/yyyy'

If you have the date variable as a DB2 date, simple use: > '<yourdatevariable>'

It is not necessary to use a fulle timestamp, DB2 Supports
an ordinary date variable as used in the above example.

There is plenty of documentation on DB2 and datetime variables, see
the ftopmost 2 posts in this forum.

BOW

This is a small write up about date format in CLP ...

You can apply this theory for any of your applications

i found something about the CAST operator and now this query returns just what i need (i can still use the common 'MM/DD/YYYY' format):

select IDENTIFIER from BCHISTSESSN
where USERID = ?
and (DATE(STARTDT) >= CAST(? AS DATE)
and DATE(STARTDT) <= CAST(? AS DATE))

thanks for the heads up about the locale settings. i see internationalization coming our way so this is good information to know.

regards,
jason