CODEWITHSUNDEEP
sqloracle-database
Hoang Nguyen
Hoang Nguyen

Reputation: 61

Oracle What is The Real Date in TIMESTAMP Field?

I have a field with data type is TIMESTAMP(DD-MON-RR HH.MI.SSXFF AM is the format). When I run a select statement in SQL developer this field display '04-NOV-08 12.00.00.000000000 AM' as the result and I think 08 is the 2008 year but when I debug in my java code this field displays the value 'Sun Nov 04 00:00:00 ICT 8' it means the year is 8. Could you tell me which is the correct value?

Upvotes: 0

Views: 721

Answers (1)

Alex Poole
Alex Poole

Reputation: 191415

Your default NLS_TIMESTAMP_FORMAT (which you can change as shown here, though you shouldn't really rely on it) gives you a two-digit representation of the year.

For display purposes there is no difference between the YY and RR date format models. Your query is showing you the year as just 08, but that could represent year 8 in any century.

As @DanBracuk suggested you can see the full year by specifying the four-digit year format mask, with to_char(value, 'YYYY-MM-DD'); that gave you 0008-11-04. The value stored in your timestamp is therefore really in year 8, not 2008, and Java was showing you the correct value. (Which you would expect if you are passing the data as a datetime, not as a string; but you didn't show that).

Since you presumably don't actually want data from year 8 in your table you have a data issue. You may be able to correct your data by simply adding 2000 years to every value, but that assumes they are all showing a year from the first century, and the rest of the date is correct.

You also need to figure out how that data got there, and if it is still being created with the wrong century. The simplest explanation might be from a two-digit year being supplied during an insert from a session that has a four-digit-year NLS setting. This is exactly why you should not rely on NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, etc., as the person who wrote the code generally has no control over the session of the person running it.

You can see what happens with a 2-digit year and 4-digit mask with this demo. This inserts data with the same string value being converted using both to_date and to_timestamp, and with RR, RRRR, YY and YYYY format masks.

create table t42 (id number, ts timestamp);
insert into t42 (id, ts) values (1, to_date('04-NOV-08', 'DD-MON-RR'));
insert into t42 (id, ts) values (2, to_timestamp('04-NOV-08', 'DD-MON-RR'));
insert into t42 (id, ts) values (3, to_date('04-NOV-08', 'DD-MON-RRRR'));
insert into t42 (id, ts) values (4, to_timestamp('04-NOV-08', 'DD-MON-RRRR'));
insert into t42 (id, ts) values (5, to_date('04-NOV-08', 'DD-MON-YY'));
insert into t42 (id, ts) values (6, to_timestamp('04-NOV-08', 'DD-MON-YY'));
insert into t42 (id, ts) values (7, to_date('04-NOV-08', 'DD-MON-YYYY'));
insert into t42 (id, ts) values (8, to_timestamp('04-NOV-08', 'DD-MON-YYYY'));

    select id, ts, to_char(ts, 'YYYY-MM-DD') as full_year from t42;

        ID TS                           FULL_YEAR
---------- ---------------------------- ----------
         1 04-NOV-08 00.00.00.000000000 2008-11-04
         2 04-NOV-08 00.00.00.000000000 2008-11-04
         3 04-NOV-08 00.00.00.000000000 2008-11-04
         4 04-NOV-08 00.00.00.000000000 2008-11-04
         5 04-NOV-08 00.00.00.000000000 2008-11-04
         6 04-NOV-08 00.00.00.000000000 2008-11-04
         7 04-NOV-08 00.00.00.000000000 0008-11-04
         8 04-NOV-08 00.00.00.000000000 0008-11-04

The string representation using my default timestamp model shows all the values as being the same, with the years all shown as just 08, as you are seeing. But the values that were inserted with the YYYY format mask were - correctly - interpreted as 0008, not 2008.

You can also see what relying entirely on the NLS settings can do:

alter session set nls_timestamp_format = 'DD-MON-YY HH24:MI:SS.FF3';

insert into t42 (id, ts) values (9, '04-NOV-08');

alter session set nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS.FF3';

insert into t42 (id, ts) values (10, '04-NOV-08');

select id, ts, to_char(ts, 'YYYY-MM-DD') as full_year from t42 where id > 8;

        ID TS                       FULL_YEAR
---------- ------------------------ ----------
         9 04-NOV-2008 00:00:00.000 2008-11-04
        10 04-NOV-0008 00:00:00.000 0008-11-04

The same code was run, but this ended up with two very different values.

Aside from ad hoc queries, perhaps, don't ever rely on NLS settings. You shoudl not assume anything about another user's session settings. If you have a string value to convert to a date or timestamp, always explicitly call to_date or to_timestamp and specify the correct format mask. If possible you should use 4-digit years in your string values. You might also be able to use date/timestamp literals, depending on how the string is being presented.

Upvotes: 1

Related Questions