Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a 'Date', 'Start' and 'Duration' field. The Duration is a decimal hours field eg, 12.5 rather than 12:30.
For example, if I have 01/10/2017 as a Date, 20:00, as a Start time, I need to create an End DateTime field to show, in this
example, 02/10/2017 20:30.
Is this possible?
Thanks,
Phil
SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';
Load *,
Date(num(Date#(Date(date#(Date,'DD/MM/YYYY'),'DD/MM/YYYY')&' '&Date#(Start,'hh:mm'),'DD/MM/YYYY hh:mm'))+num#(Duration,'##,###')/24,'DD/MM/YYYY hh:mm') as End;
LOAD * INLINE [
Date, Start, Duration
01/10/2017, 20:00, "12,5"
];
Hi,
For add 12.5 hours and obtain you End date time
=Timestamp(Timestamp#(Date&' '&Start time,'DD/MM/YYYY hh:mm')+0.52,'DD/MM/YYYY hh:mm')
May be this
TimeStamp(Date + Interval#([Start time]*60*60, 's')) as [End DateTime]
If you need to convert decimal to exact time and concatenate in date field, You can use this:
Example: = date(today()) & ' ' & Time(12.5/24)
SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';
Load *,
Date(num(Date#(Date(date#(Date,'DD/MM/YYYY'),'DD/MM/YYYY')&' '&Date#(Start,'hh:mm'),'DD/MM/YYYY hh:mm'))+num#(Duration,'##,###')/24,'DD/MM/YYYY hh:mm') as End;
LOAD * INLINE [
Date, Start, Duration
01/10/2017, 20:00, "12,5"
];
Hi,
another example:
table1:
LOAD *,
Timestamp(Date+Start+Duration) as End;
LOAD Date,
Start,
Interval(Duration/24,'hh:mm') as Duration
INLINE [
Date, Start, Duration
01/10/2017, 20:00, 12.5
03/10/2017, 12:34, 5.8
05/10/2017, 23:45, 2.2
];
hope this helps
regards
Marco
I went with Franks's answer. But really appreciate everyone's responses.