Calculate relative dates in Oracle

Question

Post reply

Calculate relative dates in Oracle

Jason Coleman

SSCrazy

Points: 2187
More actions
June 16, 2009 at 2:30 am

#206553

Hi,
I'm doing some work in Oracle on date ranges, and have a calendar table with Activity_date. I'd like to calculate the "relative week no." from this, but I'm not sure of the functions to use...
In MS SQL it was just easy to use datediff, but there is not such function in Oracle, as the dates are stored as numbers....
Anyone point me in the right direction
Thx..

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

Jason Coleman (6/16/2009)
as the dates are stored as numbers....
Anyone point me in the right direction
Thx..

The dates should be stored as the Date datatype. What is the data type of the column the dates are stored in?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason Coleman SSCrazy Points: 2187 More actions · Answer 2

Right... finally started thinking straight...

select

activity_date,

to_char(activity_date,'IW')-to_char(sysdate,'IW') as Date_diff

from

dw.calendar

and then hey presto! :w00t:

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

Jason Coleman (6/17/2009)
Right... finally started thinking straight...
select
activity_date,
to_char(activity_date,'IW')-to_char(sysdate,'IW') as Date_diff
from
dw.calendar
and then hey presto! :w00t:

Outstanding. Thanks for the feedback on this, Jason.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)