Date from Datetime in format YYYYMMDD

[jasmeenkr]

Hi All,

I am trying to load date from datetime using script:

LOAD
Date(DateTime,'YYYYMMDD') as DateKey,

but it gives me value as integer 43256 and i want data to be YYYYMMDD.

[dplr-rn]

give an example of values in DateTime in source

[jasmeenkr]

The values are like 2/3/2019 12:00:00 AM

[dplr-rn · Accepted solution]

in your load script try something like below

Date(Date#(DateTime,'d/m/yyyy hh:mm:ss TT'),'YYYYMMDD') as DateKey

[jasmeenkr]

Thanks. This does resolved issue but now I have to join it with another table where datekey is defined as bigint values 20190903. how to convert it into date so that it joins to Fact.DateSK

[dplr-rn]

Similar process using date# and date but different format s.

Date(date#(column-name, 'source forma'), 'desired format')

If you want to link between tables you will need to add floor to my previous answer to remove time

E.g. date(floor(date#(...))....)

[jasmeenkr]

Hi ,,

So my DImDate has dateKey -datatype- bigint

Fact Order has Datekey from Date(Date#(Datetime,'m/d/yyyy hh:mm:ss'),'YYYYMMDD') as integer value like 43754 in place of datekey like 20140305. how to correct this so my tables connect and my data relatable between two

[dplr-rn · Accepted solution]

If it's already a date number you don't need date# function.

Just the date function and format you need