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?

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"

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','')

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#')

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

The datatype for the Oracle field is "timestamp"

TOAD doesn't seem to know how to handle the query you said to try.

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.

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.

select TO_CHAR( max( DATE_CREATED ) ,'MM/DD/YYYY HH24:MI' ) as date_created
from qmb

USing that I get the following error:

ORA-00937: not a single-group group function

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.

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.