date time between two dates 1

[nassers]

I am kind of confused. I have two date fields. I have to trap each day and hours that go with this, eg. (date format is dd-mon-yyyy hh24:mi)If start_date is 03-09-2004 04:00 and end_date is 03-11-2004 :06:00 then my days should read like this:
day1 - 03-09-2004 20 hours
day2 - 03-10-2004 24 hours
day3 - 03-11-2004 6 hours

I want to trap these date and time in a separate temporary table. My program is giving differnet results, esp when time is > 12 hours.
Any help is appreciated.

thanks,

 

[blom0344]

As answered in the other thread:

This is not a formatting issue.
What you need is a calenderfile with daily entries and use that against your facttable with a theta-join:

Select calender.day,facttable.startdate,facttable.enddate from calender,facttable where calender.day between
to_char(startdate,'mm-dd-yyyy') and to_char(enddate,'mm-dd-yyyy')

This is an example on ORACLE.

In your example this will fetch 3 days from the calenderfile, if the calenderday > startdate and < enddate you can grant it 24 hours.

If calenderday is the same as the startdate you will need to perform a calculation to extract the hours, something like:

trunc(((86400*(date2-date1))/60)/60)- 24*(trunc((((86400*(date2-date1))/60)/60)/24))

These are all hints to a possible solution, you will have to tweak them here and there and decide where you will perform the calcs (database,universe,reporter)


T. Blom
Information analyst
tbl@shimano-eu.com

 

[nassers]

Hi Blom, thanks,
Great idea.

I shall try and modify my query,

thanks for again,
nasser