• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 174
  • 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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
É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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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