Date Difference

Hello,

I have MS Sql server 2000 database and I use ASP as front end.

I have the following table

---------------------
Name | LastLogin
--------------------
ABC | 6/5/2007
DEF | 9/12/2006
MNO | 1/15/2007
XYZ | 6/5/2006
------------------


I want to select the name of persona who have logged in in the past 7 days.

The following query does not work for some reasons.

SELECT * from userLogin where DATEDIFF(d, GETDATE(), LastLogin) <= 7


Thanx for your help.

 

Where did that "d" variable come from?
DATEDIFF should only have 2 arguments, most probably just GETDATE() and LastLogin.

 

"d" will show the difference in "days"

 

Great. That made me feel like an idiot (didn't think Transact SQL when seeing MSSQL)

Back to the question:
Can you show the result of:

SELECT LastLogin, DATEDIFF(d, LastLogin, GETDATE()) FROM userLogin

Code (markup):

And what do you mean "not working?". Is it returning all the results? Maybe because it is date2-date1 so it should be DATEDIFF(d, LastLogin, GETDATE()), not the other way round...

 

This is wrong If I am remember correct: DATEDIFF(d, LastLogin, GETDATE())
Correct for is:
DATEDIFF("d", LastLogin, GETDATE())
or
DATEDIFF(day, LastLogin, GETDATE())

I prefer second one because of I don't need to use aposthropes (") and don't need to escape.

 

you can always put ABS() around it

 

what is ABS() ?

 

returns the absolute number, so with + or -

then it doesn't matter what variable goes first in datediff, either it'll return 7 or -7 and with abs(datedif(..)) both will qualify

 

It's better to just get it right and not use workarounds. cancer10, did you solve the problem?