SQL Dynamic Datepart When Using Datediff

SQL Dynamic DatePart when using DateDiff

According to BOL entry on DATEDIFF (arguments section) for SQL Server 2005,

These dateparts and abbreviations cannot be supplied as a user-declared variable.

So you are probably stuck with Dynamic SQL or using a CASE statement.
But I would opt for a CASE version instead of dynamic SQL.

Using DATEDIFF to assign dynamic interval

You have to use dynamic sql for this. Once you start using dynamic sql you open yourself up to sql injection. Here is how you can do this and protect yourself against sql injection. The case expression may seem a little odd but if you have a value in your @interval that is not allowed the entire sql string will be NULL and no harm will come to your database. Also, please notice that you did not specify a length for @interval. For variables the default length is 1 so it would only be 'y' not 'year' as you expected it to be. Always specify the scale and precision of variables.

DECLARE @startdate datetime2 = '2007-05-05';  
DECLARE @enddate datetime2 = '2007-05-04';  
DECLARE @interval varchar(10) = 'year'

declare @SQL nvarchar(max)

set @SQL = 'select datediff(' + 
    case @interval when 'year' then 'year'
        when 'week' then 'week'
        when 'days' then 'day'
    end
    + ', @startdate, @enddate)'

exec sp_executesql @SQL, N'@startdate datetime2, @enddate datetime2', @startdate, @enddate

Can you pass a variable into the interval of DATEDIFF

You can't. You need to use a case expression:

select (case @interval
            when 'year' then datediff(year, mydate, getdate())
            when 'month' then datediff(month, mydate, getdate())
            when 'day' then datediff(day, mydate, getdate())
        end)

I should say. You could use dynamic SQL, but that is generally unnecessarily complicated for this type of calculation.

SQL Dynamic DatePart when using DateDiff

According to BOL entry on DATEDIFF (arguments section) for SQL Server 2005,

These dateparts and abbreviations cannot be supplied as a user-declared variable.

So you are probably stuck with Dynamic SQL or using a CASE statement.
But I would opt for a CASE version instead of dynamic SQL.

How to dynamically pass in a date_or_time_part for DateDiff?

date_or_time_part must be one of the values listed in Supported Date
and Time Parts (e.g. month). The value can be a string literal or can
be unquoted (e.g. 'month' or month).

You can use a CASE expression

case when mycolumn = 'month' then DATEDIFF(month,...)
    when mycolumn = 'year' then DATEDIFF(year,...)
end diff

How can I make a dynamic SQL Server query with sliding dates but specific times?

You're really close, you just need to change the second zero to the days you want to move.

WHERE startdatetime >= dateadd(hour,21,dateadd(dd,datediff(dd,0,getdate()),-9))
AND startdatetime <= dateadd(hour,23,dateadd(dd,datediff(dd,0,getdate()),-3))

The error 207 refers to an invalid column name. Make sure that you don't have any typos on your query and that the columns actually exist.

DATEPART as a parameter

One option is to use a CASE with the parameter (it would be better to use an int enumeration instead of string):

declare @part varchar(10)
set @part = 'YEAR'
select case @part 
            when 'YEAR' then datepart(yy, SomeDateTimeColumn) 
            when 'MONTH' then datepart(mm, SomeDateTimeColumn) 
            else datepart(dd, SomeDateTimeColumn) 
        end
from SomeTable

Defining type as a variable - DATEADD(type, value, date) - SQL

An alternative to already provided solution is to use a dynamic SQL:

Setup:

-- drop table dbo.DateTest
create table dbo.DateTest
(
    TheDate DATE,
    UpdatedDate DATE
)

TRUNCATE TABLE dbo.DateTest
GO
INSERT INTO dbo.DateTest (TheDate) VALUES ('20150203'), ('20150506'), ('20141231')
GO

Code:

select * from dbo.DateTest

DECLARE @type VARCHAR(8) = 'year'         -- this must be a DATEADD recognizable token
DECLARE @increment INT = 2
DECLARE @Sql NVARCHAR(1000) = N'
    UPDATE dbo.DateTest SET UpdatedDate = DATEADD(' + @type + ', @increment, TheDate)'

EXEC sp_executesql @Sql, N' @increment INT', @increment

select * from dbo.DateTest

I wish date part could be provided as parameter, but it does not seem to be possible.

---

Related Topics