-
Timestamp format
How can I have the default for a TIMESTAMP datatype attribute inserted in the following format?
2006-08-21-16.37.30.459999
When I use systimestamp, I get the following timestamp format:
21-AUG-06 05.19.22.562000 PM +05:30
Thanks in advance.
-
erm..
insert into timestamp_table(timestamp_column)
select to_timestamp('2006-08-21-16.37.30.459999', 'YYYY-MM-DD-HH24.MI.SS.FF6') from dual
-
what you need to understand is that both dates and timestamps are internally stored as numbers - a value of the number of days and fractional days since a specific point in time. if used in a query, they are to_char() automatically with a certain format, if inserted, they are to_date() or to_timestamp() also with that certain format. I dont know if enough of the internals are revealed to e.g. allow you to insert the number 1.5 into a date column (thereby giving you a date that is 1.5 days after the epoch) so all insertion into, modification of and selection from date and time type columns must be done with sending in a representation of the time in format X and addionally a description of that format X in oracle compliant formatting
-
Thanks for the input.
The issue was resolved by also setting NLS_TIMESTAMP_FORMAT='YYYY-MM-DD-HH24.MI.SS.FF';
Otherwise get the error:
SQL> INSERT INTO ADMIN.RESUEST VALUES(1, 1, DEFAULT, 1, DEFAULT, DEFAULT);
INSERT INTO ADMIN.RESUEST VALUES(1, 1, DEFAULT, 1, DEFAULT, DEFAULT)
*
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month
SQL>
Thanks once again.
-
indeed... you can set the system wide default format for a timestamp to be that.. but bear in mind then that oracle will only thus go and do an implicit TO_CHAR or TO_TIMESTAMP using that format string..
In all programming situations, it pays to be explicit rather than implicit; assuming a computer can think gets you into trouble.
additionally, when deviating from the default values, you should be aware that your change may break other code that also does its work implicitly with the old format. If you ahve a legacy app that is inserting or selecting timestamp data and is expecting it to be in some particular format, you potentially just broke it
The solution? Dont change defaults. Dont rely on implicit conversions. Be thorough now to avoid debugging later
-
Originally Posted by cjard
select to_timestamp('2006-08-21-16.37.30.459999', 'YYYY-MM-DD-HH24.MI.SS.FF6') from dual
The above give timestamp values in following format:
31-DEC-99 11.59.59.999999000 PM
However, the requirement is to have timestamp in the following format:
9999-12-31-23.59.59.999999
How can this be done without making changes to NLS_TIMESTAMP_FORMAT?
Thanks.
Last edited by ggnanaraj; 08-22-2006 at 01:59 AM.
-
Originally Posted by cjard
indeed... you can set the system wide default format for a timestamp to be that..
The change was made for the session not system wide...
alter session SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD-HH24.MI.SS.FF';
-
Originally Posted by ggnanaraj
The above give timestamp values in following format:
31-DEC-99 11.59.59.999999000 PM
However, the requirement is to have timestamp in the following format:
9999-12-31-23.59.59.999999
How can this be done without making changes to NLS_TIMESTAMP_FORMAT?
Thanks.
As cjard said, you have to understand that dates and timestamps are stored in an internal format, NOT STRINGS, strings are just for DISPLAY. SO ALWAYS CONVERT EXPLICITLY :
- a DATE or TIMESTAMP TO STRING using TO_CHAR when you want to display it
- a STRING to DATE or TIMESTAMP using TO_DATE or TO_TIMESTAMP when you want to insert/update/compare dates ...
You can have a look here if you want to know how dates are stored internally. (excerpt from the OCCI documentation).
HTH & Regards,
rbaraer
-
Originally Posted by RBARAER
As cjard said, you have to understand that dates and timestamps are stored in an internal format, NOT STRINGS, strings are just for DISPLAY. SO ALWAYS CONVERT EXPLICITLY :
Thanks for the input.
The idea is to maintain the storage/display formats of TIMESTAMP - externally - without change to already existing application code, which is to be moved to work on Oracle. I'm not concerned about how it is stored internally. The timestamp format expected when doing a SELECT or INSERT is 2006-08-21-16.37.30.459999.
The 'definition of the table can be altered to a certain extent with respect to defaults' and so can the 'session settings' but code is to be changed only if the previous 2 cannot handle the requirement.
Given this requirement, the issue can be resolved only by use of session applicable code as follows:
alter session SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD-HH24.MI.SS.FF';
Thanks & Regards.
ggnanaraj
-
ALTER SESSION might match your needs for now, but it is a very bad habit IMO to rely on the session date format. I'm pretty sure you will regret it in the long term. So as a short-term solution that can be OK, but keep in mind that this should be changed in the future.
What we do in order to use the same format everywhere we need to is using a date format as a package variable and use it instead of hard-coded string format. For example :
Code:
CREATE OR REPLACE PACKAGE PKG_Dates AS
GstrDateFormat VARCHAR2(25) := 'YYYY/MM/DD HH24:MI:SS';
GstrTimestampFormat VARCHAR2(30) := 'YYYY/MM/DD HH24:MI:SS.FF6';
END PKG_Dates;
/
This does not work in pure SQL since package variables cannot be called from there, but works perfectly in all PL/SQL code. For example :
Code:
rbaraer@Ora10g> var rc REFCURSOR
rbaraer@Ora10g> BEGIN
OPEN :rc FOR
SELECT TO_CHAR(SYSDATE, PKG_Dates.GstrDateFormat),
TO_CHAR(SYSTIMESTAMP, PKG_Dates.GstrTimestampFormat)
FROM DUAL;
END; 2 3 4 5 6
7 /
PL/SQL procedure successfully completed.
rbaraer@Ora10g> PRINT rc
TO_CHAR(SYSDATE,:B2)
---------------------------------------------------------------------------
TO_CHAR(SYSTIMESTAMP,:B1)
---------------------------------------------------------------------------
2006/08/22 10:49:54
2006/08/22 10:49:54.949515
rbaraer@Ora10g>
HTH & Regards,
rbaraer
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|