Date Format

[#1]

Hi,

I want to load this sample file. But date formats are different. For some of the rows it is in 24 HR format and some rows in AM and PM format. Please find attached sample data. I need Month, Date and Hour format.

Thanks

[Kushal_Chawda]

You can give the format you want in red color

timestamp(alt(TImestamp#(StartDate,'DD-MM-YYYY hh:mm:ss'),TImestamp#(StartDate,'DD-MMM-YY hh.mm.ss.fff TT')),'DD/MM/YYYY hh:mm:ss') as NewDate

[sunny_talwar]

Try this:

Table:

LOAD [Product ID],

    [Product name],

    TimeStamp(Alt(TimeStamp#([Start Date], 'DD-MM-YYYY hh:mm:ss'), TimeStamp#([Start Date], 'DD-MMM-YYYY hh.mm.ss[.fff] TT'))) as [Start Date]

FROM

[Sample Date.xlsx]

(ooxml, embedded labels, table is Sheet1);

[tamilarasu]

Hi,

LOAD [Product ID],

     [Product name],

     Date(Alt(Date#([Start Date],'DD-MM-YYYY hh:mm:ss'),Date#('26-FEB-16 05.37.48.354000000 PM','DD-MMM-YY hh.mm.ss[.fff] TT')),'DD-MM-YYYY hh:mm:ss') as date

FROM

[Sample Date (1).xlsx]

(ooxml, embedded labels, table is Sheet1);

[Chanty4u]

chk dis

[#6]

Thanks Sunny,

But I don't want AM and PM. I want in 24 Hrs format

[sunny_talwar]

Change this part:

SET TimestampFormat='D/M/YYYY h:mm:ss[.fff]';

Table:

LOAD [Product ID],

    [Product name],

    TimeStamp(Alt(TimeStamp#([Start Date], 'DD-MM-YYYY hh:mm:ss'), TimeStamp#([Start Date], 'DD-MMM-YYYY hh.mm.ss[.fff] TT'))) as [Start Date]

FROM

[Sample Date.xlsx]

(ooxml, embedded labels, table is Sheet1);

[Chanty4u]

chk it now

[#9]

Hi Sunny,

How to derive Month, Day and Hour

Thanks

[tamilarasu · Accepted solution]

Hi Jack,

Try,

Table:
LOAD [Product ID], 
     [Product name], 
      Date(Alt(Date#([Start Date],'DD-MM-YYYY hh:mm:ss'),Date#([Start Date],'DD-MMM-YY hh.mm.ss[.fff] TT')), 'DD-MM-YYYY') as [Start Date],
      Date(Alt(Date#([Start Date],'DD-MM-YYYY hh:mm:ss'),Date#([Start Date],'DD-MMM-YY hh.mm.ss[.fff] TT')), 'MMM') as [Month],
      Date(Alt(Date#([Start Date],'DD-MM-YYYY hh:mm:ss'),Date#([Start Date],'DD-MMM-YY hh.mm.ss[.fff] TT')), 'DD') as [Day],
      Date(Alt(Date#([Start Date],'DD-MM-YYYY hh:mm:ss'),Date#([Start Date],'DD-MMM-YY hh.mm.ss[.fff] TT')), 'hh') as [Hour]
     FROM
[Sample Date.xlsx]
(ooxml, embedded labels, table is Sheet1);