Finding a prior date by subtracting from today

J

Jim in Arizona

I'm having difficulty figuring out how to find the date three days from
today's date.

I'm trying to use an sql select statement, like so:

"SELECT * FROM TableA " & _
"WHERE DateColumn BETWEEN " & _
BeginDate & " AND " & EndDate

I want to display records that are from three days ago to the present.

I don't know how to find the date from three days ago. Something like:

Dim EndDate as Date = Now() 'Or DateAndTime.Today
Dim BeginDate as Date = DateAndTime.Today - 3

Of course, that doesn't work.

Any Ideas?

TIA,
Jim
 
M

Mark Rae

"SELECT * FROM TableA " & _
"WHERE DateColumn BETWEEN " & _
BeginDate & " AND " & EndDate

I want to display records that are from three days ago to the present.

"SELECT * FROM TableA " & _
"WHERE DateColumn <= " & _
"DATEADD(d, -3, CONVERT(datetime, CONVERT(varchar, getdate(), 106)))"
 
J

Jim in Arizona

Mark said:
"SELECT * FROM TableA " & _
"WHERE DateColumn <= " & _
"DATEADD(d, -3, CONVERT(datetime, CONVERT(varchar, getdate(), 106)))"

I actually glanced at the DateAdd function on a webpage somewhere and
within Microsoft.VisualBasic.DateAndTime but wasn't sure about its use.

So, this works just as good on the vb code side:

Dim BeginDate As Date = DateAdd(DateInterval.Day, -3, DateAndTime.Today)

So, which would be more effective, do you think? Using DateAdd in the vb
code or in the SQL? I plan on using an SQL Stored Proc for the SQL
instead of storing it in a string on the vb code side.

Thanks Mark.

Jim
 
M

Mark Rae

So, this works just as good on the vb code side:

Dim BeginDate As Date = DateAdd(DateInterval.Day, -3, DateAndTime.Today)
Yep.

So, which would be more effective, do you think? Using DateAdd in the vb
code or in the SQL? I plan on using an SQL Stored Proc for the SQL instead
of storing it in a string on the vb code side.

I don't suppose it matters *too* much in the general scheme of things -
however, I tend to use a stored procedure whenever I can because of its
precompiled execution plan...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,899
Latest member
RodneyMcAu

Latest Threads

Top