how to get the same day in the previous year for current sysdate?

[daviddmw]

would appreciate if you can delight me with some experience you had. what i need is to identify the same day in the previous year for current sysdate. i am not sure if you can provide a sample SQL or a name of Oracle function that perform this query.

Thanks alot!

David

 

[daviddmw]

the following SQL seems returning the expected result... do you have some other ways? Thanks.

select to_char(sysdate - round(365/7,0)*7,'YYYY-MM-DD HH24:MI:SS Day') from dual;

 

[SunDog]

add_months(SYSDATE,-12)

The only catch that comes to mind is if SYSDATE is 29-Feb (leap year). Then the function returns 28-Feb.

 

[daviddmw]

thanks, Sundog. I thought of add_months but it does not return the same day. for example, today is Sep 27, 2007 (Thursday), the return of add_months(sysdate, -12) is Sep 27, 2006 (Wednesday). The correct result should be Sep 28, 2006 (Thursday).

 

[rbrooker]

select next_day(add_months(SYSDATE,-12)-1, to_char(sysdate, 'Day') ) from dual

 

[bit2]

Originally posted by daviddmw:
I thought of add_months but it does not return the same day.

The problem here was not with Sundog's solution, but the "specification" of the task. How do you define "same day"? Do you want to get the same day of the same week in the previous year (at least your answer suggests that)?

 

[Gustavo]

@bit2 and SunDog - I agree

@rbrooker - at a glance, this shall not work if Sysdate is a Sunday (or your database's 1t weekday) in a year preceded by a leap year.

 

[Stew Stryker]

Probably too late to help, and not a solution anyways, but I'm thinking that daviddmw is looking for something like:

Today's the 16th Tuesday of the year. What was last year's 16th Tuesday?

Unfortunately, I can't figure out a to_date format that would give it.

 

[B]

Hi all,

it's my old fashion but customizable and correct solution.

SELECT TO_DATE(TO_CHAR(SYSDATE, 'dd') || '.' || TO_CHAR(SYSDATE, 'mm') || '.' ||
to_char(TO_NUMBER(TO_CHAR(SYSDATE, 'yyyy')) - 1),
'dd.mm.yyyy')
FROM DUAL;

 

[Stew Stryker]

Sorry Bulent but that'll give the same dd/mm for the previous year, not the same day of the week as this year. An example of what he's going for is to calculate the first Monday in October for a given set of years.

 

[rbrooker]

first monday in october for a given set of years...

select to_char(next_day(last_day(to_date('01-SEP-' || (2008 - level), 'DD-MON-YYYY')), 'Monday'), 'DD-MON-YYYY- Day')
from dual
connect by level < 10

 

[rbrooker]

second monday in october ...

select to_char(next_day(last_day(to_date('01-SEP-' || (2008 - level), 'DD-MON-YYYY')) + 7, 'Monday'), 'DD-MON-YYYY- Day')
from dual
connect by level < 10

 

[daviddmw]

Sorry guys for the confusion of my question, and for not joining the discussion in the past few days as I was away for a vacation .

Stew is right. I was looking for the same day of the week of previous year as this year.

Really appreciate all for your help and some ideas are pretty cool! But so far, it seems that only the following SQL works fine.

select to_char(sysdate - round(365/7,0)*7,'YYYY-MM-DD HH24:MI:SS Day') from dual;

Thanks!