using datediff

adamhw · November 29th, 2010, 06:33 PM

Hi there I want to select records from my database that were entered in the last 7 days. I'm thinking it would b something like this:

strDatenow = now()

SELECT * FROM mytable WHERE DATEDIFF(strDatenow) <= 7

Is this anywhere near where it should be?

thanks

Adam

Old Pedant · November 29th, 2010, 08:33 PM

You have to have some FIELD in your TABLE that holds the date entered.

Databases do *NOT* do that automatically.

*ASSUMING* you have such a field:

Code:

SELECT * FROM mytable
WHERE theDateRecordWasAdded >= DATEADD(d,-7,getDate())

Now, the problem with that is that getDate() returns a date AND time, equivalent to NOW() in VB/VBScript. So that DATEADD will give you a date and time exactly 7 days ago as of this moment. If you want it to be 7 days ago, starting at midnight on that day, we have to get fancier.

But I won't go further until you tell me (a) you do have a field in your table that holds when the record was added, (b) whether that field holds a date-only or a date+time value, and (c) how you want to "cut off" the records [to the second or to the day].

adamhw · November 30th, 2010, 04:47 AM

Great thanks Old Pedant for the detailed response.

The db does hold a datetime type field called dateadded and I would like it to be to the day.

So in answer to your questions:

a) you do have a field in your table that holds when the record was added,

Yes - called 'dateadded'

(b) whether that field holds a date-only or a date+time value, and

holds datetime value

(c) how you want to "cut off" the records [to the second or to the day].

to the day would be great.

Old Pedant · November 30th, 2010, 04:00 PM

I just realized that you posted in the "SQL Server ASP" forum, so I assume you are using ASP to drive all this, right?

So we can do it easier by letting ASP code do some of the work:

Code:

<%
...
weekAgo = DATE() - 7 ' really!  it's this simple in ASP code!
SQL = "SELECT * FROM mytable WHERE dateAdded >= '" & weekAgo & "'"
Set RS = yourAlreadyOpenConnection.Execute( SQL )
...

If you do it all in the SQL query it's much messier:

Code:

SELECT * FROM mytable
WHERE dateAdded >= DATEADD( d, -7, CONVERT(DATETIME,CONVERT(VARCHAR(20),getDate(),112),112) )

That's because SQL Server has no simply way to get the current date (getDate() is the equivalent of NOW() in VBS) so you have to do that messy double CONVERT.

adamhw · January 15th, 2011, 03:44 PM

Hi Old Pedant,

This is an addition to this thread and was hoping you could clarify this for me:

My sql statement reads: select * from mytable where p.dateadded <= DATEADD(d,-90,getDate()) -

I'm hoping this will return anything from my table older than 90 days old?

Am I correct?

thanks

Adam

Old Pedant · January 15th, 2011, 04:20 PM

Yes, but with the caveat that it will be 90 days to the second, not to the day, as I discussed back in November.

So, again, you might be better off doing part of the work in ASP code:

Code:

SQL = "SELECT * FROM mytable WHERE dateAdded >= '" & ( DATE() - 90 ) & "'"

Ummm...just noticed that your code had p.dateAdded but no place do you alias any table to "p".

adamhw · January 16th, 2011, 01:22 PM

thanks Old Pedant, that's great.

Yes, sorry, I do use p. as an alias for a table in the proper sql statement.