Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Morning All,
Could anyone assist with converting the time stamp in the attached document to a date.
Thanks
H
Use this script
LOAD
[Time Stamp],
Date(Timestamp#([Time Stamp],'MM/DD/YYYY h:mm:ss TT'),'MM/DD/YYYY') as Date
FROM
[Time Stamp.xlsx]
(ooxml, embedded labels, table is Sheet1);
Do you want to extract the date from timestamp or you want to change the data type?
Hi Herbert,
floor(date(dateField,'DD/MM/YYYY')) as newDate
if it is in text format then,
floor(date(date#(DateField,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY')) as newDate
Hi Pradnya,
I need to extract the date. Please see the attached Excel file for the raw data.
Thank you
Try like
Load
[Time Stamp],
Date( Time#([Time Stamp],'MM/DD/YYYY h:mm:ss TT'),'MM/DD/YYYY') as Date
From Location;
Thanks Anand,
This is the closest answer so far, as it actually produces a date. Problem however is that its giving dates in the year 1899. My data is 2014 data.
Any suggestions?
Thanks
Try this,
floor(date(date#([Time Stamp],'DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY')) as newDate
Change num to date from properties>Number>Date
Use this script
LOAD
[Time Stamp],
Date(Timestamp#([Time Stamp],'MM/DD/YYYY h:mm:ss TT'),'MM/DD/YYYY') as Date
FROM
[Time Stamp.xlsx]
(ooxml, embedded labels, table is Sheet1);
Please see the attachment.
Thanks,
AS