Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    40
    Rep Power
    11

    Screwy date output from Oracle table


    I am getting funky data on my timestamp output. Using SYSDATE on the insert statement with an oracle db:

    TO_CHAR(qmb.DATE_CREATED,'MM-DD-YYYY HH24:MM') date_created

    This gets me a time of 14:03, or sometimes 04:03, regardless of the actual time. Do I need some kind of format definition in the insert statement?

    Anyone have any ideas on this?
    Last edited by tech-writer; March 15th, 2004 at 01:48 PM. Reason: supplying more data
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,275
    Rep Power
    968
    Are you saying the time that is actually being stored in the database is wrong, or it is wrong when you pull it out and use to_char() on it?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    40
    Rep Power
    11
    Originally Posted by kiteless
    Are you saying the time that is actually being stored in the database is wrong, or it is wrong when you pull it out and use to_char() on it?
    It seems to be storing the hours correctly. But the minutes always come out as "03" In viewing the db in TOAD, the time values is shown as a "0" ir a "1"
  6. #4
  7. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,275
    Rep Power
    968
    So the problem is really in how you are inserting the date/time value into the table...CF is just reading back what is already stored.

    How are you doing the insert of the date/time field?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    40
    Rep Power
    11
    Originally Posted by kiteless
    So the problem is really in how you are inserting the date/time value into the table...CF is just reading back what is already stored.

    How are you doing the insert of the date/time field?
    insert into QMB values ('','','SYSDATE','')
  10. #6
  11. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,275
    Rep Power
    968
    Shouldn't it be:

    insert into QMB values ('','',SYSDATE)

    which would insert a comma into the first column and the sysdate into the second column.

    Can you confirm that the column that sysdate is going into is a date/time field?
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    40
    Rep Power
    11
    Originally Posted by kiteless
    Shouldn't it be:

    insert into QMB values ('','',SYSDATE)

    which would insert a comma into the first column and the sysdate into the second column.

    Can you confirm that the column that sysdate is going into is a date/time field?

    Those were single quotes. The date is going into the correct field. The exact syntax being used is:

    insert into qmb_record values('#RECORD_ID#'+1,'#user_id#','n',SYSDATE,'','#other_fields#')
    Last edited by tech-writer; March 16th, 2004 at 11:39 AM.
  14. #8
  15. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,275
    Rep Power
    968
    Only thing I can think of is, can you confirm that the data type of the field that is holding the date is of data type "date"?

    If you run this, do you get a date with the proper time component?

    select sysdate from dual
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    40
    Rep Power
    11
    The datatype for the Oracle field is "timestamp"

    TOAD doesn't seem to know how to handle the query you said to try.
  18. #10
  19. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,275
    Rep Power
    968
    Timestamp is a data type specifically for storing extremely precise times, like down to a millionth of a second. This might be part of the problem, if you only want time to the second, a date field should be fine.

    select sysdate from dual

    simply selects the system time from the system's virtual table "dual". This should not be a problem...my copy of TOAD runs this query fine. If that doesn't work there may be other issues with your Oracle or TOAD configuration.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    40
    Rep Power
    11
    Seemed the problem was in the output statement, I got that from somewhere here, but MM should have been MI for minutes.

    But now I have another complexity for you on this:

    How can I apply this formatting:

    TO_CHAR(qmb.DATE_CREATED,'MM/DD/YYYY HH24:MI') date_created

    To these parameters:

    select max(DATE_CREATED) date_created from qmb


    I need to maintain consistent format, but on one page I need to display the most recent timestamp for various groups of associated records.
  22. #12
  23. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,275
    Rep Power
    968
    select TO_CHAR( max( DATE_CREATED ) ,'MM/DD/YYYY HH24:MI' ) as date_created
    from qmb
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    40
    Rep Power
    11
    USing that I get the following error:

    ORA-00937: not a single-group group function
  26. #14
  27. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,275
    Rep Power
    968
    Do you have other fields in the query? I think you must, because this works fine on my local instance:

    select TO_CHAR( max(CREATE_DATE),'MM/DD/YYYY HH24:MI') date_created from newsContent


    If so, you'll need a GROUP BY clause.
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    40
    Rep Power
    11
    yes, as I say I am gathering data from 5 different tables. The existing query (which is quite long) doesn't generate errors. When I added that statement it would no longer run.

    The statement runs fine by itself, but if that is just one of the datafields I need to query at once among several across multiple tables, it gets belligerant with me.
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo