[eranda]

I have write this sql with php.....

PHP Code:

$endDate = "09/21/2007 10:30";
$startDate = "09/21/2007 08:30";
    
$query = "select mtas_jbnumber,to_char(mtas_origitargtodate,'MM/DD/YYYY'),to_char(mtas_origitargfrmdate,'MM/DD/YYYY') from im.mta where to_char(mtas_origitargfrmdate,'MM/DD/YYYY HH24:MI') < '$endDate'
and  to_char(mtas_origitargtodate,'MM/DD/YYYY HH24:MI') > '$startDate' and mtas_assignee = '$txtMCode' and mtas_perform <> 'D'"; 


But in the table "mta" exists only dates in 2006.
mtas_origitargtodate and mtas_origitargfrmdate are timestamps

But when i run the sql, it retrieves dates in 2006. how can it hapens? Even i change above start and end date 's year to 2008, it retrieves 2006 dates. But month and the date works fine. It's like that year is not considered. Plz can anyone help me?
Thnx...

[r937]

in your TO_CHAR expressions, use YYYY-MM-DD format

[eranda]

Originally Posted by r937

in your TO_CHAR expressions, use YYYY-MM-DD format

No that cant do... because i have used "MM/DD/YYYY" format everywhere .....

This is a strange thing.... I cant understand wht is happening there...

[eranda]

Im leaving.. now.....
C u tomorrow morning.. bye...

[shammat]

to_char is an Oracle function, not a Postgres function.

And please: post SQL code, not PHP code.
With that I mean a statement that can be run with e.g. SQL*Plus (or psql, if you are indeed using Postgres).
First get your statement working with pure SQL then put it into a PHP script.

I know nothing of PHP, so this answer might be wrong, but I would assume you need something like:

mtas_origitargtodate > to_date('$startDate', ,'MM/DD/YYYY HH24:MI')

assuming that mtas_origitargtodate is of type DATE

[eranda]

Originally Posted by shammat

to_char is an Oracle function, not a Postgres function.

And please: post SQL code, not PHP code.
With that I mean a statement that can be run with e.g. SQL*Plus (or psql, if you are indeed using Postgres).
First get your statement working with pure SQL then put it into a PHP script.

I know nothing of PHP, so this answer might be wrong, but I would assume you need something like:

mtas_origitargtodate > to_date('$startDate', ,'MM/DD/YYYY HH24:MI')

assuming that mtas_origitargtodate is of type DATE

Hey.....
thnx for helping me....
Actually type of mtas_origitargtodate is Timestamp.
So i changed my sql to

mtas_origitargtodate > to_timestamp('$startDate','MM/DD/YYYY HH24:MI')

Then my problem solved.......
Thnx everyone who tried to help me.

[loquin]

Originally Posted by shammat

to_char is an Oracle function, not a Postgres function.

While the CAST function is often used (Ref 4.2.8), to_char, to_number, to_date, and to_timestamp are all valid PostgreSQL function ...

[shammat]

Originally Posted by loquin

While the CAST function is often used (Ref 4.2.8), to_char, to_number, to_date, and to_timestamp are all valid PostgreSQL function ...

Ah! Didn't know that
Thanks for the hint

[loquin]

I've never tried looking it up, but I wonder of they're all just wrappers for the CAST function to coddle the Oracle users?