Date and Time Format

[anasazii]

I have an application where I would like to store audit information such as Creation Date and Last Updated Date. Both are Date fields.
My application stores the SYSDATE value, but it is only storing the date part(03/28/2004) - no time (HH:MM:SS PM). Is there anyway to get the time included?

I don't know if I'm on the right track, but I've tried

to_char(sysdate, 'MM/DD/YYYY HH:MI:SS PM')
-- Obviously, this errors when trying to store back into a Date field...When I try adding a to_date around it I get 'Not a valid Month'

It seems like there should be an easier way?
Thanks,
Janel

 

[anasazii]

I think I got it to work...thanks.

select to_date(to_char(sysdate, 'MM/DD/YYYY HH:MI:SS PM'), 'MM/DD/YYYY HH:MI:SS PM') from dual;

 

[SantaMufasa]

Anasazii,

Although you "see" the results you want, you do not need to go to that extent to "store" date and time. Oracle DATE expressions always contain date and time. Your default display mask, however, probably shows just the date.

Every Oracle date contains at least the following components:

Sign: negative = B.C.; positive = A.D.
1 byte for 2-digit century
1 byte for 2-digit year
1 byte for 2-digit month
1 byte for 2-digit day
1 byte for 2-digit 24-hour hour
1 byte for 2-digit minute
1 byte for 2-digit second

...and if you use timestamp data type, it includes timezone indicator and milliseconds.

It is your to_char function mask that determines how your DATE/TIME expression appears, but DATEs in Oracle contain date and time no matter what.


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.