Help on Date comparission

[#1]

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?

[Gysbert_Wassenaar · Accepted solution]

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

[#3]

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?

[Gysbert_Wassenaar]

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).