Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2007
    Posts
    11

    Unanswered: Postgres SQL error. Plz help me.

    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...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in your TO_CHAR expressions, use YYYY-MM-DD format
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Posts
    11
    Quote 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...

  4. #4
    Join Date
    Jan 2007
    Posts
    11
    Im leaving.. now.....
    C u tomorrow morning.. bye...

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    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:

    Code:
    mtas_origitargtodate > to_date('$startDate', ,'MM/DD/YYYY HH24:MI')
    assuming that mtas_origitargtodate is of type DATE

  6. #6
    Join Date
    Jan 2007
    Posts
    11
    Quote 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:

    Code:
    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
    Code:
    mtas_origitargtodate > to_timestamp('$startDate','MM/DD/YYYY HH24:MI')
    Then my problem solved.......
    Thnx everyone who tried to help me.

  7. #7
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote 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 ...
    Last edited by loquin; 10-02-07 at 12:48.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  8. #8
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote 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

  9. #9
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    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?
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •