...

View Full Version : Date Difference



phantom007
06-18-2007, 01:29 PM
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.

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

Daemonspyre
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
response.flush

miranda
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

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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum