Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calculate relative dates in Oracle Expand / Collapse
Author
Message
Posted Tuesday, June 16, 2009 2:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:24 AM
Points: 91, Visits: 189
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..
Post #735504
Posted Tuesday, June 16, 2009 8:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:32 AM
Points: 37,104, Visits: 31,658
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #736156
Posted Wednesday, June 17, 2009 3:11 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:24 AM
Points: 91, Visits: 189
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!
Post #736307
Posted Wednesday, June 17, 2009 8:48 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:32 AM
Points: 37,104, Visits: 31,658
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!


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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #736649
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse