topic Re: Separating date and time from the field ? in QlikView

Separating date and time from the field ?

Anonymous — Thu, 24 Jan 2013 19:53:11 GMT

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

Gysbert_Wassenaar — Thu, 24 Jan 2013 19:59:04 GMT

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 — Thu, 24 Jan 2013 20:38:33 GMT

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 — Thu, 24 Jan 2013 20:47:42 GMT

Thank you guys

Anonymous — Thu, 24 Jan 2013 20:48:40 GMT

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