Multi source data foundation date functions

[former_member583901]

Hi all,

I was working with single source data foundation with following sql statements:

select dateadd(second, "RequestCharges"."EXECUTEDTIME" /1000 + 1*60*60, '19700101')

select DATEDIFF(MINUTE, '19700101', dateadd(second, "RequestCharges"."MM2COMPLETEREQUEST" /1000, '19700101'))

select year(dateadd(second, "RequestCharges"."EXECUTEDTIME" /1000 + 1*60*60, '19700101'))

I have on my sql database date fields with type bigint, and with previous functions I'm converting this values into real dates.

Now I need to convert this data foundation into multi source, and with that change all previous sql statement are no longer working.

I was trying to use some idt available functions like "timestampAdd" and "incrementDays" but without any success.

Do you have any idea how I can achieve the same functionality on multi source data foundation?

Many thanks,

Vitor Ramalho

[Joe_Peters · Accepted solution]

For the first one:

timestampadd('sql_tsi_second',"RequestCharges"."EXECUTEDTIME" / 1000,totimestamp('1970-01-01 01:00:00'))

The second one is just the number of minutes since the epoch, so:

"RequestCharges"."MM2COMPLETEREQUEST" / 60000

and finally:

year(timestampadd('sql_tsi_second',"RequestCharges"."EXECUTEDTIME" / 1000,totimestamp('1970-01-01 01:00:00')))

The date functions are somewhat limited. In a worst-case scenario, you can add a calculated column to a table, which can use the database's native SQL syntax.

[former_member193452 · Accepted solution]

Yes, and in the calculated column you may need to select choose database specific sql.

Regards,

Jacqueline