SQL DATEDIFF Not working?

sql tsql datediff
Jammer picture Jammer · Mar 25, 2010 · Viewed 11.9k times · Source

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

CResults picture CResults · Mar 25, 2010

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

Related questions

using DateDiff to find duration in minutes

I am trying to use Datediff to find out the duration between columnA and columnB. SELECT datediff (minute, stime, etime) from Exceptions2 where stime = [exceptions2].starttime and etime = [exceptions2].endtime This produces errors. Can anyone please help me with what …

Read More
how to get the number on months between two dates in sql server 2005

I have a column in my sql server 2005 table that should hold the number of months an employee has been in service. Since I also have the date the employee was engaged, I want the "months_In_Service" column to …

Read More
How do you handle NULLs in a DATEDIFF comparison?

I have to compare 2 separate columns to come up with the most recent date between them. I am using DATEDIFF(minute, date1, date2) to compare them, however, in some records the date is Null, which returns a null result and …

Read More