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

Help with dates in a SQL server pass through query

I use the following expression as query criteria  in access to return records that were entered during the previous 14 days only.

labor_date = Between Date()-14 and Date()

What would be the equivalent syntax for a pass through query against a SQL Server table.  I know that SS handles dates differently than access.

Thanks
0
snyperj
Asked:
snyperj
  • 6
  • 4
  • 2
  • +1
1 Solution
 
Dale FyeCommented:
It has been a while since I've used SQL Server, but I believe you are going to have to build that SQL string manually.

strSQL = "SELECT ... FROM yourTable WHERE [Labor_Date] BETWEEN '" & Format(Date()-14, "yyyy-mm-dd") & "' " _
           & "AND '" & Format(Date(), "yyyy-mm-dd") & "'"

Then push that SQL into the SQL property of your query:

currentdb.Querydefs("YourPassThruQueryName").SQL = strSQL

0
 
Rey Obrero (Capricorn1)Commented:
try using dateserial()

labor_date between dateserial(year(date()),month(date()),day(date())) and dateserial(year(date()),month(date()),day(date())-14)
0
 
Nick67Commented:
SQL Server will deal with date literals surrounded by single quotes
Here's the SQL text of my pass-through

Exec SpInspectorRevenueSummary '27-May-10 12:00 AM' ,'02-Jun-10 11:59 PM', 1

It EXECutes SpInspectorRevenueSummary passing in two date literals and a third integer parameter
Dates are fun things :(
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
snyperjAuthor Commented:
Thanks, I was able to pass:

 between '2011-09-05' and '2011-09-19'
0
 
snyperjAuthor Commented:
using variables set beforehand for the actual dates
0
 
Nick67Commented:
Once you get the hang of it, it is easy enough.
Use VBA to build a T-SQL compliant string
Use a querydef, and change it's SQL property to that string
Give 'er
 
Dim db As Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRecordSource As String

'how this works:
'On the SQL db there is a stored procedure WhatEverYourSPisNamed  that needs parameters
'In the mdb there is a passthrough query that has an ODBC connection string and returns records = true
'The text in the passthrough query matters little, it could even be blank. It gets replaced
'by the code below, then executed and the records populate the report.
'the stored procedure is WhatEverYourSPisNamed 
Set db = CurrentDb
Set qdf = db.QueryDefs("SomePassThroughQuery")
With Forms!frmInspectorRevenueSummary
    strRecordSource = "Exec WhatEverYourSPisNamed " & Chr(39) & Nz(!StartDate, "'1/1/2005'") & Chr(39) & " ," & Chr(39) & Nz(!EndDate, Now()) & Chr(39) & ", " & Me.FrameDateType
End With
qdf.SQL = strRecordSource
Me.RecordSource = "SomePassThroughQuery"

Open in new window

0
 
Dale FyeCommented:
If you passed:

between '2011-09-05' and '2011-09-19'

Then why did you not select my response as the answer?
0
 
Nick67Commented:
It wouldn't have worked @fyed.
For a passthrough, it has to be bare T-SQL syntax
It would have needed to be literally

exec somepassthrough @p1, @p2....@p1000

You got the formatting of the date literals surrounded by single quotes right, but the sproc is going to take the parameters and do stuff with them, so the rest of the SQL was wrong.
0
 
Dale FyeCommented:
Nick,

I disagree.  The OP didn't say anything about a stored procedure, and I the SQL syntax I provided would work just fine as the SQL of a passthrough query.

The OP could then set that query as the Recordsource of a report or form, or as the RowSource of a list or combo.

Dale
0
 
Nick67Commented:
I guess I had sproc on the brain.  It'd the only thing I use passthroughs for.  You could be right about your syntax working
0
 
Dale FyeCommented:
Nick,

I rarely contest the OP's selection of the correct answer, but nothing you provided contained the

" between '2011-09-05' and '2011-09-19' "

syntax the OP claimed to use in the problem solution.
0
 
Nick67Commented:
Points aren't a big deal.
The accepted solution does show that I feed in date literals surrounded by single quotes.
That's the needed chunk of the syntax, knowing that SQL Server needs single quotes and not hashes (#)
Yours shows that too, but maybe it got lost in the '" '" mega quote notation
Your syntax would have worked

There's no much question how the dates are going in here
Exec SpInspectorRevenueSummary '27-May-10 12:00 AM' ,'02-Jun-10 11:59 PM', 1
<SQL Server will deal with date literals surrounded by single quotes>

@Nick67
0
 
Nick67Commented:
@fyed
I do have to thank you.
I've never seen anything like <Date()-14> before or realized that that would work
Or that more generally DateValue("SomeStringDate')-14 would work
I've always used the more tortured
Format(DateSerial(Year(Now()), Month(Now()), Day(Now()) - 14), "dd-mmm-yyyy")
to do date calculations.

Thank you!
I learned something new.

@Nick67
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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