Date format issues [message #632483] |
Sun, 01 February 2015 12:57 |
|
gorants
Messages: 85 Registered: May 2014 Location: ATL
|
Member |
|
|
Hello All,
I am having issues with Date format, can some one help on this.
Question 1:
Below is the my current system date.
select sysdate from dual; ---> 01-Feb-2015 10:10:26
But when i execute below query, first time it shows correct system time, after that on every re execute it display the same, what was the wrong here.
SELECT TO_CHAR(SYSDATE,'Mon DD YYYY HH:MMAM') FROM DUAL; --Feb 01 2015 10:02AM
Question 2 :
I was trying to add one day to sysdate.
v_Z := sysdate + 1;
DBMS_OUTPUT.PUT_LINE('A Day later ' || TO_CHAR (v_Z, 'Mon DD YYYY HH:MMAM'));
output : A Day later Feb 02 2015 12:01AM.
Expected : A Day later Feb 02 2015 12:00AM
[Updated on: Sun, 01 February 2015 12:59] Report message to a moderator
|
|
|
|
Re: Date format issues [message #632485 is a reply to message #632483] |
Sun, 01 February 2015 13:15 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Question 1:
select sysdate from dual;
SYSDATE return type is DATE. You want to display it on screen without telling Oracle in what format. Therefore Oracle implicitly converts DATE to string using client side settings - parameters NLS_CALENDAR, NLS_DATE_FORMAT, NLS_DATE_LANGUAGE in NLS_SESSION_PARAMETERS. In my case:
select parameter,value from nls_session_parameters;
...
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
...
Therefore:
SQL> select sysdate from dual;
SYSDATE
---------
01-FEB-15
SQL>
In your case, based on results you posted, NLS_DATE_FORMAT is DD-Mon-YYYY HH24:Mi:SS. That's why you get results you posted. When you issue:
SELECT TO_CHAR(SYSDATE,'Mon DD YYYY HH:MMAM') FROM DUAL;
you explicitly tell Oracle how to convert DATE to string. That's why you get Feb 01 2015 10:02AM.
Question 2.
Oracle DATE precision is second. Oracle DATE arithmetic uses day as unit of measure. THerefore adding 1 to a DATE means same time tomorrow. Therefore if current hour and minute is 12:01AM, current date + 1 will also have hour and minute is 12:01AM.
SY.
[Updated on: Sun, 01 February 2015 13:16] Report message to a moderator
|
|
|
|
Re: Date format issues [message #632489 is a reply to message #632488] |
Sun, 01 February 2015 20:42 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
For "minutes", could you change it from " MM" to "MI". MM is taking the format for month and always returning you "02" instead of the minute value.
|
|
|
|
|