SQL Server : insert data into temporary table using a variable

Question

I am trying to insert the result in @date into temporary table, here is my co

DECLARE @MinDate DATE = CONVERT(VARCHAR(15), DATEADD(month, DATEDIFF(MONTH, 0, GETDATE()), 0), 101), 
        @MaxDate DATE = CONVERT(VARCHAR(15), EOMONTH(GETDATE()), 101),
        @dayInMonth VARCHAR(15);

DECLARE @date DATE; 
DECLARE @counter INT = 0;

DECLARE my_cursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR 
     SELECT TOP 
         (Datediff(day, @MinDate, @MaxDate) + 1) Date = Dateadd(day, Row_number() OVER(ORDER BY a.object_id) - 1, @MinDate) 
     FROM   
         sys.all_objects a 
     CROSS JOIN 
         sys.all_objects b; 

OPEN my_cursor 

FETCH next FROM my_cursor INTO @date 

WHILE @@FETCH_STATUS = 0 
BEGIN 
    IF( @counter = 15 ) 
    BEGIN
        --PRINT @date
        --PRINT @date -- here is where you get the 15th date
        IF DAY(@date) BETWEEN 10 AND 20
        BEGIN
            SET @date = DATEADD(DAY,(15-DATEPART(DAY,@date)),@date)
            PRINT CONVERT(VARCHAR(15), @date, 101)
            SET @counter = 0
        END

        IF @counter = 0 
        BEGIN
            SET @date = DATEADD(DAY,(30-DATEPART(DAY,@date)),@date)
            PRINT CONVERT(VARCHAR(15), @date, 101)
        END
    END 

    SET @counter = @counter + 1 

    FETCH next FROM my_cursor INTO @date 
END 

CLOSE my_cursor 
DEALLOCATE my_cursor

Is there anyway that I could achieved to insert the result into the temporary table? If so... how?

Any help will do. Thanks!

what do you actually want to insert actually ?? the dates u printed inside cursor?? — Newaz Sharif
– Newaz Sharif, Commented Mar 28, 2017 at 5:53
I don't see any temporary table (#someTable or ##someTable) in your code. Did you mean "table variable" (@someTable)? — stakx - no longer contributing
– stakx - no longer contributing, Commented Mar 28, 2017 at 6:05
Hi @stakx, I removed it, But actually I insert the #temptbl inside the if statement counter. I just did that because I want to make my code neat and easy to read from everybody since it was too messy earlier and also it is easy to put where the code is suppose to execute... Thats, it hope this would help. — Alvin Quezon
– Alvin Quezon, Commented Mar 28, 2017 at 6:08

Newaz Sharif · Accepted Answer · 2017-03-28 06:06:02Z

If I have understood your question properly this should work.

    DECLARE @MinDate DATE = CONVERT(VARCHAR(15),DATEADD(month, DATEDIFF(MONTH, 0, GETDATE()), 0), 101), 
        @MaxDate DATE = CONVERT(VARCHAR(15),EOMONTH(GETDATE()),101),
--DECLARE @MinDate DATE = '20170301', 
--        @MaxDate DATE = '20170331',
        @dayInMonth VARCHAR(15);
DECLARE @date DATE; 
DECLARE @counter INT = 0;

DECLARE my_cursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR 
  SELECT TOP (Datediff(day, @MinDate, @MaxDate) + 1) Date = 
  Dateadd(day, Row_number() 
                 OVER( 
                   ORDER 
  BY a.object_id) - 1, @MinDate) 
  FROM   sys.all_objects a 
         CROSS JOIN sys.all_objects b; 


Create table #temp (datevar varchar(15))
OPEN my_cursor 

FETCH next FROM my_cursor INTO @date 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      IF( @counter = 15 ) 
        BEGIN
        --PRINT @date
        --PRINT @date -- here is where you get the 15th date
          IF DAY(@date) BETWEEN 10 AND 20
              BEGIN
                SET @date = DATEADD(DAY,(15-DATEPART(DAY,@date)),@date)
                insert into #temp values (CONVERT(VARCHAR(15), @date, 101))
                PRINT CONVERT(VARCHAR(15), @date, 101)
                SET @counter = 0
              END
              IF  @counter = 0 
              BEGIN
                SET @date = DATEADD(DAY,(30-DATEPART(DAY,@date)),@date)
                insert into #temp values (CONVERT(VARCHAR(15), @date, 101))
                PRINT CONVERT(VARCHAR(15), @date, 101)
              END
        END 
      SET @counter = @counter + 1 
      FETCH next FROM my_cursor INTO @date 
  END 

CLOSE my_cursor 
DEALLOCATE my_cursor

select * from #temp

Thanks! This is what I want to achieve... Thank you so much!

Collectives™ on Stack Overflow

SQL Server : insert data into temporary table using a variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related