Separating date and time from the field ?

[Anonymous]

Hello ,

  I'm have a field in this format  1/1/2012 7:15:54PM   ie MM//DD/YYY HH:MM:SS .i"m using Qlikview 11 version.

    I want to separate date and time component . How can this be done.

Attaching an example qvw

Thanks

B

[Gysbert_Wassenaar · Accepted solution]

Use the floor and frac functions to split the datetime in date and time parts:

Date_field:

LOAD

Date_time,

floor(Date_time) as Date,

frac(Date_time) as Time

FROM

\\Falconetwork\dsteam$\_Swarup\Camp\date_time.xls

(biff, embedded labels, table is Sheet1$);

[Anonymous]

Hi Prabhu,

Date_field:

LOAD Date_Time,

          Date(Timestamp#(@1,'MM/DD/YYYY hh:mm:ss TT'),'DD-MM-YYYY')   AS Date,

          Time(Timestamp#(@1,'MM/DD/YYYY hh:mm:ss TT'),'DD-MM-YYYY')   AS Time

From

\\Falconetwork\dsteam$\_Swarup\Camp\date_time.xls

(biff, embedded labels, table is Sheet1$);

above script is usefull for seperating  date and time.

Regards

[Anonymous]

Thank you guys

B

[Anonymous]

Hi Prabhu,

   Above mentioned floor and frac functions also useful but in frent of those script add date for date and time function for time.

Date_field:

LOAD

Date_time,

DATE(floor(Date_time),'DD-MM-YYYY') as Date,

time(frac(Date_time),'hh:mm:ss') as Time

FROM \\Falconetwork\dsteam$\_Swarup\Camp\date_time.xls(biff, embedded labels, table is Sheet1$);ds

Rgards