Home » SQL & PL/SQL » SQL & PL/SQL » to compare dates in oracle
to compare dates in oracle [message #263892] Fri, 31 August 2007 04:12 Go to next message
pravin9
Messages: 42
Registered: May 2007
Member
Hi,
My date format in database is mm/dd/yyyy hh24:mi:ss
now i need to pass a parameter with only month and year and it should compare with the date values in the database
can anyone explain me with the query how it looks

I only pass 092006 here 09 refers to month and 2006 to year

Thanks,
Praveen
Re: to compare dates in oracle [message #263902 is a reply to message #263892] Fri, 31 August 2007 04:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
My date format in database is mm/dd/yyyy hh24:mi:ss

No. Date has no format. Date representation has a format but it is no more a date it is a string.

Have a look at TO_DATE and TO_CHAR functions.

Regards
Michel
icon1.gif  to compare dates in oracle [message #263907 is a reply to message #263892] Fri, 31 August 2007 04:37 Go to previous messageGo to next message
dhanooj
Messages: 1
Registered: August 2007
Location: kochi
Junior Member


hi,


you can use like this,

SQL>select to_char(sysdate,'MMYYYY') from dual;

SQL> /

TO_CHAR(SYSDATE,'MMYYYY')
-------------------------
082007
Re: to compare dates in oracle [message #263911 is a reply to message #263907] Fri, 31 August 2007 04:57 Go to previous messageGo to next message
pravin9
Messages: 42
Registered: May 2007
Member
Hi
i get that value but how do i compare it with the values i pass dynamically
I want it in such a way that it should compare with in the dates i pass dynamically the format i pass is 'mmyyyy'

Regards,
Praveen
Re: to compare dates in oracle [message #263914 is a reply to message #263911] Fri, 31 August 2007 05:01 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Till what extent you have written your solution?
Post it here.

Have you checked to_date?

By
Vamsi
Re: to compare dates in oracle [message #263943 is a reply to message #263892] Fri, 31 August 2007 05:49 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
hi
sytnax:
select * from <tab name> where to_char(<col>,'mmyyyy')=
'<value in mmyyyy format>';
ex:

SQL> select * from t1 where to_char(hiredate,'mmyyyy')
= '092007'

Regards
NATESH
Re: to compare dates in oracle [message #264118 is a reply to message #263892] Fri, 31 August 2007 22:59 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
select * from yourtable
where
date_val between 
to_char(to_date('092006','mmyyyy'),'dd/mon/yyyy hh24:mi:ss')
and 
to_char(last_day(to_date('092006','mmyyyy')),'dd/mon/yyyy hh24:mi:ss')

[Updated on: Sat, 01 September 2007 02:47]

Report message to a moderator

Re: to compare dates in oracle [message #264146 is a reply to message #264118] Sat, 01 September 2007 02:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This is so wrong.. Compare dates to string, ouch!
(wow, I beat joy_division!)
Re: to compare dates in oracle [message #264147 is a reply to message #264118] Sat, 01 September 2007 02:36 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
muzahidul islam wrote on Sat, 01 September 2007 05:59
select * from yourtable
where
date_val between
to_char(to_date('092006','mmyyyy'),'dd/mon/yyyy hh24:mi:ss')
and
to_char(last_day(to_date('092006','mmyyyy')),'dd/mon/yyyy hh24:mi:ss')

muzahid,
this goes for you too:
Quote:
How to format your post?

If you are providing code snippets such as SQL*Plus copy/paste, please be sure to use formatting tags:
[code] Enter your code here.[/code]
It makes a huge difference to the forum readers. Unreadable code makes a question harder to understand and will delay answers. A lot of people don't spend that much time on the forum and unclear posts will be skipped more often because they take a lot more time to decipher.

The difference between the code blocks below is clear:

First a block without proper tags, you'll notice that it is not that readable:

DECLARE
CURSOR yourcursor
IS
SELECT yourcolumn
, another_column
FROM yourtable
WHERE some_column BETWEEN A AND B
AND some_other > SYSDATE;
BEGIN
FOR a_record IN yourcursor
LOOP
do_something_here;
IF a_record.another_column = 1
THEN
do_extra_stuff;
ELSE
do_other_stuff;
ELSE;
END LOOP;
END;


Now, the same PL/SQL block – this time with use of the code tags. It clearly delineates the code from the other text and preserves text indenting.
 DECLARE
  CURSOR yourcursor
      IS
  SELECT yourcolumn
       , another_column
    FROM yourtable
   WHERE some_column BETWEEN A AND B
     AND some_other  > SYSDATE;
BEGIN
  FOR a_record IN yourcursor
  LOOP
    do_something_here;
    IF a_record.another_column  = 1
    THEN
      do_extra_stuff;
    ELSE
      do_other_stuff;
    ELSE;
  END LOOP;
END; 

You can also paste the code in your message, select it and hit this button http://www.orafaq.com/forum/theme/orafaq/images/b_code.gif.

What if the code is not formatted itself? Code tags will do little about that. So you first have to format the code
  • by hand
  • via an external tool (like TOAD)
  • via the SQL Formatter page of OraFAQ.
Make sure that lines of code do not exceed 80 characters.

Scott Mackey has created a nice little document explaining in detail and with screencaps how to format. The document can be downloaded/viewed here.


Use code tags.

MHE
Re: to compare dates in oracle [message #264758 is a reply to message #264146] Tue, 04 September 2007 08:35 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Frank wrote on Sat, 01 September 2007 03:08
This is so wrong.. Compare dates to string, ouch!
(wow, I beat joy_division!)


Joy_division does not do work on weekends. Even though answering the forums is technically not really work, it takes away from downtime.
Previous Topic: Using WHENEVER ERROR in login.sql
Next Topic: Row getting deleted from table after insertion
Goto Forum:
  


Current Time: Sat Dec 03 07:59:16 CST 2016

Total time taken to generate the page: 0.13239 seconds