| to compare dates in oracle [message #263892] |
Fri, 31 August 2007 04:12  |
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 #263911 is a reply to message #263907] |
Fri, 31 August 2007 04:57  |
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 #264147 is a reply to message #264118] |
Sat, 01 September 2007 02:36 |
 |
Maaher
Messages: 7065
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  .
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 |
joy_division
Messages: 4963
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
