Home » SQL & PL/SQL » SQL & PL/SQL » query to get message between two dates
query to get message between two dates [message #253986] Wed, 25 July 2007 07:43 Go to next message
glenvishal
Messages: 8
Registered: May 2005
Junior Member
hi
i have a column which has the date in the form YYYY-MM-DD HH:MM:SS . i need to get information between one time to another. how do i write a query for that
Re: query to get message between two dates [message #253992 is a reply to message #253986] Wed, 25 July 2007 07:59 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
what have you tried so far?
Re: query to get message between two dates [message #253993 is a reply to message #253986] Wed, 25 July 2007 08:00 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You column does not have the DATE stored in that form. DATES are not stored in any format. They are displayed in a format you choose. Now, unless your column is a VARCHAR2, in which case you are in for headaches that will not end until one of the following happens; 1. your death; 2. the death of the application, then it's as simple as using the BETWEEN clause.

* Never was good at grammar, so I am not sure if that sentence is grammatically correct.

[Updated on: Wed, 25 July 2007 08:01]

Report message to a moderator

Re: query to get message between two dates [message #253998 is a reply to message #253986] Wed, 25 July 2007 08:04 Go to previous messageGo to next message
glenvishal
Messages: 8
Registered: May 2005
Junior Member
hi
it is stored simply as a date in the db and am having a trouble as to what to give in the between clause
Re: query to get message between two dates [message #254000 is a reply to message #253998] Wed, 25 July 2007 08:06 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
between to_date(...) and to_date(...);


QED
Re: query to get message between two dates [message #254002 is a reply to message #253992] Wed, 25 July 2007 08:13 Go to previous messageGo to next message
glenvishal
Messages: 8
Registered: May 2005
Junior Member
hi
the qyery is as below


SELECT MESSAGE FROM FM_EVENT_LOG WHERE EVENT_TIME
between to_char('2007-07-07 22:26:32','YYYYMMDDHH24mm') and
to_char('2007-07-07 22:27:32','YYYYMMDDHH24mm')
Re: query to get message between two dates [message #254004 is a reply to message #254002] Wed, 25 July 2007 08:15 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
TO_DATE not TO_CHAR.
You cannot compare a DATE column to a string that TO_CHAR will return.
Re: query to get message between two dates [message #254363 is a reply to message #253986] Thu, 26 July 2007 10:19 Go to previous message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
also your format string is wrong.


whb@xe>select to_date('2007-07-07 22:26:32','YYYYMMDDHH24mm')  from dual;
select to_date('2007-07-07 22:26:32','YYYYMMDDHH24mm')  from dual
                                     *
ERROR at line 1:
ORA-01810: format code appears twice


  1* select to_date('2007-07-07 22:26:32','YYYY-MM-DD HH24:mi:ss')  from dual
whb@xe>/

TO_DATE('
---------
07-JUL-07

[Updated on: Thu, 26 July 2007 10:20]

Report message to a moderator

Previous Topic: Please help on merge satatement
Next Topic: How to capture the results of a dynamic SQL
Goto Forum:
  


Current Time: Sun Dec 04 10:29:06 CST 2016

Total time taken to generate the page: 0.06734 seconds