Different Text() value for SQL-sourced dates in load script vs chart

awwitas — Tue, 03 May 2022 16:04:02 GMT

After spending the morning identifying the source of a date issue, I have found the following.

It seems that the <date> field being loaded from a SQL query has a different text value when evaluated in the load script versus when assigned to a variable versus when evaluated in a table/chart. Can someone explain what's happening?

Using this load script:

LIB CONNECT TO 'EDW Dev SSO'; Test: Load DateDT ,Num(DateDT) as NumDateDT ,Text(DateDT) as TextDateDT ; sql select cast(getdate() as date) as DateDT ; let vTextDateDT = Text(peek('DateDT',0,'Test')); Test2: Load Text(DateDT) as TextDateDT2 Resident Test;

I get this data back:

<TextDateDT> is 'Text(DateDT)' evaluated in a preceding load and returns 44684

<Text(DateDT)> is 'Text(DateDT)' evaluated in the table and returns '2022-05-03'

<VarTextDateDT> is 'Text(DateDT)' evaluated using peek() in the load script to get the value of <DateDT> from the table and returns '2022-05-03'

<TextDateDT2> is 'Text(DateDT)' evaluated in a resident load and returns 44684

Is the dual value itself changing, or does the function operate differently in chart vs load? I can't find documentation either way.

If it's relevant, I run into exactly the same issue if using timestamps instead of dates.

Thanks

topic Different Text() value for SQL-sourced dates in load script vs chart in Connectivity & Data Prep

Different Text() value for SQL-sourced dates in load script vs chart