Sunday, September 10, 2006

SQL to get Records for Last 24 Hours using DateAdd

One of the applications I wrote is a logging system. One of the requested modifications is that I make it easy for managers to look at entries made in the last 24 hours. Right now, I have hard-coded links for Current Day, Yesterday, Last 7, etc.

Current Day shows entries made since midnight. Yesterday shows entries made for the previous day. So right now there's no quick way to get the entries for the last 24 hours.

The solution was simple:
Select * from Entries where DateAdded >= " & DateAdd("d",-1, Now())
How it works:

The VBScript Now() function gets the current date and time. I then use the DateAdd funciton to subtract 1 day. I then get all entries with a DateAdded timestamp great than or equal to resulting date and time.

Alternately, you could subtract hours:
Select * from Entries where DateAdded >= " & DateAdd("h",-24, Now())


DateAdd is pretty nifty.

Syntax:
DateAdd(datepart, number, date)

Datepart can be (abbreviation):
year (yyyy)
quarter (q)
month (m)
day (d)
week (ww)
hour (h)
minute (n)
second (s)

Examples:
1 month from today: DateAdd("m", 1, Now())
100 years ago: DateAdd("yyyy", -100, Now())
10 minutes from now: DateAdd("m", 10, Now())
1 quarter (3 months) ago: DateAdd("q", -1, Now())

No comments: