Re: Format Date Problem

On 21 Apr 2003 10:53:53 -0700, jen_designs_at_hotmail.com (Jennifer) wrote:

>"Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message news:<_8Toa.25568$Si4.13723_at_rwcrnsc51.ops.asp.att.net>...
>> Yu are mistaking how dates/times are stored with how they are displayed.
>> They are not stored in the format you indicate. Try using to_char to
>> display the date as a string. eg select to_char(sysdate,'mm/dd/yyyy') from
>> dual;
>>
>> Jim
>>
>Thanks Jim. That worked pefectly.
>
>Jennifer

Just as a follow-on to what Jim said, Oracle's 'date' type *always* stores both date and time. And it stores it in whatever internal format it stores it in. For all I know -- or care -- that could simply be the number of clock ticks since the The Flood. How it is presented to the client issueing the SELECT is subject to the NLS_DATE_PARM which can be set several different places -- most of which the app has no control over. Therefore, I recommend to our programmers that they never depend on that parm and always use the to_char function. Doing an alter session to set it at the beginning of your program serves the same purpose, but I prefer the explicitness of using the function.

Conversley, when inserting/updateing a date column, if you don't use the to_date function, you have to insure that you are supplying a 'date-time' character string in the format defined by the operative NLS_DATE_PARM. Again, defensive programming suggests that you always use the to_date function so that you have the control you need.

Others will disagree. As an unrepentant COBOL programmer, I always did everything I could to keep my code from requiring my telephone to ring at 2:00am. I try to take the same approach as a DBA. Received on Mon Apr 21 2003 - 14:49:54 CDT