OraFAQ Forum: SQL & PL/SQL » Records between current date and 2 weeks from the current date?

Home » SQL & PL/SQL » SQL & PL/SQL » Records between current date and 2 weeks from the current date?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Records between current date and 2 weeks from the current date? [message #378348]

Tue, 30 December 2008 02:09

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

Frank Naude
Messages: 4581
Registered: April 1998

Senior Member

Use something like BETWEEN 0 AND 14.

Report message to a moderator

Re: difference between two dates with condition [message #378351 is a reply to message #378349]

Tue, 30 December 2008 02:19

msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR

Member

ThankQ

Report message to a moderator

Re: difference between two dates with condition [message #378446 is a reply to message #378348]

Tue, 30 December 2008 08:12

joy_division
Messages: 4963
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

Report message to a moderator

Re: difference between two dates with condition [message #378494 is a reply to message #378446]

Tue, 30 December 2008 14:27

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It depends on your default date format.

Regards
Michel

Report message to a moderator

Re: difference between two dates with condition [message #378504 is a reply to message #378348]

Tue, 30 December 2008 15:13

flyboy
Messages: 1903
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.

Report message to a moderator

Re: difference between two dates with condition [message #378509 is a reply to message #378494]

Tue, 30 December 2008 15:24

joy_division
Messages: 4963
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.

Report message to a moderator

Re: difference between two dates with condition [message #378517 is a reply to message #378504]

Tue, 30 December 2008 15:54

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

Report message to a moderator

Re: difference between two dates with condition [message #378597 is a reply to message #378517]

Wed, 31 December 2008 01:16

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And then?
What is the purpose of your post?

Regards
Michel

Report message to a moderator

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

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

msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR

Member

sorry ignore this post

Report message to a moderator

Re: current date with 2 weeks from other date ? [message #378623 is a reply to message #378621]

Wed, 31 December 2008 02:18

Frank Naude
Messages: 4581
Registered: April 1998

Senior Member

Can you please post your solution?

Report message to a moderator

Re: current date with 2 weeks from other date ? [message #378626 is a reply to message #378623]

Wed, 31 December 2008 02:27

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.

Report message to a moderator

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

trivendra
Messages: 211
Registered: October 2007
Location: Phoenix

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

Report message to a moderator

Re: difference between two dates with condition [message #378657 is a reply to message #378597]

Wed, 31 December 2008 04:26

flyboy
Messages: 1903
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.

Report message to a moderator

Re: difference between two dates with condition [message #378700 is a reply to message #378657]

Wed, 31 December 2008 08:52

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Oh, that's this. A little comment in her post should be welcome.

Regards
Michel

Report message to a moderator

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

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)

Report message to a moderator

Previous Topic:	Creating Trigger with Dynamic sql when ever table structure changes
Next Topic:	Stored procedure results into a temp table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 04:56:16 CST 2024