Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Help with dates in a SQL server pass through query

Posted on 2011-09-19
13
Medium Priority
?
440 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

636 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