Strange behavior with Excel dates

[#1]

Hi,

When I load the following dates from Excel, somehow the field Started

is not formatted as expected.
Both fields look identical in Excel.

Only if I add it as a new field (with the date#) it works properly.

Please see attached images.

The problematic script:

LOAD [Case ID],
Events,
date#(Started, 'DD.MM.YYYY hh:mm:ss') as Started,  
Date#(Finished, 'DD.MM.YYYY hh:mm:ss') as Finished

FROM
[..\Data\Sales Orders - Durations.xls]
(biff, embedded labels, table is [Sales Orders - Durat$]);

The script that works:

LOAD [Case ID],
Events,
date#(Started, 'DD.MM.YYYY hh:mm:ss') as NewStarted,   
Date#(Finished, 'DD.MM.YYYY hh:mm:ss') as Finished,
....

Thanks!

[manideep78]

Right Click on the Table--> Properties--> Number tab-->

Change the option as TimeStamp#

[er_mohit]

hiii

it give you the number format of date generates by OS..

by changing the alias name you got the right format which you want like

LOAD [Case ID],
Events,
date#(Started, 'DD.MM.YYYY hh:mm:ss') as A_Started,  
Date#(Finished, 'DD.MM.YYYY hh:mm:ss') as c_Finished

FROM
[..\Data\Sales Orders - Durations.xls]
(biff, embedded labels, table is [Sales Orders - Durat$]);

[#4]

Hi,

The question is why the fields don't behave in the same manner.

Another words, one can be directly manipulated, and the other one needs a name change.

Thanks!

[er_mohit · Accepted solution]

if you see your field finished, it can't be give you the right format which you want somehow it gives you default format as your OS. and sometimes it gives the autogenerate number which show in your

date#(Date(Started, 'DD.MM.YYYY hh:mm:ss'),'DD.MM.YYYY hh:mm:ss') as Started

try this

because date# always work with date function  so, date# gives you the format code and date gives you the format you want