Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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]';
Try this,
timestamp(num(floor(num(date(decision_dte)))+num(time(decision_time))))
-Sundar
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')
Yes, I believe so.
hi
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')
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'))
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')
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