如何循环日期作为SQL查询的参数？ [英] How to loop through the date as parameter of SQL query?

问题描述

我的TRANSACT SQL QUERY接受 @ Start_Date ='2016-06-3 13：46：25.370'作为参数并返回结果：

 ColA ColB ColC 
'2016-06-3 13 ：46：25.370'B C 

我想传递另一个参数 @ End_Date ='2016-06- 5 13：46：25.370'并得到结果：

 ColA ColB ColC 
 '2016-06-3 13：46：25.370'B C 
'2016-06-4 13：46：25.370'B C 
'2016-06-5 13：46：25.370'B C 

基本上，我试图将日期作为参数循环。



我的SQL查询是：< br $>

  DECLARE   @ Start_Date   as  Datetime = '  2016-06- 3 13：46：25.370'， @ End_Date   as  datetime = '  2016-06-3 13：46：25.370' 
  SELECT   @ Start_Date  [ColA]，'  B' [ColB]，'  C' [ColC] 

任何帮助都将受到高度赞赏。

解决方案

尝试：

 SELECT * FROM MYTable WHERE MyDateColumn BETWEEN @Start_Date AND @End_Date 

但是......如果那些是你的列值而你将日期存储在VARCHAR或NVARCHAR列中，然后您需要先修复它。永远不要在字符串列中存储日期（或除了名称和地址之类的真正字符串值之外的任何其他内容） - 稍后当您尝试使用它们时会出现大问题，因为字符串排序基于两个字符串中的第一个不同字符。

如果您正在寻找解决方案来生成具有两个日期作为输入参数的部分数据，您可能会对 CTE [ ^ ]。

  DECLARE   @ Start_Date   DATETIME  = '  2016-06-03 13：46：25.370' 
  DECLARE   @ End_Date   DATETIME  = '  2016-06-05 13：46：25.370' 
 
;  WITH  MyDates  AS  
（
  SELECT   @ Start_Date   AS  MyDate 
  UNION   ALL  
  SELECT  DATEADD（DD， 1 ，MyDate） AS  MyDate 
  FROM  MyDates 
  WHERE  DATEADD（DD， 1 ，MyDate）< = @ End_Date 
 ）
  SELECT  * 
  FROM  MyDates 
   -   选项（MAXRECURSION 0） - 如果日期超过100 ，则取消注释这个谎言

结果：

 MyDate 
 2016-06-03 13 ：46：25.370 
 2016-06-04 13：46：25.370 
 2016-06-05 13：46：25.370 

Hi,

My TRANSACT SQL QUERY accepts @Start_Date='2016-06-3 13:46:25.370' as a parameter and returns the results:

ColA	ColB	ColC
'2016-06-3 13:46:25.370'	B	C

I want to pass another parameter @End_Date='2016-06-5 13:46:25.370' and get the result:

ColA	ColB	ColC
'2016-06-3 13:46:25.370'	B	C
'2016-06-4 13:46:25.370'	B	C
'2016-06-5 13:46:25.370'	B	C

Basically, I am trying to loop through the dates as parameter.

My SQL Query is:

DECLARE @Start_Date as Datetime='2016-06-3 13:46:25.370', @End_Date as datetime='2016-06-3 13:46:25.370'
SELECT @Start_Date [ColA], 'B' [ColB], 'C' [ColC] 

Any help will be highly appreciated.

解决方案

Try:

SELECT * FROM MYTable WHERE MyDateColumn BETWEEN @Start_Date AND @End_Date

But... If those are your column values and you are storing dates in VARCHAR or NVARCHAR columns, then you need to fix that first. Never store dates (or anything else other than genuine string values like names and addresses) in string columns - it gives huge problems later when you try to use them, because string ordering is based on the first different character in two strings.

If you're looking for solution to "produce" some portion of data having two dates as input parameters, you may be interested in CTE[^].

DECLARE @Start_Date DATETIME ='2016-06-03 13:46:25.370'
DECLARE @End_Date DATETIME ='2016-06-05 13:46:25.370'

;WITH MyDates AS
(
	SELECT @Start_Date AS MyDate
	UNION ALL
	SELECT DATEADD(DD,1, MyDate) AS MyDate
	FROM MyDates
	WHERE DATEADD(DD,1, MyDate)<=@End_Date
)
SELECT *
FROM MyDates
--OPTION(MAXRECURSION 0) --uncomment this lie, if the count of dates exceedes 100

Result:

MyDate
2016-06-03 13:46:25.370
2016-06-04 13:46:25.370
2016-06-05 13:46:25.370

这篇关于如何循环日期作为SQL查询的参数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！