Re: is this my date problem

On Wed, Oct 01, 2003 at 07:57:18PM -0700, Theodore Petrosky wrote:
> here is the actual query:
>
> agencysacks=# SELECT jobnumseq, (SELECT cname FROM
> clientinfo ci WHERE ci.acode = j.clientid) as client,
> shrtdesc, to_char(proofduedate, 'Dy FMMon DD, YYYY
> HH12 am') FROM jobs j WHERE proofduedate BETWEEN
> to_timestamp('01 October 2003 00:01', 'DD Month YYYY
> HH24:MI') AND to_timestamp ('01 October 2003 23:59',
> 'DD Month YYYY HH24:MI') ORDER BY client,
>...
> I am trying to create a 'today' type query. between
> october 1, 2003 00:01 am and october 1, 2003 23:59

If you want a "today" type query, why are you using between? This
should work, and be a whole lot more reliable:

SELECT jobnumseq, (SELECT cname
FROM clientinfo ci
WHERE ci.acode = j.clientid) as client,
shrtdesc,
to_char(proofduedate, 'Dy FMMon DD, YYYY HH12 am')
FROM jobs j
WHERE date(proofduedate) = '2003-10-01')
ORDER BY client,

or if you want to derive "today" automatically this should work:

SELECT jobnumseq, (SELECT cname
FROM clientinfo ci
WHERE ci.acode = j.clientid) as client,
shrtdesc,
to_char(proofduedate, 'Dy FMMon DD, YYYY HH12 am')
FROM jobs j
WHERE date(proofduedate) = date(now()))
ORDER BY client,