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

> > "select sum(salesTotal) from ocf where employeeNo='0123' AND orderDate
like
> > '%$month $year %'" // i put % infront to ignore day, and % at the back
to
>
> How about
> ... like '%$month-$year%' ...
>
>
> > 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
>
> Here, you have the dash between month and year ................^
> You also have year and month reversed from above... Could be trouble!
>
it works when i put '%$month%', but this will only retrieve the month, but
any year, cause year din't specified, so i put the $year in like the 1st
query above, it's did not work, then i try all the possible, it dont work
also :(

$month=Feb, not 02

>> > "select sum(salesTotal) from ocf where employeeNo='0123' AND orderDate
> like
>> > '%$month $year %'" // i put % infront to ignore day, and % at the back
> to
>>
>> How about
>> ... like '%$month-$year%' ...
>>

>> > $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
>>
>> Here, you have the dash between month and year ................^
>> You also have year and month reversed from above... Could be trouble!
>>
> it works when i put '%$month%', but this will only retrieve the month, but
> any year, cause year din't specified, so i put the $year in like the 1st
> query above, it's did not work, then i try all the possible, it dont work
> also :(
>


LIKE matches strings, so
LIKE 'aaa%bbb'
would match
aaafdsglkjfdgldsjgbbb
aaabbb
aaa12345678bbb
aaaetcbbb

so if you want to match date strings which look like
...2001-01...
...2001-11...
...2002-03...
...2002-10...

you will need to use
LIKE '%2001-01%'
LIKE '%2001-11%'
LIKE '%2002-03%'
LIKE '%2002-10%'

How you get PHP to make strings like that is left as
an exercise!

HINT: You can crank up the PostgreSQL logging to show
      the queries which are actually being run.