thanx alot for the quick responce!!
aman, October 12, 2003 - 8:43 am UTC
sir!!
thanx alotfor the quick responce!!you are amazing!!
sir,its true it will show 2017 with both the RR and YY format if the date is entered in the 2003 year.sir i am confused about the point that if someone had entered the date in the year 1940 (20 centuary) and now someone else is trying to look at it in 2003(21 centuary) then what will be shown to him?as last digits of both the current year (2003 21st centaury) and specified year(1940 20th centaury) lie in the range of 0-49 ,according to the RR concepts this would show year 2040 as it says that "the return date will be current centaury means 21st centaury" and if uwe use it will be 1940 ..correct me if i am wrong.i tried to do to the practical of it by changing the system date from the Control Panel->Regional Options ->date =1939 but then database stopped working giving the error that the system date is in correct , i changed thedate from the Task Bar to 2099 , i was not able to connect with any of the Sys or System login and i was getting the error that hosdef extension not found ....so i was unable to check my doubt practically ...please explain ..
thanx alot sir
October 12, 2003 - 9:32 am UTC
you are confusing DATES with FORMATS.
A date is a 7 byte binary piece of data always -- it stores
YYYY
MM
DD
HH24
MI
SS
So, if in 1940 some inserted "to_date( '01-jan-17', 'dd-mon-yy' )" that would be 1917.
In the year 2040 -- that will STILL be 1917
It will be 1917 FOREVER
It is like the Number "5". you insert 5, you get 5 out forever.
If you insert 1917, it'll be 1917 forever.
Now, in the year 2040, if you:
select to_char( dt, 'dd-mon-yy' )
you'll get back 01-jan-17 which is basically a MEANINGLESS character string and points out why the only "good" thing is to use YYYY on INPUT and OUTPUT to avoid ambiguities.
downside of using 'YY'
A reader, October 12, 2003 - 12:55 pm UTC
Tom
Can you show an example where using 'YY' format instead of rr or rrrr , proves fatal.
If I insert using yy format, and query using a to_char of yyyy, then I will still be o.k....
SQL> insert into t select to_date('01-01-17','dd-mm-yy') from dual
2 ;
1 row created.
SQL> commit;
Commit complete.
SQL> select x from t;
X
---------
01-JAN-17
SQL> select to_char(x,'mm/dd/yy') from t;
TO_CHAR(
--------
01/01/17
SQL> select to_char(x,'mm/dd/yyyy') from t;
TO_CHAR(X,
----------
01/01/2017
What is the issue here.
October 12, 2003 - 1:17 pm UTC
here:
03
What year is that please? No matter what you say -- you are wrong.
Now:
2003
what year is that please? ahh -- it takes 4 characters to show a year -- i get it!
SQL> select to_char(x,'mm/dd/yy') from t;
TO_CHAR(
--------
01/01/17
is that 1917, 2017, 1817 - what??????? you don't know -- that is the issue.
Worse, end user gives you "40".
Is that 1940, 2040 -- what????? no clue, no idea. not safe.
something we all should have learned 3 years ago big time?
excellent
aman, October 13, 2003 - 8:06 am UTC
thanx alot sir . sothat means if the person has entered the date in 1940(20 century) and he sees it in the 2003 (21st centuary) he will see it as 1940 FOREVER..one last doubt please forgive me for the stupid question but then what does itmean by the statement that if the current year and specfied year they are in the range of 0-49 RR will show the date in the current centuary ?does it mean that whatever the date is like 1940 it will be reurned in the 21 st centaruy as like 1940 but internally with the centuary of 20th one?or as i thought that it will be returned as like 2040 if we go by the word to word description of RR format ?
thanx alot
October 13, 2003 - 11:56 am UTC
RR is an "input" format.
it means if you enter to_date( '01-jan-40', 'dd-mon-rr' ) Oracle will slide around the date based on the current year.
In 1999 and 2001 -- that would be the year 2040
As opposed to yy -- where the century is based on the current date. meaning
to_date( '01-jan-40', 'dd-mon-yy' )
is 1940 on December 31'st 1999 at 11:59:59 and 2040 on January 1st 2000 at 00:00:00
It just changes the sliding window.
I will strongly reiterate "i hope this shows you why it would be a truly utterly horrible idea to use yy or rr -- YYYY is "right"'
excellent as always
aman, October 13, 2003 - 11:29 pm UTC
thanx alot sir , thts what i was thinking too !!finally the conclusion goes that if we are putting somthing wrong we wil get the output wrong too
thanx alot for the excellent explanation
A reader, February 18, 2004 - 9:50 am UTC
Hi All
Even if i use DD-MON-YYYY the date is stored as '12-JAN-0004'
but if i use DD-MON-RRRR it stores properly '12-JAN-2004'
Why is this behavior in YYYY
By the way what would you advice us to use (YYYY or RRRR)
Regards
February 18, 2004 - 9:05 pm UTC
if you use dd-mon-yyyy as an INPUT FORMAT and you get 12-jan-0004 -- that means you are giving the database 12-jan-04, that is the year "4". you would have to give the database 12-jan-2004.
my point is - you and your end users cannot give anything "4" and expect "the right answer". the year is 2004, not 4.
A reader, October 06, 2005 - 7:15 am UTC
please tell me some more examples showing the difference between dd-mon-rr and dd-mon-yy format.
October 06, 2005 - 8:09 am UTC
hmmm.
yy - that always takes the current century and adds that to the date. so, in 1999, converting '05' using 'yy' would result in 1905. In 2001, converting '05' using 'yy' would result in 2005.
rr - that takes a sliding window of +- 50 years. converting '05' using 'RR' in either of 1999 or 2001 would result in 2005.
A reader, December 30, 2005 - 6:11 am UTC
Tom,
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
December 30, 2005 - 9:43 am UTC
decode( 0, -1, NULL, something_of_date_type )
^^^^
You used NULL as the first return type from decode. Hence this decode invocation will return VARCHAR2 (strings) as it's type - everything that is not a string returned from this decode will have to_char applied to it. therefore, it is really:
decode( 0,
-1, NULL,
to_char( something_of_date_type ) )
so, you have:
decode( 0,
-1, null,
to_char( to_date('25-MAY-1932','dd-mon-yyyy') ) )
which is the STRING
'25-may-32'
which when you assign to a date - using dd-mon-rr, since we are in the period of time around the year 2000, not 1900 - becomes
25-may-2032
beware the implicit conversion!!! The first return type from decode sets the type for the rest:
ops$tkyte@ORA10GR2> 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.
ops$tkyte@ORA10GR2> declare
2 v date;
3 begin
4 select decode(0,-1, to_date(NULL),
5 (to_date('05251932', 'MMDDYYYY')))
6 into v
7 from dual;
8 DBMS_OUTPUT.PUT_LINE(to_char(v,'DD-MON-YYYY'));
9 end;
10 /
25-MAY-1932
PL/SQL procedure successfully completed.
thanks tom
Giridhar, December 30, 2005 - 10:01 am UTC
thanks tom for Detailed explanation.
A Reader, January 24, 2008 - 3:48 pm UTC
Tom,
In the database the
NLS_DATE_FORMAT = 'DD-MON-RR' and
NLS_TIMESTAMP_FORMAT ='DD-MON-RR HH.MI.SSXFF AM'
When run a query :
select start_time, dt from tab_dt; I get the following results:
START_TIME DT
---------------------------------------------
01-JAN-08 09.30.01.454000 AM 04-JAN-08
01-JAN-08 09.30.10.836000 AM 04-JAN-08
Datatypes used:
START_TIME - TIMESTAMP(6)
DT - DATE
I am storing the above records in CSV format and loading it to another table. In the new table, the Year is getting loaded as 0008 for both.
Please let me know what needs to be done to fix this.
January 24, 2008 - 5:41 pm UTC
well.... we would sort of need to know what the program loading this does to the data wouldn't we..
and remember any and all database NLS settings are overwritten with defaults if the client specifies ANY NLS settings itself (eg: in the session that loads these, those formats might be very different)
I would recommend using YYYY to UNLOAD anything - it only makes sense, it is the only safe way.
A reader, January 30, 2008 - 10:33 am UTC
Tom,
Below the process that is used to load data:
This table is in schema1
--Table structure
create table tab_processed
(start_time TIMESTAMP(6)
,dt date);
--tab_dt.dat file
01-JAN-08 09.30.01.454000 AM,04-JAN-08
01-JAN-08 09.30.10.836000 AM,04-JAN-08
tab_dt.ctl
load data
INFILE 'c:\test\tab_dt.dat'
BADFILE 'c:\test\tab_dt.bad'
DISCARDFILE 'c:\test\tab_dt.dis'
TRUNCATE
INTO TABLE tab_processed
fields terminated by ","
optionally enclosed by '"'
TRAILING NULLCOLS
( START_TIME "to_timestamp(:START_TIME,'DD-MON-YYYY HH.MI.SS.FF AM') "
, DT "to_date(:DT,'DD-MON-YYYY')"
Once it is loaded to schema1,
it is loaded to schema2 with additional columns
This table is in schema2
--Table structure
create table tab_processed
(start_time TIMESTAMP(6)
,dt date
,start_dt date
,end_dt date
,create_time TIMESTAMP(6)
);
INSERT INTO schema2.tab_processed
(start_time,dt,start_dt, end_dt,create_time)
SELECT start_time, dt, SYSDATE,'', SYSTIMESTAMP
FROM schema1.tab_processed;
When the data is loaded to schema1.tab_processed, the year gets loaded as 0008 instead of 2008 even though the control file has the date format of 'DD-MON-YYYY' & timestamp format of 'DD-MON-YYYY HH.MI.SS.FF AM' and the same is being inserted to schema2.tab_processed. Can you advice what needs to be changed/corrected to resolve this issue? Please let me know, if I am not clear in the above explanation.
January 30, 2008 - 10:45 am UTC
umm, why are you using yyyy ?
and why are you not using rr??
think about this, when you say "i will give you up to a four character year, and you convert - YYYY" and I give you 08, what year is that?
It is the year 8, 0008, 008, 08 - they are all the year 8.
in fact, why are you using a silly two character date field in the first place, you would think we learned our lesson almost a decade ago :(
ops$tkyte%ORA10GR2> select to_timestamp('01-JAN-08 09.30.01.454000 AM','DD-MON-YYYY HH.MI.SS.FF AM'),
2 to_date('04-JAN-08','DD-MON-YYYY')
3 from dual
4 /
TO_TIMESTAMP('01-JAN-0809.30.01.454000AM','DD-MON-YYYYHH.MI.SS.FFAM')
---------------------------------------------------------------------------
TO_DATE('04-JAN-08',
--------------------
01-jan-0008 09:30:01.454000000
04-jan-0008 00:00:00
ops$tkyte%ORA10GR2> select to_timestamp('01-JAN-08 09.30.01.454000 AM','DD-MON-rr HH.MI.SS.FF AM'),
2 to_date('04-JAN-08','DD-MON-rr')
3 from dual
4 /
TO_TIMESTAMP('01-JAN-0809.30.01.454000AM','DD-MON-RRHH.MI.SS.FFAM')
---------------------------------------------------------------------------
TO_DATE('04-JAN-08',
--------------------
01-jan-2008 09:30:01.454000000
04-jan-2008 00:00:00
A reader, January 30, 2008 - 11:02 am UTC
Tom,
Thanks for your response.
In the database the
NLS_DATE_FORMAT = 'DD-MON-RR' and
NLS_TIMESTAMP_FORMAT ='DD-MON-RR HH.MI.SSXFF AM'
I ran the same query that you posted in your response. But I am getting the year as 08 instead of 2008. Please let me know, what should I use to get the correct year.
col ts format a40
select to_timestamp('01-JAN-08 09.30.01.454000 AM','DD-MON-rr HH.MI.SS.FF AM') ts,to_date('04-JAN-08','DD-MON-rr') dt from dual;
TS DT
----------------------------------- ---------
01-JAN-08 09.30.01.454000000 AM 04-JAN-08
RR
A reader, January 30, 2008 - 11:09 am UTC
Tom,
One of my friend from non-oracle background asked me this question. What does 'RR' stands for? Why Oracle choose 'RR' and not 'XX' or TT?
One can tell that DD is for Day, MM is for Month, YY is for year, but can't make out the what does RR stands for.
January 30, 2008 - 1:43 pm UTC
say it out loud: rrrrrrrrr
sort of like a growl.
we are growling at you for using two characters, telling you to STOP it
:)
no reason. Just RR, could have been ZZ or XX or TT, but then you would ask "why ZZ"...
A reader, January 30, 2008 - 2:17 pm UTC
Tom,
Thanks for your response.
In the database the
NLS_DATE_FORMAT = 'DD-MON-RR' and
NLS_TIMESTAMP_FORMAT ='DD-MON-RR HH.MI.SSXFF AM'
I ran the same query that you posted in your response. But I am getting the year as 08 instead of
2008. Please let me know, what should I use to get the correct year.
col ts format a40
select to_timestamp('01-JAN-08 09.30.01.454000 AM','DD-MON-rr HH.MI.SS.FF AM')
ts,to_date('04-JAN-08','DD-MON-rr') dt from dual;
TS DT
----------------------------------- ---------
01-JAN-08 09.30.01.454000000 AM 04-JAN-08
I also tried with rrrr, but still the result on sqlplus session show 08 for year. What should I do get the correct year?
col ts format a40
select to_timestamp('01-JAN-08 09.30.01.454000 AM','DD-MON-rrrr HH.MI.SS.FF AM')
ts,to_date('04-JAN-08','DD-MON-rrrr') dt from dual;
TS DT
----------------------------------- ---------
01-JAN-08 09.30.01.454000000 AM 04-JAN-08
January 30, 2008 - 4:08 pm UTC
grrr - IN YOUR CONTROL FILE PLEASE
you need to use RR on the WAY IN (you have two characters, totally insufficient to represent a date, but if you use RR on the WAY IN (like I did above - with my to_date on the STRING), we'll guess as best we can what the century should be.
RR in your CONTROL FILE - on the way IN - when the string is converted to a date.
A reader, January 30, 2008 - 5:07 pm UTC
Tom,
Thank You.
Reader, January 30, 2008 - 8:17 pm UTC
Tom,
Can you please explain the difference between RR and RRRR formats?
January 31, 2008 - 3:22 am UTC
by default, using the "loose matching" that dates use, they are pretty much the same. When you use FX however, it is different (fx is "Format eXact"). Then the question would be "what is the difference between rrrr and yyyy" and the answer would be the same as what is the difference between rr and rrrr without fx...
ops$tkyte%ORA10GR2> alter session set nls_date_format = 'dd-mon-yyyy';
Session altered.
ops$tkyte%ORA10GR2> select to_date( '08', 'rr' ) from dual;
TO_DATE('08
-----------
01-jan-2008
ops$tkyte%ORA10GR2> select to_date( '2008', 'rr' ) from dual;
TO_DATE('20
-----------
01-jan-2008
ops$tkyte%ORA10GR2> select to_date( '08', 'rrrr' ) from dual;
TO_DATE('08
-----------
01-jan-2008
ops$tkyte%ORA10GR2> select to_date( '2008', 'rrrr' ) from dual;
TO_DATE('20
-----------
01-jan-2008
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select to_date( '08', 'fxrr' ) from dual;
TO_DATE('08
-----------
01-jan-2008
ops$tkyte%ORA10GR2> select to_date( '2008', 'fxrr' ) from dual;
select to_date( '2008', 'fxrr' ) from dual
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
ops$tkyte%ORA10GR2> select to_date( '08', 'fxrrrr' ) from dual;
select to_date( '08', 'fxrrrr' ) from dual
*
ERROR at line 1:
ORA-01862: the numeric value does not match the length of the format item
ops$tkyte%ORA10GR2> select to_date( '2008', 'fxrrrr' ) from dual;
TO_DATE('20
-----------
01-jan-2008
A reader, February 05, 2008 - 11:57 pm UTC
Tom,
test.start_date is of type DATE.
In the database:
NLS_DATE_FORMAT = DD-MON-RR
The below quey returns data.
select * from test
where to_char(start_date,'DD-MON-YYYY') = '04-FEB-2008';
But the below query does not return data:
select * from test where start_date = '04-FEB-2008';
Can you please tell me why 2nd query does not return data?
February 06, 2008 - 7:41 am UTC
why would you convert a date into a string and not a string into a DATE!
ugh....
think about it,
to_char(dt,'dd-mon-yyyy') results in a string with NO TIME, which you then compared to a string in the same format.
where dt = 'string' results in a dt compared to a string converted with the default date format, the string had no time component, so it is set to midnight - your dt column on the other hand, likely has "time" in it.
use >= and < on a range to get all of the records for a day (do NOT put a function on the date column - that would obviate index access paths! do it in the fashion I demonstrate
ops$tkyte%ORA10GR2> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
Session altered.
ops$tkyte%ORA10GR2> create table test ( start_date date );
Table created.
ops$tkyte%ORA10GR2> select sysdate from dual;
SYSDATE
--------------------
06-feb-2008 08:13:33
ops$tkyte%ORA10GR2> insert into test values ( sysdate-2 );
1 row created.
ops$tkyte%ORA10GR2> variable x varchar2(30)
ops$tkyte%ORA10GR2> exec :x := '04-feb-2008';
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> select *
2 from test
3 where start_date = :x
4 /
no rows selected
ops$tkyte%ORA10GR2> select *
2 from test
3 where start_date >= to_date(:x,'dd-mon-yyyy')
4 and start_date < to_date(:x,'dd-mon-yyyy')+1
5 /
START_DATE
--------------------
04-feb-2008 08:14:47
A reader, February 06, 2008 - 4:11 pm UTC
Tom,
Question I asked before:
Below the process that is used to load data:
This table is in schema1
--Table structure
create table tab_processed
(start_time TIMESTAMP(6)
,dt date);
--tab_dt.dat file
01-JAN-08 09.30.01.454000 AM,04-JAN-08
01-JAN-08 09.30.10.836000 AM,04-JAN-08
tab_dt.ctl
load data
INFILE 'c:\test\tab_dt.dat'
BADFILE 'c:\test\tab_dt.bad'
DISCARDFILE 'c:\test\tab_dt.dis'
TRUNCATE
INTO TABLE tab_processed
fields terminated by ","
optionally enclosed by '"'
TRAILING NULLCOLS
( START_TIME "to_timestamp(:START_TIME,'DD-MON-YYYY HH.MI.SS.FF AM') "
, DT "to_date(:DT,'DD-MON-YYYY')"
Once it is loaded to schema1,
it is loaded to schema2 with additional columns
This table is in schema2
--Table structure
create table tab_processed
(start_time TIMESTAMP(6)
,dt date
,start_dt date
,end_dt date
,create_time TIMESTAMP(6)
);
INSERT INTO schema2.tab_processed
(start_time,dt,start_dt, end_dt,create_time)
SELECT start_time, dt, SYSDATE,'', SYSTIMESTAMP
FROM schema1.tab_processed;
When the data is loaded to schema1.tab_processed, the year gets loaded as 0008 instead of 2008 even
though the control file has the date format of 'DD-MON-YYYY' & timestamp format of 'DD-MON-YYYY
HH.MI.SS.FF AM' and the same is being inserted to schema2.tab_processed. Can you advice what needs
to be changed/corrected to resolve this issue? Please let me know, if I am not clear in the above
explanation.
Your Answer:
You mentioned to change the control file to use RR instead of YYYY. This loads the year as 2008 instead of 0008.
My question now:
In the database the
NLS_DATE_FORMAT = 'DD-MON-RR' and
NLS_TIMESTAMP_FORMAT ='DD-MON-RR HH.MI.SSXFF AM'
Hence, when I run the below query, I get the year as 08.
Can I change the NLS_DATE_FORMAT to 'DD-MON-RRRR' and NLS_TIMESTAMP_FORMAT to 'DD-MON-RRRR HH.MI.SSXFF AM'to get the year as 2008,
instead of changing YYYY to RR in the control file?
select start_time, dt from tab_dt; I get the following results:
START_TIME DT
---------------------------------------------
01-JAN-08 09.30.01.454000 AM 04-JAN-08
01-JAN-08 09.30.10.836000 AM 04-JAN-08
Thanks
February 07, 2008 - 7:36 am UTC
My recommendation is always:
explicitly use the format you are expecting.
never rely on the database defaults - never.
defaults change.
And if the client sets any NLS settings, the database settings don't count, they are all defaulted (eg: code that works on unix might fail on windows because windows sets the nls_lang - causing all database nls_* settings to be ignored)
http://asktom.oracle.com/tkyte/Misc/NLSDateFormat.html Be explicit, code what you mean, put the date format there.
A reader, February 07, 2008 - 9:23 am UTC
Tom,
If I create the after logon trigger, which sets the date format to 'DD-MON-RRRR', will it be in effect for all the client sessions (I mean windows or unix)?
Thanks a lot for your answers.
A Reader, February 07, 2008 - 5:16 pm UTC
Thank You again for your answers.
I have some data in the table which has year as 0008 & 0007.
I am using the below update statements to update the records. I tested and it works fine. Please let me know if you have any better way of updating the records
alter session set nls_date_format = 'DD-MON-RRRR';
alter session set nls_timestamp_format = 'DD-MON-RRRR';
select start_time, dt from tab_dt; I get the following results:
START_TIME DT
-------------------------------- ------------
01-JAN-0008 09.30.01.454000 AM 04-JAN-0008
01-JAN-0008 09.30.10.836000 AM 04-JAN-0008
--to update fields with DATE datatype
update tab_dt t
set t.dt = to_date(to_Char(dt,'mm/dd/')||'2007','mm/dd/yyyy')
where to_char(dt,'yyyy') like '%0007';
update tab_dt t
set t.dt = to_date(to_Char(dt,'mm/dd/')||'2008','mm/dd/yyyy')
where to_char(dt,'yyyy') like '%0008';
--To update fields with TIMESTAMP(6) datatype
update tab_dt t
set t.START_TIME = TO_TIMESTAMP(to_Char(START_TIME,'mm/dd/')||'2007','mm/dd/yyyy HH.MI.SSXFF AM')
where to_char(START_TIME,'yyyy') like '%0007';
Thanks
February 07, 2008 - 5:23 pm UTC
ops$tkyte%ORA10GR2> select * from t;
X
--------------------
01-jan-0007 00:00:00
02-feb-0008 00:00:00
11-mar-2007 00:00:00
12-apr-2008 00:00:00
ops$tkyte%ORA10GR2> update t set x = add_months(x,2000*12) where x < to_date( '01-jan-0010', 'dd-mon-yyyy' );
2 rows updated.
ops$tkyte%ORA10GR2> select * from t;
X
--------------------
01-jan-2007 00:00:00
02-feb-2008 00:00:00
11-mar-2007 00:00:00
12-apr-2008 00:00:00
avoids tons of to_char/to_dates, makes a single pass, could even use an index to find the rows if there is a small set of rows to be updated.
A reader, February 08, 2008 - 12:10 pm UTC
Your Answer to my previous question was:
Change the control file to use RR instead of YYYY. This loads the year as 2008 instead of 0008.
As you said, we created the after database logon trigger to set the session date to DD-MON-RRRR.
Does it matter if I put RRRR in SQL loader control file instead of RR? I tried and both works fine. Please let me know your opinion about this.
Thanks
February 08, 2008 - 3:29 pm UTC
you have RR data, it wouldn't make sense to use RRRR unless you wanted to.
Look at your input data, probably you should use
fxDD-MON-rr
to perform strict validation of the input data
A reader, February 08, 2008 - 11:44 pm UTC
After creating the after logon database trigger, to set the date format and timestamp format to DD-MON-RRRR, when I run the query,
select start_time, dt from tab_dt; I get the following results:
START_TIME DT
-------------------------------- ------------
01-JAN-2008 09.30.01.454000 AM 04-JAN-2008
01-JAN-2008 09.30.10.836000 AM 04-JAN-2008
I use the above as a CSV file and load it Schema1.
I did not understand your answer. Can you elaborate?
Can you please let me know why I should use fxDD-MON-rr but not DD-MON-RRRR in the sql*loader control file?
A reader, February 12, 2008 - 11:27 am UTC
I was wondering if you could answer the above question.
Also, I tried the update statement that you posted on the table which has timestamp datatype. It is converting timestamp to date type
update t
set x = add_months(x,2000*12)
where x < to_date( '01-jan-0010', 'dd-mon-yyyy' );
START_TIME - TIMESTAMP(6)
DT - DATE
BEFORE update:
select start_time, dt from tab_dt; I get the following results:
START_TIME DT
-------------------------------- ------------
01-JAN-0008 09.30.01.454000 AM 04-JAN-0008
01-JAN-0008 09.30.10.836000 AM 04-JAN-0008
After update:
select start_time, dt from tab_dt; I get the following results:
START_TIME DT
----------------------- ------------
01-JAN-2008 09:30:01 AM 04-JAN-0008
01-JAN-0008 09:30:10 AM 04-JAN-0008
I want to preserve the time component in START_TIME column.
Please let me know what needs to be changed in the update statement to make it work?
Thanks
February 12, 2008 - 1:29 pm UTC
I am (for the Nth time) telling you
BE EXPLICIT
DO NOT RELY ON DEFAULTS
YOUR CSV FILE HAS DATE IN A CERTAIN FORMAT, for goodness sake, encode that format in the control file
that way when your code is moved to another server, hey, it still works
that way when someone says "who the heck wrote this trigger, get rid of it", your code still works
I fail to see why you don't want to put it where it belongs.
to preserve the time, USE A DATE FORMAT IN YOUR CONTROL FILE THAT INCLUDES THE DATE AND THE TIME
and just drop that trigger.
A reader, February 12, 2008 - 2:53 pm UTC
Tom,
Sorry to bother you on this one again and thanks a lot for all your answers.
I understand your logic of putting RR in the control file.
My situation is as follows:
I added DD-MON-RRRR to the after logon trigger
since I wanted the year to be displayed as 4 digits.
I am not sure if it makes sense to use RRRR in the trigger.
I was wondering if I should use DD-MON-YYYY in the trigger
but not DD-MON-RRRR if the year has to be displayed as 4 digits whenever a client session connects to oracle server.
Since I put DD-MON-RRRR in the trigger, my CSV file has year in 4 digits:
select start_time, dt from tab_dt;
START_TIME DT
-------------------------------- ------------
01-JAN-2008 09.30.01.454000 AM 04-JAN-2008
01-JAN-2008 09.30.10.836000 AM 04-JAN-2008
I also used DD-MON-RRRR in the SQL*LOader control file. I tested, by putting
RR and RRRR in the control file by having 4 digit year in the CSV file.
Both works fine. I just want to know, if it is not correct to use RRRR in control file.
Also, can you please tell me what is the difference in putting RR VS RRRR in the control file.
Is it correct to do the following:
1)I will have the database default as DD-MON-RR,
2)Should I add DD-MON-YYYY in the trigger since I want the 4 digit year to be displayed whenever a client session logs in.
3)I will use RR in the control file
On a seperate note, year in some on the records are 0008. For which you passed me a update statement:
update t
set x = add_months(x,2000*12)
where x < to_date( '01-jan-0010', 'dd-mon-yyyy' );
The above update statement works fine for the date field.
I have some columns which are of timestamp data type. They also have year as 0008. If I use the above update statement on timestamp column,
it removes the XFF part in 'DD-MON-RRRR HH.MI.SSXFF AM'. I understand that add_months function is converting it to DATE and storing DD-MON-YYYY HH:MI:SS.
What changes do I need to make to the update statement to retain the HH.MI.SSXFF AM part of timestamp and just change the year from 0008 to 2008
START_TIME - TIMESTAMP(6)
DT - DATE
BEFORE update:
select start_time, dt from tab_dt; I get the following results:
START_TIME DT
-------------------------------- ------------
01-JAN-0008 09.30.01.454000 AM 04-JAN-0008
01-JAN-0008 09.30.10.836000 AM 04-JAN-0008
After running the below update statements,
update tab_dt
set START_TIME = add_months(START_TIME,2000*12)
where START_TIME < to_date( '01-jan-0010', 'dd-mon-yyyy' );
update tab_dt
set dt = add_months(x,2000*12)
where dt < to_date( '01-jan-0010', 'dd-mon-yyyy' );
the data will be as follows:
select start_time, dt from tab_dt; I get the following results:
START_TIME DT
----------------------- ------------
01-JAN-2008 09:30:01 AM 04-JAN-2008
01-JAN-2008 09:30:10 AM 04-JAN-2008
But, I want the data to be as follows:
START_TIME DT
-------------------------------- ------------
01-JAN-2008 09.30.01.454000 AM 04-JAN-2008
01-JAN-2008 09.30.10.836000 AM 04-JAN-2008
Thanks again for your answers
February 13, 2008 - 12:22 pm UTC
... I added DD-MON-RRRR to the after logon trigger
since I wanted the year to be displayed as 4 digits. ...
then you should select to_char( date_field, 'dd-mon-yyyy' )
I'll say it over and over - relying on the nls_date_format to be what you want it to be is a recipe for disaster. someone else at sometime in the future will say "what a stupid format, everyone knows yyyy/mm/dd is the best"
as an output format, yyyy and rrrr are equivalent - use whichever one makes you feel better. I prefer yyyy as it is the more obvious of the two.
... Since I put DD-MON-RRRR in the trigger, my CSV file has year in 4 digits: ...
see, you keep going back to that csv file, I don't see what it has to do with anything since you are putting the format to convert the strings in the csv file into a date IN THE CONTROL FILE (i don't care about your trigger during the load, you are putting the string to date format in the control file).
I think I am the one that told you to use RRRR in the control file, so yes, it would be correct and proper to use it there - in fact, I've said it over and over and over - put it in the CONTROL FILE.
please, enough on this.
here is my last word on this, period, final, done:
a) put the correct and proper format in your control file, the one that takes YOUR STRING from the csv file and converts it into an Oracle date or timestampl.
b) put the correct and desired format in a to_char in your select to convert the binary date data stored in the database into a string for humans to stare at. DO NOT rely on nls_date_formats or triggers if you have a precise specific format you demand - USE THAT FORMAT directly, explicitly.
A reader, February 13, 2008 - 12:53 pm UTC
Tom,
Thanks for all your answers.
I understand your point of having the date in yyyy/mm/dd format. I cannot change the format to yyyy/mm/dd, since lot of code, that is previously written based on DD-MON-RR will fail.
I am still in the process of changing codes and making it work. I wanted the year to be displayed as 4 digits whenever someone logs in. Hence, I changed the session setting to DD-MON-RRRR. From your posting, I understood that RRRR is same as YYYY.
I will eventually use yyyy/mm/dd hh24:mi:ss format.
Thank You again.
February 13, 2008 - 1:21 pm UTC
sigh, I am not suggesting you change your format to yyyy/mm/dd, that was just AN EXAMPLE of someone, anyone in the future deciding your default format was bad and changing it to suit THEM.
A reader, February 13, 2008 - 1:31 pm UTC
Tom,
I understood that you were giving an example.
I just wanted to let you know that, we want it to be yyyy/mm/dd eventually. Sorry for the confusion.
I really appreciate your help.
A Reader, February 19, 2008 - 3:22 pm UTC
year in some on the records are 0008. For which you passed me an update
statement:
update t
set x = add_months(x,2000*12)
where x < to_date( '01-jan-0010', 'dd-mon-yyyy' );
The above update statement works fine for the date field.
I have some columns which are of timestamp data type. They also have year as 0008. If I use the
above update statement on timestamp column,
it removes the XFF part in 'DD-MON-RRRR HH.MI.SSXFF AM'. I understand that add_months function is
converting it to DATE and storing DD-MON-YYYY HH:MI:SS.
What changes do I need to make to the update statement to retain the HH.MI.SSXFF AM part of
timestamp and just change the year from 0008 to 2008
START_TIME - TIMESTAMP(6)
DT - DATE
BEFORE update:
select start_time, dt from tab_dt; I get the following results:
START_TIME DT
-------------------------------- ------------
01-JAN-0008 09.30.01.454000 AM 04-JAN-0008
01-JAN-0008 09.30.10.836000 AM 04-JAN-0008
After running the below update statements,
update tab_dt
set START_TIME = add_months(START_TIME,2000*12)
,set dt = add_months(x,2000*12)
where START_TIME < to_date( '01-jan-0010', 'dd-mon-yyyy' );
the data will be as follows:
select start_time, dt from tab_dt; I get the following results:
START_TIME DT
----------------------- ------------
01-JAN-2008 09:30:01 AM 04-JAN-2008
01-JAN-2008 09:30:10 AM 04-JAN-2008
But, I want the data to be as follows:
START_TIME DT
-------------------------------- ------------
01-JAN-2008 09.30.01.454000 AM 04-JAN-2008
01-JAN-2008 09.30.10.836000 AM 04-JAN-2008
February 20, 2008 - 8:04 am UTC
(hint: the documentation actually covers things like how to do things to various datatypes. Once one learns a technique "find rows before year 10, add 2000 years to them", one should be able to apply that technique in a variety of ways against other datatypes by finding out how to 'add' things to them)
ops$tkyte%ORA10GR2> create table t ( x timestamp );
Table created.
ops$tkyte%ORA10GR2> insert into t values ( to_timestamp( '15-mar-0008 12:01:02.12345', 'dd-mon-yyyy hh24:mi:ss.xff' ) );
1 row created.
ops$tkyte%ORA10GR2> insert into t values ( systimestamp );
1 row created.
ops$tkyte%ORA10GR2> select to_char( x, 'dd-mon-yyyy hh24:mi:ssxff' ) from t;
TO_CHAR(X,'DD-MON-YYYYHH24:MI:
------------------------------
15-mar-0008 12:01:02.123450
20-feb-2008 08:27:58.729224
ops$tkyte%ORA10GR2> update t
2 set x = x + numtoyminterval( 2000, 'year' )
3 where x <= to_timestamp( '01-jan-0010', 'dd-mon-yyyy' );
1 row updated.
ops$tkyte%ORA10GR2> select to_char( x, 'dd-mon-yyyy hh24:mi:ssxff' ) from t;
TO_CHAR(X,'DD-MON-YYYYHH24:MI:
------------------------------
15-mar-2008 12:01:02.123450
20-feb-2008 08:27:58.729224
A Reader, February 20, 2008 - 6:33 am UTC
Tom,
For the above question, you had mentioned:
to preserve the time, USE A DATE FORMAT IN YOUR CONTROL FILE THAT INCLUDES THE DATE AND THE TIME
and just drop that trigger.
I have the time component in Control file and I also dropped the trigger. But the timestamp data type is getting converted to DATE after using add_months. Can you please let me know how to change the update statement so as to preserve time component of the timestamp field?
February 20, 2008 - 9:27 am UTC
see above...
Year format change
A reader, July 08, 2017 - 3:43 am UTC
Hi All,
Could you please help me to change the year format.
when i enter in "10-mar-40" it shows as 10-mar-1940, but i need the below format "10-mar-2040" not "10-mar-1940.
July 08, 2017 - 11:21 am UTC
1) Depends on what application/tool etc you are *entering* the date into.
2) How is it showing as 1940 ?
SQL> select to_date('10-mar-40','dd-mon-rr') from dual;
TO_DATE('10-MAR-40'
-------------------
10/03/2040 00:00:00
SQL> select to_date('10-mar-40','dd-mon-yy') from dual;
TO_DATE('10-MAR-40'
-------------------
10/03/2040 00:00:00