DSXchange

Posted: **Mon Sep 29, 2014 2:09 am**

Hi all
I made a job involves timestamp comparison but it keep give me Invalid Julian day error.
the job itself is nothing too special

and i am sure the problem is from this constraint

Code: Select all

NullToValue(Trx_Dim.P_TRANSACTION_TS, "2099-21-31-00.00.00.000000") <= Trx_Dim.DATE and NullToValue(Trx_Dim.C_TRANSACTION_TS, "2099-21-31-00.00.00.000000") <= Trx_Dim.DATE and NullToValue(Trx_Dim.S_TRANSACTION_TS, "2099-21-31-00.00.00.000000") <= Trx_Dim.DATE

the TRX_DIM.DATE is a timestamp typed variable. I think the problem is from the string to timestamp conversion. if i change NullToValue(Trx_Dim.P_TRANSACTION_TS, "2099-21-31-00.00.00.000000") to CurrentTimestamp() it works. but i just can't get this statement works! I did lots of research and experiments but still can't fix the problem, any ideas?

cheers
abyss

Posted: **Mon Sep 29, 2014 2:29 am**

Your default timestamp format (which you can view/change in the Administrator client) does not include fractional seconds.

Therefore you must do one of the following things:

change your StringToTimestamp() functions to include a format string that includes six fractional seconds

change your hard coded timestamp strings so that they do not have fractional seconds

change your default timestamp format for the project

Posted: **Mon Sep 29, 2014 2:43 am**

Is it 2099-21-31 or 2099-12-31 ?

Posted: **Mon Sep 29, 2014 7:47 am**

Yah, that too.