Date# function weirdness during load

[Lauri]

I'm getting different results from the same load. The only difference is the use of "First 100000" before "Load."

My load script:

ins:
first 100000
LOAD
InsCardEffectiveDate,
date(floor(date#(InsCardEffectiveDate, 'M/D/YYYY hh:mm:ss tt'))) as PlanStartDate
From [file.qvd] (qvd);

The result:

It gets weird when I remove the "first 100000" and run the load:

The source file has 96391 rows, so the "first 100000" shouldn't have had any impact! Any idea why this is happening?

[JosephMorales]

Hi @Lauri 

I would believe that the error is in the DATE #, the format 'D / M / YYYY hh: mm: ss tt' is not correct for DATE #, it should be 'D / M / YYYY'.

On the other hand, is the InsCardEffectiveDate field a text string? In case of being a text string use timestamp #, otherwise use timestamp, and the format 'D / M / YYYY hh: mm: ss tt',

date(floor(timestamp#(InsCardEffectiveDate, 'M/D/YYYY hh:mm:ss tt'))) as PlanStartDate

or

date(floor(timestamp(InsCardEffectiveDate, 'M/D/YYYY hh:mm:ss tt'))) as PlanStartDate

Regards,

Joseph Morales

[Vegar]

I agree, it looks like strange behaviour. Are you solely running the sample load above or do you have other criterias in your Load?

It looks like your InsCardEffectiveDate is interpreted as a timestamp by default, if so then you don't need the advanced expression. Try using

dayname(InsCardEffectiveDate) as PlanStartDate

[Lauri]

Thank you, Vegar and Joseph. The load runs only the script that I included here. Based on your feedback, I tried a variety of functions.

Here are the results with "First 100000" - I named each field with the function(s):

Here are the results without "First 100000":

You are right that the simple Date and Dayname functions work here. But I would like to understand why the other functions behave differently! A bug? I'm on Sense June 2020. I get "good" results with "First X" no matter what X is.

And here is my current script:

ins:
// first 100000
LOAD
InsCardEffectiveDate as Original_Value,
Text(InsCardEffectiveDate) AS Text,
date(InsCardEffectiveDate) AS Date,
dayname(InsCardEffectiveDate) AS DayName,
timestamp#(InsCardEffectiveDate, 'M/D/YYYY hh:mm:ss tt') as TimeStamp#,
date(floor(timestamp#(InsCardEffectiveDate, 'M/D/YYYY hh:mm:ss tt'))) as Date_Floor_Timestamp#,
date(floor(date#(InsCardEffectiveDate, 'M/D/YYYY hh:mm:ss tt'))) as Date_Floor_Date#
FROM [lib://files/Ins.qvd](qvd);

[JosephMorales]

Hi @Lauri 

The InsCardEffectiveDate field is in timestamp format by default, so you don't need to use Data # and Timestamp #, these functions are used to convert a field in a text string to a date or timestamp field, only use Date and timestamp.

Now, regarding the problem you have when using FIRST, it may be forcing something from behind but, normally, it will not give you results if you use date # and timestamp # on a field that is not a text string. I tried it in April 2019 but I have no problems.

Regards

Joseph Morales