| sysdate functioning [message #616884] |
Sun, 22 June 2014 04:16  |
 |
aruntutor
Messages: 10
Registered: June 2014
Location: chennai
|
Junior Member |
|
|
Hi,
This two cols giving same results , dunno why ?. Can anyone explains to me
select sysdate, sysdate-4/24 from dual;
SYSDATE SYSDATE-4/24
--------- ------------
22-JUN-14 22-JUN-14
|
|
|
|
| Re: sysdate functioning [message #616885 is a reply to message #616884] |
Sun, 22 June 2014 04:27  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
SYSDAET - 4/24 means you are subtracting 4 hours from SYSDATE. You can also specify as :
select sysdate, sysdate - interval '6' hour from dual;
Easy to read and understand.
|
|
|
|
| Re: sysdate functioning [message #616889 is a reply to message #616884] |
Sun, 22 June 2014 06:04 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
aruntutor wrote on Sun, 22 June 2014 05:16Hi,
This two cols giving same results , dunno why ?
DATE is stored internally in its own format. When you issue select of a date Oracle implicitly converts it from that internal format to a human readable string using session default format. This format is set by session NLS_DATE_FORMAT parameter. It looks like in your case it is set to 'DD-MON-RR'. Therefore only day, month abbreviation and last 2 digits of the year are displayed. That's why you see wame value for two different dates. Either set proper session default date format or use explicit conversion:
SQL> select value
2 from nls_session_parameters
3 where parameter = 'NLS_DATE_FORMAT'
4 /
VALUE
----------------------------------------
DD-MON-RR
SQL> select sysdate, sysdate-4/24 from dual;
SYSDATE SYSDATE-4
--------- ---------
22-JUN-14 22-JUN-14
SQL> select to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'),
2 to_char(sysdate - 4 / 24,'mm/dd/yyyy hh24:mi:ss')
3 from dual
4 /
TO_CHAR(SYSDATE,'MM TO_CHAR(SYSDATE-4/2
------------------- -------------------
06/22/2014 07:03:44 06/22/2014 03:03:44
SQL> alter session set nls_date_format = 'mm/dd/yyyy hh24:mi:ss'
2 /
Session altered.
SQL> select sysdate, sysdate-4/24 from dual;
SYSDATE SYSDATE-4/24
------------------- -------------------
06/22/2014 07:04:09 06/22/2014 03:04:09
SQL>
SY.
|
|
|
|
|
|
|
|
| Re: sysdate functioning [message #616906 is a reply to message #616905] |
Sun, 22 June 2014 12:49 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
|
Ed stevens blog about date format is also good. Mostly it comes in first search in google when searched for "But I want to store dare in format"... Hope OP would do some search by himself.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
