带有日期计数器的 sql while 循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1725379/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql while loop with date counter
提问by Prabhu
I need to do something like this in sql:
我需要在 sql 中做这样的事情：
declare @StartDate varchar(10)
declare @EndDate varchar(10)
set @StartDate='12/31/2008'
set @EndDate='1/11/2009'
Declare @date varchar = @StartDate
while (@date <= @EndDate)
begin
-- some statements
set @date += 1 -- basically increment by 1 day
end
How can I do the above correctly in SQL? Basically, my startdate and enddate are strings and not datetimes because my business logic is referencing string column in another table with a date as the name of the column-But I need to loop through a bunch of columns, each column having the name of the next day's date.
如何在 SQL 中正确执行上述操作？基本上，我的 startdate 和 enddate 是字符串而不是日期时间，因为我的业务逻辑引用另一个表中的字符串列，并将日期作为列的名称 - 但我需要遍历一堆列，每列都有第二天的日期。
If the date is 11/07/2009, the name of the column would be '11/7/2009' (without the 0 in the 7), so I have to watch out for that too.
如果日期是 11/07/2009，则列的名称将是“11/7/2009”（没有 7 中的 0），所以我也必须注意这一点。
Any help is appreciated!
任何帮助表示赞赏！
Thanks.
谢谢。
回答by Jonathan
You can convert the date params to datetime.
您可以将日期参数转换为日期时间。
SELECT convert(datetime, @StartDate) into datetimevariable
then you can use date functions to add days.
那么您可以使用日期函数来添加天数。
select DATEADD(day,1,datetimevariable) into datetimevariable
As a solution to get a m/d/yyyy format, I C&P this function from some website a couple of weeks ago. Use this code to create a function and call in this way:
作为获取 am/d/yyyy 格式的解决方案，几周前我从某个网站 C&P 这个功能。使用此代码创建一个函数并以这种方式调用：
SELECT dbo.fnFormatDate (@DateTimeVariable, 'M/DD/YYYY') into stringVariable
CREATE FUNCTION dbo.CustomFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @StringDate VARCHAR(32)
SET @StringDate = @FormatMask
IF (CHARINDEX (‘YYYY',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘YYYY',
DATENAME(YY, @Datetime))
IF (CHARINDEX (‘YY',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘YY',
RIGHT(DATENAME(YY, @Datetime),2))
IF (CHARINDEX (‘Month',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘Month',
DATENAME(MM, @Datetime))
IF (CHARINDEX (‘MON',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)
SET @StringDate = REPLACE(@StringDate, ‘MON',
LEFT(UPPER(DATENAME(MM, @Datetime)),3))
IF (CHARINDEX (‘Mon',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘Mon',
LEFT(DATENAME(MM, @Datetime),3))
IF (CHARINDEX (‘MM',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘MM',
RIGHT(‘0′+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
IF (CHARINDEX (‘M',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘M',
CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
IF (CHARINDEX (‘DD',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘DD',
RIGHT(‘0′+DATENAME(DD, @Datetime),2))
IF (CHARINDEX (‘D',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘D',
DATENAME(DD, @Datetime))
RETURN @StringDate
END
GO
回答by Remus Rusanu
回答by Ice
i would prefere the way like Jonathan allready said but use datetime variables for the loop and add a day with the DATEADD function. To access the columns you may use the CONVERT or CAST statement but now to get a varchar e.g.: CAST(@date as varchar(10)). If special formatting is needed you can build such a construct like cast(day(@date) as varchar(2)) + '/' + cast(month(@date) as varchar(2)) + '/' + cast(year(@date) as varchar(4)) this would eliminate the '0' as you mentioned.
我更喜欢 Jonathan allready 所说的方式，但对循环使用日期时间变量并使用 DATEADD 函数添加一天。要访问列，您可以使用 CONVERT 或 CAST 语句，但现在要获取 varchar，例如：CAST(@date as varchar(10))。如果需要特殊格式，您可以构建这样的结构，如 cast(day(@date) as varchar(2)) + '/' + cast(month(@date) as varchar(2)) + '/' + cast( year(@date) as varchar(4)) 这将消除您提到的“0”。
