Multi-Source Data Foundation: Date functions

[Former Member]

I have a single source unx that includes some date calculations that work fine with SQL Server 2008 in a single source data foundation because it is using SQL syntax.

In a multi source data foundation against the same data source, I am having some challenges making them work that I hope the experts might be able to help me with.

1. Difference in days between two date fields:

In SQL, I use the datediff function

In Oracle, I use the minus operator

Is there an easy equivalent for a multi source data foundation?

My thought is to convert the dates to integers and then subtract but I am not sure that is best way:

(Year(Date1)1000+dayofyear(Date1)) - (Year(Date2)1000+dayofyear(Date2))

2. I got the following to work in the multi source data foundation:

Current Week Start: decrementDays(curDate(), (dayOfWeek(curDate()) - 1))

Current Week End: incrementDays(decrementDays(curDate(),(dayofweek(curDate()) - 1)),6)

Current Month Start: decrementDays(curDate(), (dayOfMonth(curDate()) - 1))

but from there I am stuck because in database specific syntax I can add/subtract months from a date but in ANSI92 I can't seem to say "add 1 month" or "subtract 1 month".

For example, for Current Month End:

SQL I use :dateadd(ms, -3, dateadd(mm, datediff(m, 0,getdate()) + 1, 0))

DB2 I use: Current Date - (day(current date) - 1)days + 1 month - 1 day

Some other dates I am trying to calculate include:

Prior Month Start

Prior Month End

Current Quarter Start

Current Quarter End

Current Year Start

Current Year End

Any help is appreciated!

Marla

[jeff_crisp]

For a Difference between 2 Dates in Days try the following:

Timestampdiff('SQL_TSI_DAY',Datefield1,DateField2)

For Prior Month Start Date use:

toDate((timestampAdd('SQL_TSI_MONTH', -1, timestampadd('SQL_TSI_DAY',-dayofmonth(now())+1,now()))))

For Prior Month End Date use:

toDate(timestampadd('SQL_TSI_DAY',-dayofmonth(now())+1,now())

See if you can run with these and see if you can figure out your remaining dates.

[Former Member]

I'm running into the same issue with the DB2 functionality.  I was looking in the parameters to see if there was some sort of database switch we needed to enable, but am not finding any.  Can anyone in SAP help? 

I can't do the most basic date calculations for our pre-defined filters like:

CURRENT DATE

CURRENT TIMEZONE

and we should be able to.