sql while loop with date counter

Question

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.

Thanks for all your answers. I posted another question specifically about the datetime. — Prabhu, Nov 12 '09 at 21:26

Jonathan · Accepted Answer · 2012-10-08T08:48:58.813

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

Thanks. How do you convert from datetime to varchar with a particular format: declare @StartDate datetime set @StartDate='12/07/2008' declare @StartDateString varchar(10) How do you set @StartDateString to equal '12/7/2008' Thanks! — Prabhu, Nov 12 '09 at 21:11
you can call to convert to cast from datetime to string with a third param. This param is an int that tell the format. Please check this link: http://msdn.microsoft.com/es-es/library/ms187928.aspx — Jonathan, Nov 12 '09 at 21:15
Thanks Jonathan. Unfortunately, 101 is the closest format mm/dd/yyyy, but I need m/d/yyyy — Prabhu, Nov 12 '09 at 21:27
I edited my answer to append a solution for the m/d/yyyy format, and others. If you're iterested in performance, modify the function to convert the date to m/d/yyy only. Will be shorter. — Jonathan, Nov 12 '09 at 21:40

score 1 · Answer 2 · edited Oct 08 '12 at 08:48

1

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);

edited Oct 08 '12 at 08:48

Jonathan

11,809
5
57
91

answered Nov 12 '09 at 21:10

Remus Rusanu

288,378
40
442
569

score 1 · Answer 3 · edited Sep 27 '11 at 01:30

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.

score 0 · Answer 4 · edited Nov 17 '21 at 05:51

0

DECLARE @sd DATETIME
DECLARE @ed DATETIME
SELECT @sd = CONVERT(DATETIME, '2020-08-23')
SELECT @ed = CONVERT(DATETIME, '2021-03-21')

DECLARE @date DATETIME = @sd
WHILE ( @date <= @ed )
  BEGIN
      -- some statements
      SELECT @date
      SELECT @date = Dateadd(day, 1, @date)
  END

edited Nov 17 '21 at 05:51

karel

5,489
46
45
50

answered Nov 17 '21 at 05:48

Ezat allah zakavi

1
1

While this code may solve the question, [including an explanation](//meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply. – Yunnosch Nov 17 '21 at 06:45

sql while loop with date counter

4 Answers4

Linked