Results 1 to 2 of 2

Thread: date format

  1. #1
    Join Date
    Nov 2003
    Hong kong

    Unanswered: date format

    Dear all,

    I suspect the format of date has problems. Could you help me?

    select * from table1 where sch_date > '11/23/2003'

    Does it correct in syntax?

    What it should be if the field of sch_Date in table1 is in date format in oracle database?

    Thanks for your big help

  2. #2
    Join Date
    Nov 2002
    Desk, slightly south of keyboard

    Oracle will convert your '11/23/2003' into a date, using what it thinks is a valid date format (see NLS for more info). On a UK NLS that might mean it is looking for dd/mm/yy, on a US system mm/dd/yy etc. How should it convert the century? Etc etc. It raises a lot of questions.

    You should always remove any implicit date conversion by Oracle by forcing your value into a date.

    Change you statement to....
    PHP Code:
    select from table1 where sch_date to_date('11/23/2003','mm/dd/yyyy'
    and it will be guaranteed to work regardless of the current NLS settings (that assumes of course that SCH_DATE is a date column!).

    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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