SQL DATEDIFF Not working!?

Question

Hi all,

I am running a simple DATEDIFF query but it doesn't seem to calculate the days properly or i'm doing something wrong.

If I run

PRINT DATEDIFF(Day, 2010-01-20, 2010-01-01)
RETURN 19

Which is correct. If i change the month in the first date to Feb (02) I get something strange.

PRINT DATEDIFF(Day, 2010-02-20, 2010-01-01)
RETURN 20

Now shouldn't it be 48 or something?

Can anyone see what i'm doing wrong or is this not the correct function to be using if I want the No of days between these dates?

I've tried taking one date from the other:

PRINT (2010-02-20) - (2010-01-01)
RETURN -20

Any help much appreciated.

Thanks J.

Answer 1

You are missing quotes

PRINT DATEDIFF(Day, '2010-01-01', '2010-02-20')

You're getting 20 because

2010 - 1 - 1 = 2008
2010 - 2 - 20 = 1988

2008 - 1988 = 20

Answer 2

If you run it this way:

SELECT  2010-02-20, 2010-01-01

you will see

1988  2008

which are results or the integer operations that you put here.

Enclose the date constants into single quotes:

SELECT  DATEDIFF(Day, '2010-02-20', '2010-01-01')

--
-50

Answer 3

PRINT DATEDIFF(Day, '2010-01-10', '2010-02-20') 

That takes 2nd date - first date. Don't forget ' '.

Answer 4

It works if you surround your dates with apostrophes -

SELECT DATEDIFF(day, '2010-02-20', '2010-01-10')

-41