topic Date and time Fields into Timestamp in QlikView

Date and time Fields into Timestamp

Thu, 13 Feb 2014 08:55:26 GMT

I have two fields. Decision_Dte(dd/mm/yyyy) and Decision_Time(hh:mm:ss). How can I merge these into dd/mm/yyyy hh:mm:ss format.

I used the following but it gives me wrong year e.g. 30-09-3036 10:21:15. Year 3036 is wrong it should be 2013

Timestamp(DECISION_DTE & ' ' & DECISION_TIME , 'DD-MM-YYYY hh:mm:ss') as DecisionDateTime

Re: Date and time Fields into Timestamp

giakoum — Thu, 13 Feb 2014 09:06:25 GMT

it seems to work just fine. see attached. are you sure that your input fields are date and time;

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

Re: Date and time Fields into Timestamp

sundarakumar — Thu, 13 Feb 2014 09:08:09 GMT

Try this,

timestamp(num(floor(num(date(decision_dte)))+num(time(decision_time))))

-Sundar

Re: Date and time Fields into Timestamp

Thu, 13 Feb 2014 09:13:24 GMT

when i removed # after date and time then it worked. Is it correct?

Timestamp

(Date(DECISION_DTE,'DD-MM-YYYY') + Time(DECISION_TIME,'hh:mm:ss'), 'DD-MM-YYYY hh:mm:ss')

Re: Date and time Fields into Timestamp

giakoum — Thu, 13 Feb 2014 09:17:20 GMT

Yes, I believe so.

Re: Date and time Fields into Timestamp

Thu, 13 Feb 2014 09:25:59 GMT

try this

Date(date#(Date(Date#(Decision_Dte,'DD/MM/YYYY'),'DD/MM/YYYY') &' ' & Time(decision_time,'hh:mm:ss'),'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss')

Re: Date and time Fields into Timestamp

Thu, 13 Feb 2014 09:29:36 GMT

File name is Data Warehouse Sep 2013
APPL_ID	EVT_DTE	EVT_TIME
19681349	02/09/2013	15:49:56
File name is September data
APPL_ID	Input_Date_Time
19681349	04/09/2013 14:24
File name is Warehouse Data Sep, 2103
APPL_ID	DECISION_DTE	DECISION_TIME
19681349	04/09/2013	14:32:54

I have the above data in three files. I want to calculate the following difference of Networking days in Hours : (7.5 working hours in a day Monday to Friday).

1). (Input_Date_TIME) - (Evt_DTE & EVT_TIME)

2). (Decision_date & Decision_Time) - (Input_Date_Time)

Is it possible to calculate by taking data from three files. I have the following code but how should it be based on the above requirement:

Let vStartTime = '09:00:00';

Let vQuitTime = '17:00:00';

Let vWorkDay = '$(vQuitTime)'-'$(vStartTime)';

fabs(interval(((Networkdays(Date(Input_Date_Time,'DD/MM/YYYY hh:mm:ss'),Date(Decisiondatetimefinal,'DD/MM/YYYY hh:mm:ss')) -2)*'$(vWorkDay)')
+
if(frac(date(Input_Date_Time))<num('$(vQuitTime)'),if(frac(date(Input_Date_Time))>num('$(vStartTime)'),Date#(date(floor(Input_Date_Time),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date(Input_Date_Time,'DD/MM/YYYY hh:mm:ss'),Date#(date(floor(Input_Date_Time),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date#(date(floor(Input_Date_Time),'DD/MM/YYYY') & '$(vStartTime)' ,'DD/MM/YYYYhh:mm:ss')),0)
+
if(frac(date(Decisiondatetimefinal))>num('$(vStartTime)'),if(frac(date(Decisiondatetimefinal))<num('$(vQuitTime)'),(Date(Decisiondatetimefinal,'DD/MM/YYYY hh:mm:ss')-Date#(date(floor(Decisiondatetimefinal),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),Date#(date(floor(Decisiondatetimefinal),'DD/MM/YYYY') & '$(vQuitTime)','DD/MM/YYYYhh:mm:ss')-Date#(date(floor(Decisiondatetimefinal),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),0), 'hh:mm:ss'))

Re: Date and time Fields into Timestamp

ashwanin — Thu, 13 Feb 2014 09:37:42 GMT

Try below :-

date(floor(timestamp#(Decision_Dte, 'DD.MM.YYYY hh:mm')), 'DD/MM/YYYY') &' ' & Time(DECISION_TIME ,'hh:mm:ss'),'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss')

Re: Date and time Fields into Timestamp

er_mohit — Thu, 13 Feb 2014 09:50:13 GMT

Try this

you have to firstly know about in which format is your date and time then try this code

Timestamp#(Date#(Decision_Dte,'DD/MM/YYYY')&' '& Time#(DECISION_TIME ,'hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') as Datetime