Using date variables in sql statements

Question

Using date variables in sql statements

genova11

SSC-Addicted

Points: 434
More actions
September 6, 2018 at 9:58 am

#362438

I am an SQL novice. I am going to be putting some of my access queries in macros so I need to automate some of the dates I have in my SQL statements so no user intervention is required. I have researched but have not found what I need yet.
Here is one of my SQL statements. I just need to have a date variable that will take the place of my hard coded date - "201807". In this case, it is YYYYMM. (MM is the previous month). Any help is appreciated. Thank you!!
SELECT DISTINCT [dbo_RX Reporting].[Plan Name], [dbo_RX Reporting].[Therapeutic Class], Sum([dbo_RX Reporting].Price) AS SumOfPrice, [dbo_RX Reporting].Period
FROM [dbo_RX Reporting]
GROUP BY [dbo_RX Reporting].[Plan Name], [dbo_RX Reporting].[Therapeutic Class], [dbo_RX Reporting].Period, [dbo_RX Reporting].[Plan Name]
HAVING ((([dbo_RX Reporting].[Therapeutic Class]) Like "*Analgesics*") AND (([dbo_RX Reporting].Period)="201807") AND (([dbo_RX Reporting].[Plan Name])="Alabama"));

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63428 More actions · Answer 1

Is this a SQL Server question or an Access question? Using parameters in SQL Server, in my opinion, is MUCH clearer.
Given the syntax and the absolutely screwy way Access puts filters in the HAVING clause, this must be an Access query.
In query design view, right-click the design surface (where the tables are) and select Parameters from the dropdown. Then add your parameter (I'll call mine "prmDate"), and make sure you set the type to "date with time". Then you can use that parameter in your filter. For example
>=[prmDate]

PARAMETERS LastDate DATETIME;
SELECT Images.ImageName, Keywords.Keyword
FROM Keywords INNER JOIN (Images INNER JOIN ImageKeyword ON Images.ImageID = ImageKeyword.ImageID) ON Keywords.KeywordID = ImageKeyword.KeywordID
WHERE (((Images.ImageDate) >= Parameters![LastDate] ));

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 2

That's an ambiguous question.

If this is in a SQL script or command, add
DECLARE @Period CHAR(6) = '201809'; -- You don't say where this is being entered SELECT ... WHERE dbo_RX_Reporting.Period = @Period GROUP BY ...
(A filter in the WHERE clause will limit the rows processed, in the HAVING clause it will process and group all rows first and then filter the results. The WHERE clause is a better place to put a non-aggregate filter.)

Or it could be a SQLCMD variable reference,
:SETVAR Period 201809 SELECT ... WHERE dbo_RX_Reporting.Period = '$(Period)' GROUP BY ...

If this is a query in a report definition or some other external query, then it might depend on the connection used. It could be a named variable such as "Period = @Period", or a question mark parameter placeholder ( Period = ? ).

genova11 SSC-Addicted Points: 434 More actions · Answer 3

pietlinden - Thursday, September 6, 2018 10:28 AM
Is this a SQL Server question or an Access question? Using parameters in SQL Server, in my opinion, is MUCH clearer.
So which are you using? The way the two deal with parameters is different and I don't want to just go down an irrelevant rabbit hole. SQL Server is something like
CREATE PROC GetMyData
   @CutoffDate DATE
AS
   -- you could do math here to change the value of @CutOffDate before the query uses it...
   SELECT <field list>
FROM Events
WHERE Events.EventDate >= @CutOffDate;
Getting parameter values in Access is messier.

I am doing the query in Access. I need to make a change to the SQL code (in Access) though so I can automate the date.

genova11 SSC-Addicted Points: 434 More actions · Answer 4

Scott Coleman - Thursday, September 6, 2018 10:28 AM
That's an ambiguous question.
If this is in a SQL script or command, add
DECLARE @Period CHAR(6) = '201809'; -- You don't say where this is being entered SELECT ... WHERE dbo_RX_Reporting.Period = @Period GROUP BY ...
(A filter in the WHERE clause will limit the rows processed, in the HAVING clause it will process and group all rows first and then filter the results. The WHERE clause is a better place to put a non-aggregate filter.)
Or it could be a SQLCMD variable reference,
:SETVAR Period 201809 SELECT ... WHERE dbo_RX_Reporting.Period = '$(Period)' GROUP BY ...
If this is a query in a report definition or some other external query, then it might depend on the connection used. It could be a named variable such as "Period = @Period", or a question mark parameter placeholder ( Period = ? ).

The query exists in Access. I need to change the SQL code (in Access) so the date is automated. I do not want to have to change the date every month.

pietlinden SSC Guru Points: 63428 More actions · Answer 5

One day I swear I'm going to learn how to read. Apparently it hasn't happened yet.

If you're always "pointing at" or comparing to a date a fixed "distance" in the past, like "30 days ago", then you'd be better off writing this as (simplified)
SELECT...
FROM...
WHERE EventDate >= DATEADD(day,DateAdd("d",-30,Date())

genova11 SSC-Addicted Points: 434 More actions · Answer 6

Thank you. I ended up doing this:
AND (([dbo_RX Reporting].Period)=Format(DateAdd("m", -1, Date()), "yyyymm"))