Datepart() is there any way to use a variable for the datepart interval

Question

I'm creating a SQL server stored procedure that is doing a lot of filtering based on the current week. Rather than passing in a start and end date, I am passing in a single date and using Datepart() to filter out the week.

    Where DATEPART(WEEK, cd.TurninDate) = DATEPART(WEEK, @StartDate)
      and DATEPART(YEAR, cd.TurninDate) = DATEPART(YEAR, @StartDate)

Trying top be proactive, I can see managers asking for this to be run Monthly or Quarterly as well as Weekly. I'd rather not have a stored Proc for each version if I can help it. I want to pass in the frequency and the date but it doesn't seem to work.

I tried:

    Where DATEPART(@Freq, cd.TurninDate) = DATEPART(@Freq, @StartDate)
      and DATEPART(YEAR, cd.TurninDate) = DATEPART(YEAR, @StartDate)

but I get an error on the @Freq not being recognized as an interval.

Does anyone know a way I can get around that?

Do pass start and end dates. Or start date and duration (number of days). Filtering in the way you do it is not able to use index => it is slow on large tables. — Vladimir Baranov, May 28 '15 at 05:10
My Manager who is semi SQL literate is going to be running this so I want to make it as simple as possible. My goal was to allow him to put in the current date (or any date of a prior week) and get the full week's numbers. I don't want him to have to look up the first date of the week or (when it expands beyond the week only) figure out how many days in the month or quarter. Fortunately it will never be a large number of records. — Spots Knight, May 28 '15 at 15:25
Does this answer your question? [SQL Dynamic DatePart when using DateDiff](https://stackoverflow.com/questions/834971/sql-dynamic-datepart-when-using-datediff) — GSerg, May 23 '23 at 16:46

score 10 · Accepted Answer · answered May 27 '15 at 22:54

10

There is no way to use a variable but you can work around it:

case
 when @part = 'YEAR' THEN DATEPART(YEAR, cd.TurninDate)
 when @part = 'WEEK' THEN DATEPART(WEEK, cd.TurninDate)
 ...
 END

The design of the SQL/T-SQL language is lacking as you can see.

answered May 27 '15 at 22:54

usr

168,620
35
240
369

thank you this is exactly what I was looking for. Can't believe I didn't think of it myself because I have done similar before just not with dates. – Spots Knight May 28 '15 at 15:26

score 0 · Answer 2 · answered May 27 '15 at 23:12

The MSDN information is pretty clear

datepart Is the part of date (a date or time value) for which an integer will be returned. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid

https://msdn.microsoft.com/en-us/library/ms174420.aspx

But to get around this you can create a dynamic SQL and fill in that part with a variable.

select datepart(year,GETDATE())

declare @DateType varchar(max);
set @DateType = 'YEAR';
declare @datemanip varchar(max);
set @datemanip = 'select datepart(' + @DateType + ',GETDATE())';
execute( @datemanip );

Both of these statements will yield the same result. You can then put this in your stored procedure and pass in @DateType as parameter.

Thanks but the date part is part of the where clause and I do NOT want to make each entire query a dynamic query. some of them are rather complex. — Spots Knight, May 28 '15 at 15:28

Datepart() is there any way to use a variable for the datepart interval

2 Answers2