Re: Regarding date formats


"Frank van Bortel" <frank.van.bortel@xxxxxxxxx> a écrit dans le message de news: dp365a$km1$1@xxxxxxxxxxxxxxxxxxxxxxxxxx
| Giridhar wrote:
| > Hi,
| > Please review the following two pl/sql blocks.
| > Our NLS_DATE_FORMAT is defined as DD-MON-RR.
| > ------------------------------------------------------------
| > SQL> declare
| > 2 v date;
| > 3 begin
| > 4 select decode(0,-1,NULL,(to_date('05251932', 'MMDDYYYY'))) into v
| > from
| > dual;
| > 5 DBMS_OUTPUT.PUT_LINE(to_char(v,'DD-MON-YYYY'));
| > 6 end;
| > 7 /
| > -----------
| > 25-MAY-2032
| > -----------
| > PL/SQL procedure successfully completed.
| >
| > SQL> declare
| > 2 v date;
| > 3 begin
| > 4 select to_date('05251932', 'MMDDYYYY') into v from dual;
| > 5 DBMS_OUTPUT.PUT_LINE(to_char(v,'DD-MON-YYYY'));
| > 6 end;
| > 7 /
| > -----------
| > 25-MAY-1932
| > -----------
| > PL/SQL procedure successfully completed.
| > ------------------------------------------------------------
| >
| > May i know why the first one is displaying value as 25-MAY-2032 instead
| > of
| > 25-MAY-1932
| >
| > Thanks
| > Giridhar
| >
|
| Because you have a decode, and that does (datatype) conversions.
| "Oracle7 automatically converts the return value to the same
| datatype as the first result."
| Your first result is -1, the outcome of the decode would therefore
| become numeric. As your assignment datatype is date, a conversion
| will take place, and that will go through a varchar2. As this is
| an implicit conversion, the default date format mask is applied,
| "DD-MON-RR".
|
| As case statement would prevent this, but isn't available in Oracle7.
| --
| Regards,
| Frank van Bortel
|
| Top-posting is one way to shut me up...

The first result is NULL (not -1) which is of VARCHAR2 datatype.
This does not change the rest of your post: date->varchar2->date conversion
and the application of implicit date format.

Regards
Michel Cadot



.

Relevant Pages

  • Re: Regarding date formats
    ... Giridhar wrote: ... > SQL> declare ... As your assignment datatype is date, a conversion ...
    (comp.databases.oracle.server)
  • Re: Profiler Bug viewing trace flat file?
    ... Kalen Delaney, SQL Server MVP ... Run this code to create a trace capture of the RPC:Complete event: ... declare @error INT ... Now open the trace file in Profiler and you will see that the Textdata ...
    (microsoft.public.sqlserver.tools)
  • SQL SERVER 2000 domain changes for users.
    ... In the SQL server, we have ... how to change users login name in sql 2000 SP4 after domain change... ... DECLARE @charvalue varchar ... -- temporary srvrole access table ...
    (microsoft.public.sqlserver.server)
  • Re: Sync production db with test
    ... Declare @strTableSchema Nvarchar ... DECLARE @SQL varchar ... CREATE TABLE #SQLtemp ...
    (microsoft.public.sqlserver.programming)
  • Re: Performance Issues With Query
    ... SQL Server MVP ... > DECLARE @Timestamp datetime ... > DECLARE @CallEventName varchar ... > UPDATE CallByCallYesterdayFinal SET Script = SUBSTRING(@Destination, ...
    (microsoft.public.sqlserver)