Home » SQL & PL/SQL » SQL & PL/SQL » Records between current date and 2 weeks from the current date?
Records between current date and 2 weeks from the current date? [message #378348] Tue, 30 December 2008 02:09 Go to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
hi,
i am trying to get data with some condition like
"the difference of two dates <= 14"



select * from t_dds_contact where TRUNC(TO_DATE(expected_first_commission_d,'DD-MM-YYYY') - TO_DATE(sysdate,'DD-MM-YYYY')) <= 14;



I want the data which is less that or equal to 14, where I am getting all values like negative values as well. Any suggestions...


ThankQ

[MERGED by LF]

[Updated on: Wed, 31 December 2008 02:40] by Moderator

Report message to a moderator

Re: difference between two dates with condition [message #378349 is a reply to message #378348] Tue, 30 December 2008 02:12 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Use something like BETWEEN 0 AND 14.
Re: difference between two dates with condition [message #378351 is a reply to message #378349] Tue, 30 December 2008 02:19 Go to previous messageGo to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
ThankQ Surprised

Re: difference between two dates with condition [message #378446 is a reply to message #378348] Tue, 30 December 2008 08:12 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
msg2ajay wrote on Tue, 30 December 2008 03:09

TO_DATE(sysdate,'DD-MM-YYYY')




This is invalid. SYSDATE is a DATE. Trying to convert it to a date, only bad things can happen.
FOO SCOTT>select to_date(sysdate,'dd-mon-yyyy') from dual;
select to_date(sysdate,'dd-mon-yyyy') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month
Re: difference between two dates with condition [message #378494 is a reply to message #378446] Tue, 30 December 2008 14:27 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Trying to convert it to a date, only bad things can happen- Agree.
But I didn't get any error!
hr@ORAFAQ.US.ORACLE.COM> select to_date(sysdate,'dd-mon-yyyy') from dual;

TO_DATE(S
---------
31-DEC-08

Elapsed: 00:00:00.00
hr@ORAFAQ.US.ORACLE.COM> select * from v$version
  2  ;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

Elapsed: 00:00:00.01
hr@ORAFAQ.US.ORACLE.COM> 

[Updated on: Tue, 30 December 2008 15:25] by Moderator

Report message to a moderator

Re: difference between two dates with condition [message #378495 is a reply to message #378494] Tue, 30 December 2008 14:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on your default date format.

Regards
Michel
Re: difference between two dates with condition [message #378504 is a reply to message #378348] Tue, 30 December 2008 15:13 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Just to append to Michel's reply: if it does not fail, you get the result date truncated to smallest time piece in the used date format (day in your case). It is the side-effect of executed implicit conversion. It may be fine if you count with it; but I would rather use TRUNC function and keep the code understandable at the first sight.
Re: difference between two dates with condition [message #378509 is a reply to message #378494] Tue, 30 December 2008 15:24 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Olivia wrote on Tue, 30 December 2008 15:27
Trying to convert it to a date, only bad things can happen- Agree.
But I didn't get any error!



This falls into the famous proverb (I say "proverb" because I don't know that correct word. Axiom maybe?)

Showing that it works 1000 times does not prove that it is valid. Showing that it fails just once proves that it is invalid.

Re: difference between two dates with condition [message #378517 is a reply to message #378504] Tue, 30 December 2008 15:54 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
hr@ORAFAQ.US.ORACLE.COM> select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD-
--------------------
31-dec-2008 03:21:05

Elapsed: 00:00:00.00
hr@ORAFAQ.US.ORACLE.COM>

  1* select to_char(to_date(sysdate,'dd-mon-yyyy hh24:mi:ss'),'dd-mon-yyyy hh24:mi:ss') from dual
hr@ORAFAQ.US.ORACLE.COM> /

TO_CHAR(TO_DATE(SYSD
--------------------
31-dec-0008 00:00:00

Elapsed: 00:00:00.00


Thanks
Re: difference between two dates with condition [message #378597 is a reply to message #378517] Wed, 31 December 2008 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And then?
What is the purpose of your post?

Regards
Michel
Records between current date and 2 weeks from the current date? [message #378618 is a reply to message #378348] Wed, 31 December 2008 02:09 Go to previous messageGo to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
i am trying to get the records which are between present date and 2 weeks from present date.

I tried like below but i am not getting all the records.

SELECT * FROM dt_dwrs  where to_char(p_exp_end_d,'DD-MM-YYYY') in (SELECT TO_CHAR(SYSDATE+(14),'DD-MM-YYYY') FROM dual);


can any one help me regard this.

ThankQ


[A duplicate message deleted by LF]

[Updated on: Wed, 31 December 2008 02:38] by Moderator

Report message to a moderator

Re: current date with 2 weeks from other date ? [message #378621 is a reply to message #378618] Wed, 31 December 2008 02:15 Go to previous messageGo to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
sorry ignore this post
Re: current date with 2 weeks from other date ? [message #378623 is a reply to message #378621] Wed, 31 December 2008 02:18 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Can you please post your solution?
Re: current date with 2 weeks from other date ? [message #378626 is a reply to message #378623] Wed, 31 December 2008 02:27 Go to previous messageGo to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
sorry I don't mean I got solution. Just now I have seen two posts from me with same question so I said to ignore my post.


I still need solution ... any one ?

ThankQ.
Re: Records between current date and 2 weeks from the current date? [message #378628 is a reply to message #378618] Wed, 31 December 2008 02:37 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
If it is like this then

Quote:
msg2ajay

i am trying to get the records which are between present date and 2 weeks from present date.



you can do it like this.

SELECT *
  FROM DT_DWRS
 WHERE P_EXP_END_D BETWEEN SYSDATE AND (SYSDATE + 14)


Thanks
Trivendra
Re: difference between two dates with condition [message #378657 is a reply to message #378597] Wed, 31 December 2008 04:26 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Michel Cadot wrote on Wed, 31 December 2008 08:16
And then?
What is the purpose of your post?

In my opinion, Olivia just demonstrates that the truncating may happen, as I mentioned. And that I was not correct, as the date is truncated to smallest time piece in NLS_DATE_FORMAT (not the used format). Of course, only if the TO_DATE function does not fail.
Re: difference between two dates with condition [message #378700 is a reply to message #378657] Wed, 31 December 2008 08:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oh, that's this. A little comment in her post should be welcome.

Regards
Michel
Re: Records between current date and 2 weeks from the current date? [message #379951 is a reply to message #378628] Thu, 08 January 2009 10:28 Go to previous message
aparna_shiva
Messages: 1
Registered: January 2009
Junior Member
This would be more accurate.

SELECT *
FROM DT_DWRS
WHERE trunc(P_EXP_END_D) BETWEEN trunc(SYSDATE) AND (trunc(SYSDATE) + 14)
Previous Topic: Creating Trigger with Dynamic sql when ever table structure changes
Next Topic: Stored procedure results into a temp table
Goto Forum:
  


Current Time: Wed Dec 07 14:42:39 CST 2016

Total time taken to generate the page: 0.09266 seconds