Home » SQL & PL/SQL » SQL & PL/SQL » Date manipulation
Date manipulation [message #292704] Wed, 09 January 2008 12:52 Go to next message
barryman9000
Messages: 7
Registered: October 2007
Junior Member
I'm trying to get a date: "Next month, minus one day..." Here's what I have, but it's returning "Next month, minus 4 days." I'm not sure why.

SELECT ADD_MONTHS(TO_DATE(TRUNC(SYSDATE, 'DAY') -1),1) AS Expires FROM DUAL;


Oddly (although I'm sure it makes perfect sense) this query returns the value for Next month, minus 3 days.:

SELECT ADD_MONTHS(TO_DATE(TRUNC(SYSDATE -1, 'DAY')),1) AS Expires FROM DUAL;


I must not have a clear understanding of the TRUNC() function. Any suggestions?
Re: Date manipulation [message #292705 is a reply to message #292704] Wed, 09 January 2008 12:58 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You can use
SELECT ADD_MONTHS(trunc(sysdate),1) - 1 AS Expires FROM DUAL;
TRUNC on DAY will give the first day of that week.
To trunc the time you should use DD instead of DAY. (or just use trunc)
Have a look at this.

By
Vamsi

[Updated on: Wed, 09 January 2008 13:01]

Report message to a moderator

Re: Date manipulation [message #292706 is a reply to message #292704] Wed, 09 January 2008 12:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64138
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This:
TO_DATE(TRUNC(SYSDATE, 'DAY')-1)
is meaningless!
Why?
Because "TRUNC(SYSDATE, 'DAY')-1" is already a date!

Regards
Michel
Re: Date manipulation [message #292708 is a reply to message #292704] Wed, 09 January 2008 13:01 Go to previous messageGo to next message
barryman9000
Messages: 7
Registered: October 2007
Junior Member
Oh, thanks. That query does make sense then!

Here's what I ended up using, which I got mostly by guessing.

SELECT ADD_MONTHS(TO_DATE(SYSDATE -1),1) AS Expires FROM DUAL;
Re: Date manipulation [message #292715 is a reply to message #292708] Wed, 09 January 2008 13:26 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Again TO_DATE against a date; didn't Michel tell you that this is meaningless?

Why not something like this:

select add_months(trunc(sysdate), 1) - 1 from dual

Read it as: remove time component from today, add one month to it and subtract one day.

P.S.
Quote:

... which I got mostly by guessing

There's really no need to guess anything - take some time and educate yourself about SQL functions (date functions are there as well) and you won't have to guess. Leave that to fortune tellers.

[Updated on: Wed, 09 January 2008 13:29]

Report message to a moderator

Re: Date manipulation [message #292716 is a reply to message #292708] Wed, 09 January 2008 13:26 Go to previous messageGo to next message
mad_bu
Messages: 8
Registered: August 2006
Junior Member
Hi,

you can use this query also to get the same result

select trunc(add_months(sysdate,1) -1) AS Expires from dual.


--Mad bu
Re: Date manipulation [message #292724 is a reply to message #292715] Wed, 09 January 2008 14:11 Go to previous messageGo to next message
barryman9000
Messages: 7
Registered: October 2007
Junior Member
Wow Littlefoot, someone in a bad mood? I came here looking for some help understanding a certain function... aka education. I had to guess because I wasn't having any luck finding the answer online, and no one had responded to my post yet.

Thanks to everyone else who didn't give me sh*t for asking for help!
Re: Date manipulation [message #292726 is a reply to message #292724] Wed, 09 January 2008 14:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64138
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You missed what LittleFoot meant.
It was education.
SQL is not a matter of guess, it is study.
You have to read and study the page of each function you want to use.
What are the parameter types, what are the allowed values, what is the returned type and value and so on.
Not just pick up the name and trying to guess what it does and how.

Regards
Michel
Re: Date manipulation [message #292824 is a reply to message #292724] Thu, 10 January 2008 00:03 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Next month minus one day equals last_day
Re: Date manipulation [message #292830 is a reply to message #292824] Thu, 10 January 2008 00:18 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Frank,
I feel you have misread the OP.
I think barryman9000 wants to add a month to yesterday's date, not the last date of this month.
barryman9000 has done a mistake by passing date to to_date and truncating by DAY.

By
Vamsi
Re: Date manipulation [message #292858 is a reply to message #292830] Thu, 10 January 2008 01:24 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Thanks for correcting me. Indeed I misinterpreted the problem.
Previous Topic: function is said to be return only one value but ....?
Next Topic: User Errors
Goto Forum:
  


Current Time: Thu Dec 08 06:34:37 CST 2016

Total time taken to generate the page: 0.16980 seconds