Date Format Problem

[dmaki]

Hi,

My report requires two date input parameters, begin and end date. I am using synchronization to establish a relationship between two data providers. The problem I am having is getting both date inputs to work on the two querys. Is there a way to format the date and use it for both query's as a condition? Thanks in advance for the help and sorry if this is a simple question.

 

[blom0344]

What database(s) are we talking here?

Most databases have their own date formatting functions you may be able to use

Examples:

ORACLE: TO_CHAR(date,'MM/DD/YYYY')

db2: CHAR(date,ISO) --> YYYY-MM-DD
CHAR(date,USA) --> MM/DD/YYYY

Apply the formatting on universe object (or create an additional one)



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

 

[nassers]

Hi,

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 have a temporary table and want to save these days and time in this table,
my results are varying, any help in this is appreciated.

thanks,

 

[blom0344]

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