October 10, 2022 at 12:16 pm
Hi Team,
I've below requirement. The source has date in YYYYMM format.
If I run job in 2022, it should extract data between 202108 and 202206
If I run job in 2023, it should extract data between 202207 and 202306
If I run job in 2024, it should extract data between 202307 and 202406
and so on...
Not getting help on how I can set the date range like this? For first year, I can manage by hardcoding parameter value, but from 2023, I need to have expression in SQL to pickup required date range everytime I run.
October 10, 2022 at 12:51 pm
Maybe:
SELECT *
FROM YourTable
WHERE ExtractDate >= CONVERT(char(6), DATEADD(month, -6, CAST(YEAR(CURRENT_TIMESTAMP) AS char(4))), 112)
AND ExtractDate < CONVERT(char(6), DATEADD(month, 6, CAST(YEAR(CURRENT_TIMESTAMP) AS char(4))), 112)
October 10, 2022 at 1:05 pm
Another maybe:
DECLARE @RunDateYr INT;
--SET @RunDateYr = YEAR(GETDATE()) * 100;
--SET @RunDateYr = YEAR('20221010') * 100;
--SET @RunDateYr = YEAR('20231010') * 100;
SET @RunDateYr = YEAR('20241010') * 100;
DECLARE @RangeStart INT
,@RangeEnd INT;
SELECT @RangeStart = @RunDateYr - 100 + IIF(@RunDateYr = 202200, 8, 7)
,@RangeEnd = @RunDateYr + 6;
SELECT *
FROM SomeTable
WHERE SomeInt
BETWEEN @RangeStart AND @RangeEnd;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 10, 2022 at 2:11 pm
SELECT whatever
FROM dbo.table_name
WHERE date_column BETWEEN CAST(YEAR(GETDATE()) - 1 AS varchar(4)) + '07' AND
CAST(YEAR(GETDATE() AS varchar(4)) + '06'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 10, 2022 at 2:34 pm
What is the data type for the source date columns? char(6)? int? Or are they actually date datatype?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply