Home » SQL & PL/SQL » SQL & PL/SQL » QUERY SHOULD RETURN 1 HOUR (ORACLE VERSION 8I)
QUERY SHOULD RETURN 1 HOUR [message #361832] Fri, 28 November 2008 03:36 Go to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

DEAR

SELECT ( TO_DATE ('26-NOV-2009 01:00:00 AM', 'DD-MON=YYYY HH:MI:SS AM')
- TO_DATE ('25-NOV-2009 12:00:00 AM', 'DD-MON=YYYY HH:MI:SS AM')
)
* 24
FROM DUAL

I THINK ABOVE QUERY SHOULD 1 HOUR WHILE IT IS RETURNING 13.
WHAT MAY PROBLEM??? PLEASE HELP.
Re: QUERY SHOULD RETURN 1 HOUR [message #361833 is a reply to message #361832] Fri, 28 November 2008 03:43 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

THANKS ALL VIEWERS.

IT IS OK.

REGARDS.
Re: QUERY SHOULD RETURN 1 HOUR [message #361835 is a reply to message #361832] Fri, 28 November 2008 03:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your query returns 25 hours when I run it.
12:00 AM is midnight
12:00 PM is midday
Re: QUERY SHOULD RETURN 1 HOUR [message #361841 is a reply to message #361832] Fri, 28 November 2008 04:19 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

DEAR CHECK IT

SELECT ( TO_DATE ('26-NOV-2009 01:00:00 AM', 'DD-MON=YYYY HH:MI:SS AM')
- TO_DATE ('26-NOV-2009 12:00:00 AM', 'DD-MON=YYYY HH:MI:SS AM')
)
* 24
FROM DUAL
Re: QUERY SHOULD RETURN 1 HOUR [message #361843 is a reply to message #361835] Fri, 28 November 2008 04:25 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Huh, I expected an error because of

TO_DATE('26-NOV-2009 01:00:00 AM', 'DD-MON=YYYY HH:MI:SS AM')

but - everything seems to be fine. Didn't know that!
Re: QUERY SHOULD RETURN 1 HOUR [message #361845 is a reply to message #361843] Fri, 28 November 2008 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Non-alphabetic characters in TO_DATE format element are just place holders.
SQL> select to_date('13-NOV-1956','DD/MON/YYYY') d1,
  2         to_date('13-NOV-1956','DD.MON.YYYY') d2,
  3         to_date('13.NOV/1956','DD%MON#YYYY') d3
  4  from dual;
D1          D2          D3
----------- ----------- -----------
13-NOV-1956 13-NOV-1956 13-NOV-1956

1 row selected.

More they are optional ones:
SQL> select to_date('13NOV1956','DD/MON/YYYY') d1 from dual;
D1
-----------
13-NOV-1956

1 row selected.

And even more Oracle is smart enough to understand month name when using month number:
SQL> select to_date('13NOV1956','DD/MM/YYYY') d1 from dual;
D1
-----------
13-NOV-1956

1 row selected.

Regards
Michel

Re: QUERY SHOULD RETURN 1 HOUR [message #361854 is a reply to message #361845] Fri, 28 November 2008 04:56 Go to previous message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Nice examples! Thank you!
Previous Topic: Help urgent....
Next Topic: Concatenation of nulls not happening.
Goto Forum:
  


Current Time: Tue Dec 06 11:50:55 CST 2016

Total time taken to generate the page: 0.11894 seconds