Re: How to update a Date Field?

When working with dates in SQL plus, it is easy to be fooled into believing that it doesn't have the time in it. However, in your case, the date you talk about most likely does have the time included in it. Its just that you cant see it. the time is 'hiding' behind the 'default format' for the date field. There are three things that can be done about that:

1. have the DBA change the default date format for the system so that times are always shown for dates (NOT RECOMMENDED)
2. change the default date format for your particular SQL plus session. I cant remember the specific syntax (as I am not at work right now) but I think it was an ALTER SESSION statement. (doing this may violate standards at your shop, if you have strict, and comprehensive standards)
3. use the TO_CHAR function to change the date from DATE format to VARCHAR2 format. (RECOMMENDED)

You see, the date isn't really stored as you see it on the screen when you select it. What you see on the screen is 'translated' by the database software before you see it. What is really stored in the date column is a number, which is the number of days (including fractional days) since some arbitrary point in time (Jan 1st 1960?). So, what you think is a date really is a number. That number gets re-formatted by the 'default' date format defined for your system. It looks as if your DBA (or systems administrator) has left the installation default in the system parameters. that default format is 'DD-MON-YY'. Since this default format doesn't have any time formatting in it, you will never see the time if you let the system use that default format. The good news is that you don't have to let the system use that default format. You can make the system use a different format (options 2 and 3 above). I personally use the second one I mentioned above, as I have it in the SQL script that automatically gets executed when I go into SQL Plus (which is why I cant remember the syntax, as I never execute it myself). However, I wont leave you high and dry. Here is how to do option #3:

column the_date format a20
select to_char(the_date, 'mm/dd/yyyy hh24:mi:ss') the_date from the_table;

Since dates are stored as a number of days (including a decimal portion which represents the hours, minutes, and seconds) from some arbitrary date, this also means you can do arithmetic on dates. For instance, one of the most useful things I have seen done is TRUNC(THE_DATE) = TRUNC(SYSDATE)-1 (for instance) to get all the records that were processed yesterday, regardless of what time of day they were processed and regardless of what time of day it is currently.

Sean Dolan wrote:

> Somehow in my database table, I have a date field that only has the format '02-NOV-98'. I would like to do an update statement that appends the date with 12:00 PM. I have tried and tried and cannot get the date to accept the hour of time. How do you do it?
>
> Thanks, Sean Dolan
>
> PS If you could, reply to the group or my personal mail: sean_at_3si.com
>
> Thanks Again!
Received on Thu Dec 10 1998 - 21:56:49 CST