sql while loop with date counter

问题:

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

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.

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.

Any help is appreciated!

Thanks.

回答1:

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:

 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

回答2:

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.

回答3:

Use DATEADD, then cast it back to a string using the format of choice, like 101 in your case:

set @date = convert(varchar(30), dateadd(day,1, @date), 101);