Is there a way to pass the DatePart parameter of DateDiff as a variable? So that I can write code that is similar to this?
有没有办法将DateDiff的DatePart参数作为变量传递?这样我可以编写与此类似的代码?
DECLARE @datePart VARCHAR(2)
DECLARE @dateParameter INT
SELECT @datePart = 'dd'
SELECT @dateParameter = 28
SELECT
*
FROM
MyTable
WHERE
DATEDIFF(@datePart, MyTable.MyDate, GETDATE()) < @dateParameter
The only ways I can think of doing it are with a CASE statement checking the value of the parameter or by building the SQL as a string and running it in an EXEC.
我能想到的唯一方法是使用CASE语句检查参数的值,或者将SQL构建为字符串并在EXEC中运行它。
Does anyone have any "better" suggestions? The platform is MS SQL Server 2005
有人有任何“更好”的建议吗?该平台是MS SQL Server 2005
6 个解决方案
#1
19
According to BOL entry on DATEDIFF (arguments section) for SQL Server 2005,
根据SQL Server 2005的DATEDIFF(参数部分)上的BOL条目,
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.
因此,您可能会遇到动态SQL或使用CASE语句。但我会选择CASE版本而不是动态SQL。
#2
15
Old but still valid unfortunately.
虽然很旧,但仍然有效。
I did it the case way and just want to share the code so you don't have to do all the annoying typing I had to do. Covers all possible date parts. Just replace the name of the function and the date function to implement for other T-SQL date functions.
我是这样做的,只是想分享代码,所以你不必做我必须做的所有烦人的打字。涵盖所有可能的日期部分。只需替换函数的名称和日期函数,以实现其他T-SQL日期函数。
Copy and paste section
复制和粘贴部分
-- SELECT dbo.fn_DateAddFromStringPart('year', 1, GETDATE())
CREATE FUNCTION fn_DateAddFromStringPart
(
@Interval VARCHAR(11),
@Increment INT,
@Date SMALLDATETIME
)
RETURNS DATETIME
AS
BEGIN
-- Declare the return variable here
DECLARE @NewDate DATETIME
-- Add the T-SQL statements to compute the return value here
SELECT @NewDate = CASE
WHEN @Interval IN ('year', 'yy', 'yyyy') THEN DATEADD(YEAR, @Increment, @Date)
WHEN @Interval IN ('quarter', 'qq', 'q') THEN DATEADD(QUARTER, @Increment, @Date)
WHEN @Interval IN ('month', 'mm', 'm') THEN DATEADD(MONTH, @Increment, @Date)
WHEN @Interval IN ('dayofyear', 'dy', '') THEN DATEADD(DAYOFYEAR, @Increment, @Date)
WHEN @Interval IN ('day', 'dd', 'd') THEN DATEADD(DAY, @Increment, @Date)
WHEN @Interval IN ('week', 'wk', 'ww') THEN DATEADD(WEEK, @Increment, @Date)
WHEN @Interval IN ('weekday', 'dw', 'w') THEN DATEADD(WEEKDAY, @Increment, @Date)
WHEN @Interval IN ('hour', 'hh') THEN DATEADD(HOUR, @Increment, @Date)
WHEN @Interval IN ('minute', 'mi', 'n') THEN DATEADD(MINUTE, @Increment, @Date)
WHEN @Interval IN ('second', 'ss', 's') THEN DATEADD(SECOND, @Increment, @Date)
WHEN @Interval IN ('millisecond', 'ms') THEN DATEADD(MILLISECOND, @Increment, @Date)
WHEN @Interval IN ('microsecond', 'mcs') THEN DATEADD(MICROSECOND, @Increment, @Date)
WHEN @Interval IN ('nanosecond', 'ns') THEN DATEADD(NANOSECOND, @Increment, @Date)
END
-- Return the result of the function
RETURN @NewDate
END
GO
#3
2
The only thing you can really do aside from the suggested dynamic sql or case statement is to always do the datediff at a granular DatePart and then upconvert. This isn't fool proof though, you will get an overflow in the function if you try to datediff to granular a part over too large a span e.g. datediff(second, 0, getdate()). But if you just need something like minute parts you should be fine (double check with max date values you care about).
除了建议的动态sql或case语句之外,你唯一可以做的就是始终在粒度DatePart上执行datediff然后upconvert。这不是万无一失的,如果你试图在一个太大的范围内对一个部分进行粒化处理,你将会在函数中出现溢出,例如datediff(第二,0,getdate())。但是如果你只是需要像微小零件那样的东西你应该没问题(仔细检查你关心的最大日期值)。
So for example
所以举个例子
select datediff(minute, 0, getdate())
If I want to convert this to hours, days, etc, I can just divide the result by the appropriate amount. It won't take into account leap years etc.
如果我想将其转换为小时,天等,我可以将结果除以适当的数量。它不会考虑闰年等。
#4
1
Definitely using the dynamic query such as this below works very well, I assume you plan on using only abbreviation for @datePart. I would recommend using a minimum of VARCHAR(4) for datepart this will handle such abbreviations as yyyy and mcs. Happy coding:
绝对使用如下所示的动态查询非常有效,我假设您计划仅使用@datePart的缩写。我建议使用最小的VARCHAR(4)作为datepart,这将处理yyyy和mcs等缩写。快乐编码:
DECLARE @datePart VARCHAR(2)
DECLARE @dateParameter INT
DECLARE @SQLTemp varchar(MAX)
SELECT @datePart = 'dd'
SELECT @dateParameter = 28
set @SQLTemp ='SELECT * FROM MyTable
WHERE DATEDIFF('+@datePart+', '+MyTable.MyDate+', GETDATE()) < '+@dateParameter
exec (@SQLTemp)
#5
0
I don't think there are better ways then you describe. Sql Server probably compiles the DATEDIFF query to a set of operations that depend on the datepart parameter. So you'd need a CASE, or dynamic queries.
我不认为你描述的方法比较好。 Sql Server可能会将DATEDIFF查询编译为依赖于datepart参数的一组操作。所以你需要一个CASE或动态查询。
#6
0
I created a Scalar values function to do this. It is based on the solutions mentioned above but easier to implement in your code
我创建了一个Scalar值函数来执行此操作。它基于上面提到的解决方案,但在代码中更容易实现
CREATE FUNCTION [dbo].[dynamic_dateadd]
(
@unit varchar(5),
@number int,
@dt datetime
)
RETURNS datetime
AS
BEGIN
declare @result datetime
if (@unit='M') BEGIN SET @result=(select DATEADD(M,@number,@dt)) END
if (@unit='WW') BEGIN SET @result=(select DATEADD(WW,@number,@dt)) END
if (@unit='D') BEGIN SET @result=(select DATEADD(D,@number,@dt)) END
if (@unit='H') BEGIN SET @result=(select DATEADD(HH,@number,@dt)) END
return(@result)
END
In a query you can use this function like this:
在查询中,您可以像这样使用此函数:
select startdate, validity_unit, validity_number,
dbo.dynamic_dateadd(valididy_unit,validity_number,startdate) as enddate
from SALES_subscription
#1
19
According to BOL entry on DATEDIFF (arguments section) for SQL Server 2005,
根据SQL Server 2005的DATEDIFF(参数部分)上的BOL条目,
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.
因此,您可能会遇到动态SQL或使用CASE语句。但我会选择CASE版本而不是动态SQL。
#2
15
Old but still valid unfortunately.
虽然很旧,但仍然有效。
I did it the case way and just want to share the code so you don't have to do all the annoying typing I had to do. Covers all possible date parts. Just replace the name of the function and the date function to implement for other T-SQL date functions.
我是这样做的,只是想分享代码,所以你不必做我必须做的所有烦人的打字。涵盖所有可能的日期部分。只需替换函数的名称和日期函数,以实现其他T-SQL日期函数。
Copy and paste section
复制和粘贴部分
-- SELECT dbo.fn_DateAddFromStringPart('year', 1, GETDATE())
CREATE FUNCTION fn_DateAddFromStringPart
(
@Interval VARCHAR(11),
@Increment INT,
@Date SMALLDATETIME
)
RETURNS DATETIME
AS
BEGIN
-- Declare the return variable here
DECLARE @NewDate DATETIME
-- Add the T-SQL statements to compute the return value here
SELECT @NewDate = CASE
WHEN @Interval IN ('year', 'yy', 'yyyy') THEN DATEADD(YEAR, @Increment, @Date)
WHEN @Interval IN ('quarter', 'qq', 'q') THEN DATEADD(QUARTER, @Increment, @Date)
WHEN @Interval IN ('month', 'mm', 'm') THEN DATEADD(MONTH, @Increment, @Date)
WHEN @Interval IN ('dayofyear', 'dy', '') THEN DATEADD(DAYOFYEAR, @Increment, @Date)
WHEN @Interval IN ('day', 'dd', 'd') THEN DATEADD(DAY, @Increment, @Date)
WHEN @Interval IN ('week', 'wk', 'ww') THEN DATEADD(WEEK, @Increment, @Date)
WHEN @Interval IN ('weekday', 'dw', 'w') THEN DATEADD(WEEKDAY, @Increment, @Date)
WHEN @Interval IN ('hour', 'hh') THEN DATEADD(HOUR, @Increment, @Date)
WHEN @Interval IN ('minute', 'mi', 'n') THEN DATEADD(MINUTE, @Increment, @Date)
WHEN @Interval IN ('second', 'ss', 's') THEN DATEADD(SECOND, @Increment, @Date)
WHEN @Interval IN ('millisecond', 'ms') THEN DATEADD(MILLISECOND, @Increment, @Date)
WHEN @Interval IN ('microsecond', 'mcs') THEN DATEADD(MICROSECOND, @Increment, @Date)
WHEN @Interval IN ('nanosecond', 'ns') THEN DATEADD(NANOSECOND, @Increment, @Date)
END
-- Return the result of the function
RETURN @NewDate
END
GO
#3
2
The only thing you can really do aside from the suggested dynamic sql or case statement is to always do the datediff at a granular DatePart and then upconvert. This isn't fool proof though, you will get an overflow in the function if you try to datediff to granular a part over too large a span e.g. datediff(second, 0, getdate()). But if you just need something like minute parts you should be fine (double check with max date values you care about).
除了建议的动态sql或case语句之外,你唯一可以做的就是始终在粒度DatePart上执行datediff然后upconvert。这不是万无一失的,如果你试图在一个太大的范围内对一个部分进行粒化处理,你将会在函数中出现溢出,例如datediff(第二,0,getdate())。但是如果你只是需要像微小零件那样的东西你应该没问题(仔细检查你关心的最大日期值)。
So for example
所以举个例子
select datediff(minute, 0, getdate())
If I want to convert this to hours, days, etc, I can just divide the result by the appropriate amount. It won't take into account leap years etc.
如果我想将其转换为小时,天等,我可以将结果除以适当的数量。它不会考虑闰年等。
#4
1
Definitely using the dynamic query such as this below works very well, I assume you plan on using only abbreviation for @datePart. I would recommend using a minimum of VARCHAR(4) for datepart this will handle such abbreviations as yyyy and mcs. Happy coding:
绝对使用如下所示的动态查询非常有效,我假设您计划仅使用@datePart的缩写。我建议使用最小的VARCHAR(4)作为datepart,这将处理yyyy和mcs等缩写。快乐编码:
DECLARE @datePart VARCHAR(2)
DECLARE @dateParameter INT
DECLARE @SQLTemp varchar(MAX)
SELECT @datePart = 'dd'
SELECT @dateParameter = 28
set @SQLTemp ='SELECT * FROM MyTable
WHERE DATEDIFF('+@datePart+', '+MyTable.MyDate+', GETDATE()) < '+@dateParameter
exec (@SQLTemp)
#5
0
I don't think there are better ways then you describe. Sql Server probably compiles the DATEDIFF query to a set of operations that depend on the datepart parameter. So you'd need a CASE, or dynamic queries.
我不认为你描述的方法比较好。 Sql Server可能会将DATEDIFF查询编译为依赖于datepart参数的一组操作。所以你需要一个CASE或动态查询。
#6
0
I created a Scalar values function to do this. It is based on the solutions mentioned above but easier to implement in your code
我创建了一个Scalar值函数来执行此操作。它基于上面提到的解决方案,但在代码中更容易实现
CREATE FUNCTION [dbo].[dynamic_dateadd]
(
@unit varchar(5),
@number int,
@dt datetime
)
RETURNS datetime
AS
BEGIN
declare @result datetime
if (@unit='M') BEGIN SET @result=(select DATEADD(M,@number,@dt)) END
if (@unit='WW') BEGIN SET @result=(select DATEADD(WW,@number,@dt)) END
if (@unit='D') BEGIN SET @result=(select DATEADD(D,@number,@dt)) END
if (@unit='H') BEGIN SET @result=(select DATEADD(HH,@number,@dt)) END
return(@result)
END
In a query you can use this function like this:
在查询中,您可以像这样使用此函数:
select startdate, validity_unit, validity_number,
dbo.dynamic_dateadd(valididy_unit,validity_number,startdate) as enddate
from SALES_subscription