Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4

Thread: Date Difference

  1. #1
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    922
    Thanks
    302
    Thanked 3 Times in 3 Posts

    Question 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.

  • #2
    Regular Coder
    Join Date
    Jan 2005
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts
    what do you mean, it doesn't work?

  • #3
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    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.

    Code:
    strSQL = "SELECT * from userLogin where DATEDIFF(d, GETDATE(), LastLogin) <= 7"
    
    response.write strSQL
    response.flush
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #4
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,069
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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
    Last edited by miranda; 06-18-2007 at 07:32 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •