Dates 1 week and 1-2 years ago in SQL

Question

Dates 1 week and 1-2 years ago in SQL

JeremyU

SSChasing Mays

Points: 640
More actions
October 27, 2022 at 6:58 pm

#4105502
Hello,
I am trying to understand how it is possible to select 3 dates in SQL.
1) Last Saturday 1 week ago (20221015)
2) Last Saturday 1 year ago (20211023)
3) Last Saturday 2 years ago (20201024)
I know how to find this Saturday 20221022, I need to find those dates.
```
SELECT IUWEDT AS 'Date'
FROM t 
WHERE IUWEDT IN ('20221015', '20211023', '20201024') - but they need to adjust automatically.
```
I am planning to use it in WHERE clause with this Sat. that currently works.
```
WHERE CONVERT(DATETIME, CONVERT(CHAR(8), IUWEDT)) IN ( DATEADD(DAY, DATEDIFF(DAY, 5, GETDATE()) / 7 * 7, 5)  -- last Saturday )
```
Please let me know if you know how to do this.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ed B Hall of Fame Points: 3669 More actions · Answer 1

This is one possible method. Identify the coming Saturday and go back 2 weeks, 53 weeks and 105 weeks.

DECLARE @DateKey1 INT,
        @DateKey2 INT,
        @DateKey3 INT


-- Closest Saturday in the future
SELECT CONVERT(CHAR,DATEADD(DAY,7-DATEPART(DW,GETDATE()), GETDATE()),112) AS ComingSaturday

-- 2 weeks, 53 weeks and 105 weeks before closest Saturday in the future
SELECT  @DateKey1 = CONVERT(CHAR,DATEADD(WEEK,-2,DATEADD(DAY,7-DATEPART(DW,GETDATE()), GETDATE())),112),
        @DateKey2 = CONVERT(CHAR,DATEADD(WEEK,-53,DATEADD(DAY,7-DATEPART(DW,GETDATE()), GETDATE())),112),
        @DateKey3 = CONVERT(CHAR,DATEADD(WEEK,-105,DATEADD(DAY,7-DATEPART(DW,GETDATE()), GETDATE())),112)

SELECT @DateKey1, @DateKey2, @DateKey3

SELECT..
FROM ...
WHERE ...IN ( @DateKey1, @Datekey2, @DateKey3)

JeremyU SSChasing Mays Points: 640 More actions · Answer 2

@Ed B thank you. I am not a 100% sure I understand what I need to do.

I pasted my whole query:

Let me know if that is what I need to do.

There are 2 problems with it though:

I receive an error "Arithmetic overflow error converting expression to data type datetime."
I don't think I can make mutiple select statements because I need to use this query for a data visualization tool, i don't think it can accept it like this.

Let me know if I misunderstood what I needed to do.

DECLARE @DateKey1 INT,
        @DateKey2 INT,
        @DateKey3 INT


-- Closest Saturday in the future
SELECT CONVERT(CHAR,DATEADD(DAY,7-DATEPART(DW,GETDATE()), GETDATE()),112) AS ComingSaturday

-- 2 weeks, 53 weeks and 105 weeks before closest Saturday in the future
SELECT  @DateKey1 = CONVERT(CHAR,DATEADD(WEEK,-2,DATEADD(DAY,7-DATEPART(DW,GETDATE()), GETDATE())),112),
        @DateKey2 = CONVERT(CHAR,DATEADD(WEEK,-53,DATEADD(DAY,7-DATEPART(DW,GETDATE()), GETDATE())),112),
        @DateKey3 = CONVERT(CHAR,DATEADD(WEEK,-105,DATEADD(DAY,7-DATEPART(DW,GETDATE()), GETDATE())),112)






