SQL Server: use parameter instead of GETDATE()

Question

I have a stored procedure that uses selects like the following which works fine so far. In this case for example it selects all records with a date from the previous month, i.e. March 2014 (column: dateEsc, formatted as nvarchar(20), example date: 2014-03-25).

My Select (example):

SELECT COUNT(*) AS groupCount
FROM Log_Esc
WHERE 
    CONVERT(DATE, dateEsc, 120) >= CONVERT(DATE, CONVERT(VARCHAR(6), DATEADD(month, -1, GETDATE()), 112) + '01', 112)

How do I have to change this if instead of the current Date (GETDATE()) I want to use a variable date input as the reference. This input would be any date and is formatted as nvarchar(20) as well, example: 2014-04-03.

So instead of calculating the previous month compared to the current month from GETDATE() I would like to calculate the same from the variable date input.

Many thanks for any help with this, Tim.

What's with the `CONVERT(VARCHAR(6)` portion of this? Also why is you date esc column being stored as `VARCHAR(20)`? — Zane, Apr 03 '14 at 17:43
@Zane - that is how it removes the non year/month data from the datetime record -- it is not the best way to do it, see my answer. — Hogan, Apr 03 '14 at 17:50
@Hogan but then Converting that date back back to date puts it back to YYYY-MM-DD — Zane, Apr 03 '14 at 17:52
@Zane - exactly why I think it is a bad way to do it. (it will have 1 as the day) — Hogan, Apr 03 '14 at 17:53

score 1 · Accepted Answer · edited May 23 '17 at 10:26

1

First of all I think this query is better than the one you have:

SELECT COUNT(*) AS groupCount
FROM Log_Esc
WHERE DATE >= dateadd(month,datediff(month,0,dateadd(month,GETDATE(),-1)),0)
 AND  DATE <  dateadd(month,datediff(month,0,GETDATE()),0)

If there is an index on the DATE field this can do a seek.

If you have a parameter @indate defined as date or datetime then this will work

SELECT COUNT(*) AS groupCount
FROM Log_Esc
WHERE DATE >= dateadd(month,datediff(month,0,dateadd(month,@indate,-1)),0)
 AND  DATE <  dateadd(month,datediff(month,0,@indate),0)

See this question for more information on flooring a date to a month: Floor a date in SQL server

edited May 23 '17 at 10:26

Community

1
1

answered Apr 03 '14 at 17:40

Hogan

69,564
10
76
117

Thanks a lot. I will check on this. – user2571510 Apr 03 '14 at 18:05
This works perfect - my mistake was that I didn't have the parameter set as datetime. Thanks again ! – user2571510 Apr 03 '14 at 18:14

score 0 · Answer 2 · answered Apr 03 '14 at 17:01

0

So what you want is a parameter:

Specifying Parameters in a Stored Procedure

Parameters allow you to pass user input to modify output.

An example

CREATE PROCEDURE dbo.Param1
@param int 
AS
BEGIN

select 7 *@param as Value
END

EXEC dbo.Param1 5 -- 7 *5
EXEC dbo.Param1 -10  -- 7 * -10

Perhaps this'll give you some creative ideas for how you might implement parameters to accomplish your group count.

answered Apr 03 '14 at 17:01

Kyle Hale

7,912
1
37
58

Thanks. Yes, I am looking for a parameter here but I don't know how I can refer to this instead of to GETDATE() within the select. – user2571510 Apr 03 '14 at 17:07

SQL Server: use parameter instead of GETDATE()

2 Answers2