topic Re: Date Format in QlikView

Date Format

Thu, 31 Mar 2016 09:44:39 GMT

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

Re: Date Format

Kushal_Chawda — Thu, 31 Mar 2016 09:49:57 GMT

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

Re: Date Format

sunny_talwar — Thu, 31 Mar 2016 09:50:31 GMT

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);

Re: Date Format

tamilarasu — Thu, 31 Mar 2016 09:52:45 GMT

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);

Re: Date Format

Chanty4u — Thu, 31 Mar 2016 09:56:42 GMT

chk dis

Re: Date Format

Thu, 31 Mar 2016 10:08:30 GMT

Thanks Sunny,

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

Re: Date Format

sunny_talwar — Thu, 31 Mar 2016 10:11:00 GMT

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);

Re: Date Format

Chanty4u — Thu, 31 Mar 2016 10:15:01 GMT

chk it now

Re: Date Format

Fri, 01 Apr 2016 03:23:33 GMT

Hi Sunny,

How to derive Month, Day and Hour

Thanks

Re: Date Format

tamilarasu — Fri, 01 Apr 2016 04:16:29 GMT

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);

Re: Date Format

Fri, 01 Apr 2016 06:47:49 GMT

Thank You all