SELECT distinct CASE WHEN  IUWHS# IN ( '51' , '52' , '55' , '56' , '57' , '58' , '59' , '60' , '61' , '62' , '63' , '64' , '65' , '67') THEN 'TNG' 
WHEN IUWHS# IN ( '41' , '43' , '44') THEN 'SOURCE' ELSE IUWHS# END AS 'DC',
IUWEDT  AS WEEK
,
SUM((CASE WHEN  IUITM# = '0107851490' AND IUWEDT = '20200704' AND IUWHS# = '02' THEN '224'
WHEN  IUITM# = '0107851490' AND IUWEDT = '20200704' AND IUWHS# = '22' THEN '79'
WHEN IUITM# = '0107851490' AND IUWEDT = '20200704' AND IUWHS# = '30' THEN '57'
ELSE IUONHD 
END )
* 
(CASE WHEN IUITM# = '0146941022' AND IUWEDT <= '20180908' THEN '5.196'
WHEN (ITCOST = 0 or ITCOST IS NULL) THEN ITRPRC * 1 - VNVDIS / 100
ELSE [ITCOST] END))-- cost
AS OHCOST
,@DateKey1, @DateKey2, @DateKey3

FROM t

WHERE CONVERT(DATETIME, CONVERT(CHAR(8), IUWEDT)) IN ( DATEADD(DAY, DATEDIFF(DAY, 5, GETDATE()) / 7 * 7, 5)  -- last Saturday
,@DateKey1, @DateKey2, @DateKey3)

AND  IUWHS# NOT IN ( '99' , '76' , '07' , '17' , '18' , '37' , '47' , '41',  '43', '44' , '78')
GROUP BY IUWHS#, IUWEDT

This reply was modified 2 years, 1 month ago by JeremyU.

Ed B Hall of Fame Points: 3669 More actions · Answer 3

What data type is IUWEDT? At the moment the variables are inetgers and your code for last Saturday is returning a datetime.

if IUWEDT is an integer try this, otherwise try the code below that uses DATE variables

DECLARE @DateKey1 INT, @DateKey2 INT,  @DateKey3 INT,  @DateKey4 INT

SELECT  @DateKey1 = CONVERT(CHAR,DATEADD(WEEK,-1,DATEADD(DAY,7-DATEPART(DW,GETDATE()), GETDATE())),112),
        @DateKey2 = CONVERT(CHAR,DATEADD(WEEK,-2,DATEADD(DAY,7-DATEPART(DW,GETDATE()), GETDATE())),112),
        @DateKey3 = CONVERT(CHAR,DATEADD(WEEK,-53,DATEADD(DAY,7-DATEPART(DW,GETDATE()), GETDATE())),112),
        @DateKey4 = CONVERT(CHAR,DATEADD(WEEK,-105,DATEADD(DAY,7-DATEPART(DW,GETDATE()), GETDATE())),112)

--SELECT @DateKey1, @DateKey2, @DateKey3, @DateKey4 

SELECT....
FROM....
WHERE  IUWEDT IN (@DateKey1, @DateKey2, @DateKey3, @DateKey4 )

If the variable need to be dates

DECLARE @DateKey1 DATE,
        @DateKey2 DATE,
        @DateKey3 DATE,
        @DateKey4 DATE

SELECT  @DateKey1 = CONVERT(DATE,DATEADD(WEEK,-1,DATEADD(DAY,7-DATEPART(DW,GETDATE()), GETDATE()))),
        @DateKey2 = CONVERT(DATE,DATEADD(WEEK,-2,DATEADD(DAY,7-DATEPART(DW,GETDATE()), GETDATE()))),
        @DateKey3 = CONVERT(DATE,DATEADD(WEEK,-53,DATEADD(DAY,7-DATEPART(DW,GETDATE()), GETDATE()))),
        @DateKey4 = CONVERT(DATE,DATEADD(WEEK,-105,DATEADD(DAY,7-DATEPART(DW,GETDATE()), GETDATE())))

SELECT @DateKey1, @DateKey2, @DateKey3, @DateKey4 

SELECT....
FROM....
WHERE CONVERT(DATE, CONVERT(CHAR(8), IUWEDT)) IN (@DateKey1, @DateKey2, @DateKey3, @DateKey4 )

JeremyU SSChasing Mays Points: 640 More actions · Answer 4

JeremyU

SSChasing Mays

Points: 640

October 28, 2022 at 1:38 am

#4105610

@EdB thank you. It works. I appreciate your help.