This timestamp field is strange

[n1ef5ng1]

Hi everyone,

Please see attached to understand more about this data column call Date. I am unable to change anything from data point of view as it is scrap totally from website.

Date as Timestamp,

     Date(floor(timestamp#(Date,'MM/DD/YYYY hh:mm')),'DD/MM/YYYY') as Date,

    hour(Timestamp#(Date,'MM/DD/YYYY hh:mm')) as Hour

Basically the raw data is timestamp,

however for conversion of timestamp to date and hour, I somehow cant get the first twelve days of the month for this timestamp. Anybody can help me on this? thanks in advance

[tresesco]

You are right. Somehow you have to tell qv when to take the format as DD/MM/YYYY and when to consider it as MM/DD/YYYY. Do you have a logic?

[ganeshsvm]

Hi Ben, you can try the following script,

LOAD Date as Timestamp,
if(IsNum(Date), Date#(Date(floor(SubField(Date,' ',1)),'MM-DD-YYYY')), Date(Date#(SubField(Date,' ',1),'M/DD/YYYY'))) as NewDate,
Hour(Date) as Hours
FROM

(ooxml, embedded labels, table is Sheet1);