Geek Answers Handbook

SQL: Is there a way to iteratively convert a DECLARE variable?

Is there a way to do something like this in SQL:

DECLARE @iter = 1

WHILE @iter<11
BEGIN
DECLARE @('newdate'+@iter) DATE = [some expression that generates a value]
SET @iter = @iter + 1
END

In the end, I would have 10 variables:

@newdate1
@newdate2
@newdate3
@newdate4
@newdate5
@newdate6
@newdate7
@newdate8
@newdate9
@newdate10

Update:

Based on the comment, I think I should indicate why I want to do this. I am working with Report Builder 3.0. I am going to make a report where the input will be start dateand end date(in addition to one other parameter). This will generate data between the date range. However, the user also wants to check the same date range for all other years in 2013 → this year.

: 2013 , , . , 1/1/2014 - 6/1/2014, , 2013, 2015 2016 .

:

1/1/2016 - 6/1/2016

:

1/1/2013 - 6/1/2013
1/1/2014 - 6/1/2014
1/1/2015 - 6/1/2015
1/1/2016 - 6/1/2016

, .

+1
7

UDF .

exanple

Select DateR1=RetVal,DateR2=DateAdd(MM,5,RetVal) from [dbo].[udf-Create-Range-Date]('2013-01-01','2016-01-01','YY',1) 


DateR1      DateR2
2013-01-01  2013-06-01
2014-01-01  2014-06-01
2015-01-01  2015-06-01
2016-01-01  2016-06-01

UDF

CREATE FUNCTION [dbo].[udf-Create-Range-Date] (@DateFrom datetime,@DateTo datetime,@DatePart varchar(10),@Incr int)

Returns 
@ReturnVal Table (RetVal datetime)

As
Begin
    With DateTable As (
        Select DateFrom = @DateFrom
        Union All
        Select Case @DatePart
               When 'YY' then DateAdd(YY, @Incr, df.dateFrom)
               When 'QQ' then DateAdd(QQ, @Incr, df.dateFrom)
               When 'MM' then DateAdd(MM, @Incr, df.dateFrom)
               When 'WK' then DateAdd(WK, @Incr, df.dateFrom)
               When 'DD' then DateAdd(DD, @Incr, df.dateFrom)
               When 'HH' then DateAdd(HH, @Incr, df.dateFrom)
               When 'MI' then DateAdd(MI, @Incr, df.dateFrom)
               When 'SS' then DateAdd(SS, @Incr, df.dateFrom)
               End
        From DateTable DF
        Where DF.DateFrom < @DateTo
    )

    Insert into @ReturnVal(RetVal) Select DateFrom From DateTable option (maxrecursion 32767)

    Return
End

-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','YY',1) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','DD',1) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-31','MI',15) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-02','SS',1)

- NON UDF SQL

Declare @startdate Date ='1/1/2014'   -- user supplied value
Declare @enddate Date = '6/1/2014'    -- user supplied value

Declare @DateFrom Date = cast('2013-'+cast(month(@StartDate) as varchar(10))+'-'+cast(Day(@StartDate) as varchar(10)) as date)
Declare @DateTo Date   = cast(cast(Year(GetDate()) as varchar(10))+'-'+cast(month(@enddate) as varchar(10))+'-'+cast(Day(@enddate) as varchar(10)) as date) 
Declare @Incr int = DateDiff(MM,@startdate,@enddate)  -- made to be dynamic based on the user supplied dates

Declare @DateRange Table (DateR1 date,DateR2 Date)

;with DateTable As (
    Select DateFrom = @DateFrom
    Union All
    Select DateAdd(YY, 1, df.dateFrom)
    From DateTable DF
    Where DF.DateFrom < @DateTo
)
Insert into @DateRange(DateR1,DateR2) Select DateR1=DateFrom,DateR2=DateAdd(MM,@Incr,DateFrom) From DateTable option (maxrecursion 32767)

Select * from @DateRange
+3

, , ( ..), Tally (a ). - :

declare @startDate date = '20160101'
declare @endDate date = '20160601'

select N
    , dateadd(year, (N - 1) * -1, @startDate) as StartDate
    , dateadd(year, (N - 1) * -1, @endDate) as EndDate
from tally -- Follow the link above for info on how to create this table
where N <= 4
order by N desc

:

N   StartDate   EndDate
4   2013-01-01  2013-06-01
3   2014-01-01  2014-06-01
2   2015-01-01  2015-06-01
1   2016-01-01  2016-06-01

, , SQL. , , , .

, . , - , SQL, .

, , , , .

+3

, .

declare @numbers table(n int)
insert into @Numbers(N)
select top 1000 row_number() over(order by t1.number) as N
from   master..spt_values t1 
       cross join master..spt_values t2

,

    declare @iniDate date
    declare @endDate date
    delcare @limitDate date

    set @iniDate='20160101'
    set @enddate='20160106'
    set @limitDate ='20130101'

    select dateadd(yy,-1*(n-1),@inidate), dateadd(yy,-1*(n-1),@enddate) 
    from @numbers where (n-1)<=datediff(yy, @limitDate, @inidate)

