Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to fill a new field in a table with the date from a field in a previous record.
The latter contains a date in the following format: yyyy-mm-dd hh:mm:ss:xxxxxxx (not sure what those xx's are). Example:
2014-11-14 02:00:50:3930000
I need to use that value in a (new) field in the current record, but I need to substract a second or a minute from that value.
I have been searching, but couldn't find an answer...
Try this to subtract a second:
=Timestamp(Timestamp#('2014-11-14 02:22:50:3930000', 'YYYY-MM-DD hh:mm:ss:fff') - Interval#('1', 'ss'), 'YYYY-MM-DD hh:mm:ss:fffffff')
To subtract a minute, try this:
=Timestamp(Timestamp#('2014-11-14 02:22:50:3930000', 'YYYY-MM-DD hh:mm:ss:fff') - Interval#('1', 'mm'), 'YYYY-MM-DD hh:mm:ss:fffffff')
Hi,
How to calculate the time difference between two different date?
Use the above link, it might help u.
Not sure what you mean when you say you have to subtract a second or a minute? In your above example: 2014-11-14 02:00:50:3930000 what would be the output you would be expecting?
Hi Harshita and thank you for your swift reply.
I have looked at that link, and unfortunately, it gives an example on how to calculate a difference, not how to substract a value (of a second or a minute).
Thanks anyway!
If the first value is 2014-11-14 02:22:50:3930000 then the second value should be 2014-11-14 02:22:49:3930000
(which subtracts one second) OR the outcome should be 2014-11-14 02:21:50 3930000, which is the subtraction of a minute.
I think you need to create a master Time Table: The Master Time Table
This works as a master calendar, where you create missing time values. You can decide your smallest denomination to be Seconds or Minute.
HTH
Best,
Sunny
Actually, I want to do the subtraction using a variable.. It's not a matter of a 'missing' time value - I just want to do a calculation on the value of a field within a record..
First set up format like
for Minutes Calculation
vNowTime= num(timestamp#('2015/09/07 11:55:25 PM','YYYY/MM/DD hh:mm:ss TT'));
vPrevTime = num(timestamp#('2015/09/07 11:55:25 PM','YYYY/MM/DD hh:mm:ss TT'));
sum(vNowTime - vPrevTime )
or
vNowTime= num(timestamp#(INVTIME,'YYYY/MM/DD hh:mm:ss TT'));
vPrevTime = num(timestamp#(INVTIME,'YYYY/MM/DD hh:mm:ss TT'));
for Days Calculation
sum(Date(Today())- date(date#(INVDATE,'YYYYMMDD'),'DD/MM/YYYY'))
hope it works
A bit like AddMonths(EndDate,-1), but then for a minute or a second, if you catch my drift..