• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 172
  • Last Modified:

SQL date calculations?

Hi I was wondering if I could sort a database by extracting all dates within 7 days of the present date.

I'm trying something like this:
SELECT WHEN, DOWHAT, ID FROM Source WHERE (Date - WHEN) <= 00/07/00 ORDER BY WHEN;

I was pretty sure it wouldn't work, but you can't blame a guy for trying.  =)

0
nerfgunz
Asked:
nerfgunz
  • 6
  • 3
  • 2
  • +3
1 Solution
 
morgan_peatCommented:
You can do this through VB, or in the DB.

VB:
SELECT stuff FROM table WHERE date >= DateAdd("d", -7, Now)

Or through DB, which is DB specific (eg. SQL Server has a DateAdd, and a GetDate function)
0
 
jrspanoCommented:

select field1, field2 from table1 where DATEDIFF(day, datefieldindb, getdate()) <= 7
0
 
nerfgunzAuthor Commented:
Yes i am trying to do it in vb.  However both of these methods are not working for me.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Éric MoreauSenior .Net ConsultantCommented:
Which database engine are you using? Different engines = Different syntax.
0
 
nerfgunzAuthor Commented:
access 2000
0
 
dgorinCommented:
Try this

sql = "SELECT WHEN, DOWHAT, ID FROM Source WHERE WHEN <= #" & DateDiff("d", -7, Now()) & "# ORDER BY WHEN"

Date values in Access sql need to be delimited by the # character, most other SQL's by the ' character.



0
 
nerfgunzAuthor Commented:
what is "d"?
0
 
nerfgunzAuthor Commented:
dqorin, it doesnt work.
0
 
nerfgunzAuthor Commented:
I'm trying this now, but it gives me ALL the data in the table.

dcOverduePending.RecordSource = "SELECT WHEN, DOWHAT, ID FROM " & Source & " WHERE DateDiff(""d"", WHEN, " & Date & ") <= 7 ORDER BY WHEN;"
0
 
nerfgunzAuthor Commented:
going to start a new thread.
0
 
Éric MoreauSenior .Net ConsultantCommented:
I just test this query against the NWind database and it is working:
Adodc1.RecordSource = "SELECT DateDiff('d', OrderDate, Date()), * " & _
                      "From Orders " & _
                      "Where DateDiff('d', OrderDate, Date()) >= 7 " & _
                      "ORDER BY OrderDate"

Your query should read:
dcOverduePending.RecordSource = "SELECT WHEN, DOWHAT, ID FROM " & Source & " WHERE DateDiff('d', WHEN, Date()) <= 7 ORDER BY WHEN"
0
 
Hornet241Commented:

Try this

Dim TargetDate as Date

TargetDate = Now - 7

SELECT WHEN, DOWHAT, ID FROM " & Source & " WHERE When > #" & TargetDate & "#"

0
 
dgorinCommented:
Sorry, should have been DateAdd, not DateDiff.  DateAdd returns a date datatype, DateDiff returns a long integer.  The "d" indicates the interval is by days.

sql = "SELECT WHEN, DOWHAT, ID FROM Source WHERE WHEN <= #" & DateAdd("d", -7, Date()) & "# ORDER BY
WHEN"



0
 
Éric MoreauSenior .Net ConsultantCommented:
Please maintain this question.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now