EDIT: :

declare @iniDate date
declare @endDate date
declare @iniLimitDate date
declare @endLimitDate date

set @iniDate='20140201'
set @endDate='20140206'
set @iniLimitDate ='20130101'
set @endLimitDate ='20161231'    

select datefromparts(year(@iniLimitDate)+n-1,month(@iniDate), day(@iniDate))
    ,datefromparts(year(@iniLimitDate)+n-1,month(@endDate), day(@endDate))
from @numbers 
where  year(@iniLimitDate)+n-1<=year(@endlimitdate)
    and year(@iniLimitDate)+n-1<>year(@enddate)

. feb 29

+2

- ? SQL . , , getDate().

DECLARE @iter int = 1
Declare @SQL VARCHAR(MAX)

WHILE @iter<11
BEGIN
SET @SQL = ISNULL(@SQL,'') + ' DECLARE @newdate'+ CAST(@iter AS VARCHAR) + ' DATE = ' + CAST(CAST(GETDATE() AS DATE) AS VARCHAR) + ' ' 
PRINT (@SQL)
SET @iter = @iter + 1
END

SET @SQL = @SQL + ' SELECT * FROM blah'
EXEC @SQL
+1

, , UDF. .

DECLARE @startDate DATETIME, @endDate DATETIME, @tmpStartDate DATETIME, @tmpEndDate DATETIME 
SET @startDate = '1/1/2016'
SET @endDate = '6/1/2016'


SET @tmpStartDate = @startDate
SET @tmpEndDate = @endDate
DECLARE @dateTbl TABLE (startDate DATETIME, endDate DATETIME)

WHILE (DATEPART(YEAR, @tmpStartDate) >= 2013)
BEGIN
    INSERT INTO @dateTbl VALUES (@tmpStartDate, @tmpEndDate)
    SET @tmpStartDate = DATEADD(year, -1, @tmpStartDate)
    SET @tmpEndDate = DATEADD(year, -1, @tmpStartDate)
END

SELECT * FROM @dateTbl
+1

, , " DECLARE ?", - , , SQL. , , , declare. ( ), . . .

DECLARE @iter INT = 1, @SQL VARCHAR(MAX) = '', @MoreSQL VARCHAR(MAX) = '';

WHILE @iter < 11
BEGIN
    SET @SQL += 'DECLARE @NewDate' + CAST(@iter AS VARCHAR(2)) + ' DATE = GETDATE() '

    SET @iter += 1
END

SET @iter = 1

WHILE @iter < 11
BEGIN
    SET @MoreSQL  += 'SELECT @NewDate' + CAST(@iter AS VARCHAR(2)) + ' '

    SET @iter += 1
END

SET @SQL += @MoreSQL

EXEC (@SQL)
+1

/ 4 :

DECLARE @START DATETIME, @END DATETIME

SET @START='2016-01-01'
SET @END='2016-06-01'

DECLARE @DATES TABLE (STARTDATE DATETIME, ENDDATE DATETIME)

INSERT INTO @DATES (STARTDATE, ENDDATE)
SELECT @START,@END UNION
SELECT DATEADD(YY,-1,@START),DATEADD(YY,-1,@END) UNION
SELECT DATEADD(YY,-2,@START),DATEADD(YY,-2,@END) UNION
SELECT DATEADD(YY,-3,@START),DATEADD(YY,-3,@END)

SELECT * FROM @DATES ORDER BY STARTDATE

:

DECLARE @START DATETIME, @END DATETIME

SET @START='2016-01-01'
SET @END='2016-06-01'

DECLARE @CURDATE DATETIME
DECLARE @DATES TABLE (DATEVAL DATETIME)

SET @CURDATE=@START

WHILE @CURDATE<=@END
BEGIN
    INSERT INTO @DATES (DATEVAL)
    SELECT @CURDATE UNION
    SELECT DATEADD(YY,-1,@CURDATE) UNION
    SELECT DATEADD(YY,-2,@CURDATE) UNION
    SELECT DATEADD(YY,-3,@CURDATE) 

    SET @CURDATE=DATEADD(DD,1,@CURDATE)
END

SELECT * FROM @DATES ORDER BY DATEVAL

@START 2013 ...

DECLARE @START DATETIME, @END DATETIME

SET @START='2016-01-01'
SET @END='2016-06-01'

DECLARE @DATES TABLE (STARTDATE DATETIME, ENDDATE DATETIME)

DECLARE @CURYEAR INT
SET @CURYEAR=YEAR(@START)

WHILE @CURYEAR>= 2013 
BEGIN
    INSERT INTO @DATES (STARTDATE, ENDDATE)
    SELECT @START,@END 

    SET @START = DATEADD(YY,-1,@START)
    SET @END = DATEADD(YY,-1,@END)
    SET @CURYEAR=YEAR(@START)
END
SELECT * FROM @DATES ORDER BY STARTDATE
0

More articles:


All Articles