?
Solved

Help with dates in a SQL server pass through query

Posted on 2011-09-19
13
Medium Priority
?
432 Views
Last Modified: 2012-05-12
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
Comment
Question by:snyperj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 36561802
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36561803
try using dateserial()

labor_date between dateserial(year(date()),month(date()),day(date())) and dateserial(year(date()),month(date()),day(date())-14)
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 2000 total points
ID: 36562899
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Closing Comment

by:snyperj
ID: 36563213
Thanks, I was able to pass:

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

Author Comment

by:snyperj
ID: 36563220
using variables set beforehand for the actual dates
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36563288
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
 
LVL 48

Expert Comment

by:Dale Fye
ID: 36564087
If you passed:

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

Then why did you not select my response as the answer?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36564114
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
 
LVL 48

Expert Comment

by:Dale Fye
ID: 36564263
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
 
LVL 26

Expert Comment

by:Nick67
ID: 36564293
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
 
LVL 48

Expert Comment

by:Dale Fye
ID: 36564478
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
 
LVL 26

Expert Comment

by:Nick67
ID: 36565320
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
 
LVL 26

Expert Comment

by:Nick67
ID: 36567856
@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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question