Using date variables in sql statements

  • genova11

    SSC Journeyman

    Points: 96

    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"));

  • pietlinden

    SSC Guru

    Points: 62358

    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: 27323

    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 Journeyman

    Points: 96

    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 Journeyman

    Points: 96

    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: 62358

    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 Journeyman

    Points: 96

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

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

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