Thread: Date Problem

I'm using pgsql with php, and my query below will retrieve employee's
monthly sales total,

"select sum(salesTotal) from ocf where employeeNo='0123' AND orderDate like
'%$month $year %'" // i put % infront to ignore day, and % at the back to
ignore the time (because i'm using timestamp with timezone), then i shall
get particular month's records.

$month = Feb
$year    = 2002

But i cant retrieve the data,  if i take out the $year, it works...but i
need the year to identify the records.

it works when i do a direct sql query to the pgsql through pg-admin, the
query are like this,
select grand_total,ocf_no,date from ocf where date like '2002-01%' order by
date;

i really confused with the date format......

Anybody got any solution or better idea, pls reply! THANK YOU

Regards,

pauL

Paul,

> I'm using pgsql with php, and my query below will retrieve employee's
> monthly sales total,
> 
> "select sum(salesTotal) from ocf where employeeNo='0123' AND
>  orderDate like
> '%$month $year %'" // i put % infront to ignore day, and % at the
>  back to
> ignore the time (because i'm using timestamp with timezone), then i
>  shall
> get particular month's records.

The main problem is that you are treating the TIMESTAMP field like itis text.  It is not.  You cannot use comparitors
designedfor textparsing (such as "LIKE '%xxxyyy%') on TIMESTAMPs and get reliableresults.
 

Instead, use functions designed for TIMESTAMPs:
http://www.postgresql.org/idocs/index.php?functions-datetime.html

For example, you could:
SELECT ... WHERE EXTRACT(month FROM orderdate) = $month ANDEXTRACT(year FROM orderdate) = $year;
... but there are a few ways you can answer this question.  Read thedocumentation page; better yet, buy and read a
PostgreSQLbook.
 

-Josh Berkus

P.S. In the future, questions of this type are more appropriate for theNOVICE list.  The SQL list is more for
the"how-do-I-get-this-insanely-complex-query-to-perform"type.
 




______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco