Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am doing some manipulations.
I have a date filed which is of timestamp. So i have converted into Date(Date field)
When i am doing Today() - Date(Date field) it is not giving result as expected.
Example IF
Date(Date field) = 25-12-2013 the output is 0
Date(Date field) = 26-12-2013 the output is -0
And important thing is when Today is 01-01-2014 and Date(Date field) = 31-12-2013 it is giving -20.
Can some one tell me where i am doing wrong?
I have a date filed which is of timestamp
That's where your problem is. Look at the output of num(Date Field). You'll see that there is a fraction, which is the time part of the date time. If you want to calculate using the date parts only you need to use the floor function to cut of the time part: today() - floor(Date Field)
And important thing is when Today is 01-01-2014 and Date(Date field) = 31-12-2013 it is giving -20.
That sounds like the string of the date is interpreted as numbers:
1 -1 - 2014 = -2014
31 - 12 - 2013 = -1994
-2014 - -1994 = -2014 + 1994 = -20
I have a date filed which is of timestamp
That's where your problem is. Look at the output of num(Date Field). You'll see that there is a fraction, which is the time part of the date time. If you want to calculate using the date parts only you need to use the floor function to cut of the time part: today() - floor(Date Field)
And important thing is when Today is 01-01-2014 and Date(Date field) = 31-12-2013 it is giving -20.
That sounds like the string of the date is interpreted as numbers:
1 -1 - 2014 = -2014
31 - 12 - 2013 = -1994
-2014 - -1994 = -2014 + 1994 = -20
Hi Gysbert,
Floor function is solving my problem.
What about the second case ?
When Today = 1 -1 - 2014
and Floor(Datefield ) = 31 - 12 - 2013
Will it give -20 or 1?
No idea. I can't read minds so I have no idea what you're actually doing. You can't simply use a string 31-12-2013 and expect it to be treated as a date. Use the date# function to create a date from a string or use the makedate function: makedate(2013,12,1).