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
– Prabhu, Commented Nov 12, 2009 at 21:26

Jonathan · Accepted Answer · 2012-10-08 08:48:58Z

5

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

edited Oct 8, 2012 at 8:48

answered Nov 12, 2009 at 21:07

Jonathan

12k5 gold badges59 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Prabhu Over a year ago

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!

Jonathan Over a year ago

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: msdn.microsoft.com/es-es/library/ms187928.aspx

Prabhu Over a year ago

Thanks Jonathan. Unfortunately, 101 is the closest format mm/dd/yyyy, but I need m/d/yyyy

Jonathan Over a year ago

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.

Ice · Accepted Answer · 2011-09-27 01:30:26Z

1

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.

edited Sep 27, 2011 at 1:30

user142162

answered Nov 12, 2009 at 21:18

Ice

1,1723 gold badges15 silver badges28 bronze badges

Comments

Jonathan · Accepted Answer · 2012-10-08 08:48:42Z

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 8, 2012 at 8:48

Jonathan

12k5 gold badges59 silver badges92 bronze badges

answered Nov 12, 2009 at 21:10

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Comments

karel · Accepted Answer · 2021-11-17 05:51:32Z

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, 2021 at 5:51

karel

5,97562 gold badges58 silver badges60 bronze badges

answered Nov 17, 2021 at 5:48

Ezat allah zakavi

11 bronze badge

1 Comment

Yunnosch Over a year ago

While this code may solve the question, including an explanation 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.

Collectives™ on Stack Overflow

sql while loop with date counter

4 Answers 4

4 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related