View Full Version : Date Difference

06-18-2007, 01:29 PM

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.

06-18-2007, 04:14 PM
what do you mean, it doesn't work?

06-18-2007, 04:35 PM
What is your Session.LCID or your Regional Settings on your server?

If they don't match up with the info in your database (i.e. they are not both US, GB, or INDIA), then you will run into date format issues, causing your query to "not work".

You may want to use a response.write of your query and see what the SQL is when you put your information in.

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

response.write strSQL

06-18-2007, 08:29 PM
You have the code backwards. Try this instead

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

Try this in Query Analyzer to see the results

DATEDIFF(d, GETDATE(), LastLogin) AS Difference