Results 1 to 6 of 6

Thread: Date Convertion

  1. #1
    Join Date
    Sep 2007
    Posts
    56

    Unanswered: Date Convertion

    My table stores date in this format 2011-02-28 00:00:00.0, how do i convert or compare this date with sysdate in oracle

    i tried
    select trunc(to_char(let.custom0,'yyyy-mm-dd'))
    select to_date(let.custom0,'dd-mm-yyyy')

    but am not able to convert it into date,

    can anybody help me out

    Thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by donraja_ht View Post
    My table stores date in this format 2011-02-28 00:00:00.0
    What datatype is that column?

  3. #3
    Join Date
    Sep 2007
    Posts
    56
    it is VARCHAR2(1020)

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Well this is your first big (actually huge) mistake: do not store dates in a character column. Never. Ever.
    One of the reasons why, you just found out by yourself.

    However - as I bet you'll come up with "this was not designed by me and I cannot change it" - this should do it:

    Code:
    select to_date(let.custom0,'yyyy-mm-dd hh24:mi:ss.ff')
    For details on the format models available for the to_date() function check out the manual:
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924

    For details on the to_date() function check out the manual.
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions183.htm#i1003589

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Unfortunately, DATE data type does not contain fractions of second. Anyway, if you would like only to compare it to sysdate, then, as this VARCHAR2 representation has the same order as the corresponding DATE one (supposing, time components are hours:minutes:seconds), so I would compare strings like this:
    Code:
    to_char( sysdate, 'yyyy-mm-dd hh24:mi:ss' ) < substr( let.custom0, 1, 19)
    At least, this will not fail after you will store VARCHAR2 representation of non-existing dates (e.g. '2011-02-29 00:00:00.0').

    [Edit: grammar of the last sentence]

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by flyboy View Post
    Unfortunately, DATE data type does not contain fractions of second.
    Good point. I didn't think about that.

    In this case to_timestamp() can be used instead of to_date().

    The result of to_timestamp() can be compared with sysdate without problems. If the fractional settings are indeed important, than it should be compared to current_timestamp (instead of sysdate)

Posting Permissions

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