Declare a variable which converts Date data type to Integer

Question

In SQL Server, the following code gives me the date of Sunday last week from whatever date it is today:

DECLARE @dt date = GETDATE();
SELECT dateadd(week,datediff(week,6,@dt),6);

I need to declare a variable from the date given and make that variable an integer. I have tried things like:

DECLARE @last_sunday INT;
SELECT dateadd(week,datediff(week,6,@dt),6) INTO @last_sunday FROM xxx;

But the problem is that I don't understand which table I should write after FROM. In my interpretation the line:

dateadd(week,datediff(week,6,@dt),6)

is some sort of calculation rather than collecting a value from a data table. How can I make last Sunday's date into a variable that is an integer?

Answer 1

You need to do something like this:

Step 1

DECLARE @date DATE = dateadd(week,datediff(week,6,GETDATE()),6)

Step 2

DECLARE @dateInt AS INT = REPLACE(@date, '-', '')

In the first step, you are getting the correct date value in a date data type. Given that the date has the hyphens, you are then using the REPLACE function in step 2 to remove them and at the same time converting your date to an integer value.

Finally, to make sure that steps 1 and 2 work, you can run the following query:

SELECT @dateInt

For today, this will get you the following value: 20210530