Home » SQL & PL/SQL » SQL & PL/SQL » Date format issues (12g)
Date format issues [message #632483] Sun, 01 February 2015 12:57 Go to next message
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 #632484 is a reply to message #632483] Sun, 01 February 2015 13:14 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
consider the results below. What did you learn from it?

SQL> @sysdate.sql
SQL> DECLARE
  2  BEGIN
  3  dbms_output.enable(10000);
  4  
  5  dbms_output.put_line(to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
  6  DBMS_LOCK.SLEEP(3);
  7  dbms_output.put_line(to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
  8  DBMS_LOCK.SLEEP(3);
  9  dbms_output.put_line(to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') || '<-->' ||
 10  to_char(sysdate+1,'YYYY-MM-DD HH24:MI:SS') || '===>' ||
 11  to_char(TRUNC(sysdate+1),'YYYY-MM-DD HH24:MI:SS')
 12  );
 13  end;
 14  /
2015-02-01 11:13:38
2015-02-01 11:13:41
2015-02-01 11:13:44<-->2015-02-02 11:13:44===>2015-02-02 00:00:00

PL/SQL procedure successfully completed.
Re: Date format issues [message #632485 is a reply to message #632483] Sun, 01 February 2015 13:15 Go to previous messageGo to next message
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 #632488 is a reply to message #632485] Sun, 01 February 2015 20:26 Go to previous messageGo to next message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member
Thanks for your response. Let me explain the issues clearly, sorry for not being clear.


Issue 1

I want the output display as Mon DD YYYY HH:MMAM format.

But below query wasn't returning the correct sys time ( actual sys time was 01-Feb-2015 10:10:26. but below query returns Feb 01 2015 10:02AM , 6 mints behind the current time, on every execute i get the same time.

SELECT TO_CHAR(SYSDATE,'Mon DD YYYY HH:MMAM') FROM DUAL;

Where as i see correct Hours and mints in with below

select sysdate from dual;


Issue 2


I gave input as 'Feb-01-2015'.

V_a= 'Feb-01-2015';

v_Z := V_a + 1;


When i executed the query, system time was Feb-01-2015 10:10

i have expected below query would have return as Feb 02 2015 12:00AM

DBMS_OUTPUT.PUT_LINE('A Day later ' || TO_CHAR (v_Z, 'Mon DD YYYY HH:MMAM'));

but it returned below

output : A Day later Feb 02 2015 12:01AM.


Correct me if i am missing anything here. Appreciate your inputs.
Re: Date format issues [message #632489 is a reply to message #632488] Sun, 01 February 2015 20:42 Go to previous messageGo to next message
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.
Re: Date format issues [message #632490 is a reply to message #632488] Sun, 01 February 2015 20:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>SELECT TO_CHAR(SYSDATE,'Mon DD YYYY HH:MMAM') FROM DUAL;
>Where as i see correct Hours and mints in with below

trivia questions for you below

What is Format mask abbreviation for MINUTE?
What is Format mask abbreviation for MONTH?

BTW - You got PRECISELY what you requested.
Re: Date format issues [message #632531 is a reply to message #632490] Mon, 02 February 2015 10:57 Go to previous message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member
Thanks Lalit and BS for correcting me.
Previous Topic: Quicksort - problem with null values
Next Topic: A hierarchical query problem
Goto Forum:
  


Current Time: Thu Mar 28 16:04:08 CDT 2024