sql while循环与日期计数器 [英] sql while loop with date counter
问题描述
我需要在sql中执行以下操作:
I need to do something like this in 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
如何在SQL中正确执行上述操作?基本上,我的开始日期和结束日期是字符串,而不是日期时间,因为我的业务逻辑引用了另一个表中的字符串列,但以日期作为列的名称.但是我需要遍历一堆列,每个列都具有第二天的日期.
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.
如果日期为2009年7月11日,则该列的名称将为"11/7/2009"(7中没有0),因此我也必须注意这一点.
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.
感谢您的帮助!
谢谢.
推荐答案
您可以将日期参数转换为日期时间.
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
作为获取m/d/yyyy格式的解决方案,我几周前从某些网站上使用了C& P.使用此代码创建函数并以这种方式调用:
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:
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
这篇关于sql while循环与日期计数器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!