June 12, 2019 at 3:43 pm
so I have a third party database I do an ETL to another third party system. One of the fields in the extract database table is a PeriodID field. unfortunately this third party application uses a "name" i.e. January, February, March etc etc. the system I am loading to needs an EOM date. i.e. 1/31/2019 2/28/2019 3/31/2019 etc. etc.
I did this brute force code that works just fine, but would require annual maintenance. Can anyone help me with a better solution?
Case
When PeriodID = 'January' THEN '1/31/2019'
When PeriodID = 'February' THEN '2/28/2019'
When PeriodID = 'March' THEN '3/31/2019'
When PeriodID = 'April' THEN '4/30/2019'
When PeriodID = 'May' THEN '5/31/2019'
When PeriodID = 'June' THEN '6/30/2019'
When PeriodID = 'July' THEN '7/31/2019'
When PeriodID = 'August' THEN '8/31/2019'
When PeriodID = 'September' THEN '9/30/2019'
When PeriodID = 'October' THEN '10/31/2019'
When PeriodID = 'November' THEN '11/30/2019'
Else '12/31/2019' END
AS PeriodEndDate,
June 12, 2019 at 4:08 pm
Something along these lines:
Declare @jan01 date
Set @jan01 = Dateadd(Year, Datediff(Year, 0, GETDATE()), 0)
select
Case Left(PeriodID, 3)
When 'Jan' THEN Dateadd(Day, -1, Dateadd(Month, 1, @jan01), 0)
When 'Feb' THEN Dateadd(Day, -1, Dateadd(Month, 2, @jan01), 0)
When 'Mar' THEN Dateadd(Day, -1, Dateadd(Month, 3, @jan01), 0)
When ...
END
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".
June 12, 2019 at 4:08 pm
Can't you just concatenate Year?
When PeriodID = 'January'
THEN '1/31/' + CAST(YEAR(CURRENT_TIMESTAMP) AS CHAR(4))
--Vadim R.
June 12, 2019 at 4:53 pm
So I tried the date function
Dateadd(Day, -1, Dateadd(Month, 1, @jan01), 0)
and I get an error message
The DateAdd function requires 3 arguments.
June 12, 2019 at 4:54 pm
yes as you were posting I was searching the internet and found the year function. I did not do the CHAR(4) as you did so your solution worked.
June 12, 2019 at 5:11 pm
however if I just concatenate the year, then every 4 years I need to update for the leap year affect.
June 12, 2019 at 5:12 pm
I think the first solution is the best for me in the instance. As it would account for year and leap year changes. but I keep getting an error message
The DateAdd function requires 3 arguments.
I have not figured out how to modify it to work. still working on it.
June 12, 2019 at 5:14 pm
never mind got it. thanks
When 'Jan' THEN Dateadd(Day, -1, Dateadd(Month, 1, @jan01))
June 12, 2019 at 5:33 pm
Oops, yep, sorry. A copy/paste where I accidentally left the ", 0" at the end.
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".
June 12, 2019 at 5:59 pm
Checking for leap year is fairly easy:
DECLARE @Date AS DATE = '20120101'
IF ISDATE(CAST(YEAR(@Date) AS CHAR(4)) + '0229') = 1
PRINT 'Leap'
ELSE
PRINT 'Norm'
--Vadim R.
June 12, 2019 at 6:22 pm
You could also do this:
Declare @firstOfYear date = dateadd(year, datediff(year, 0, getdate()), 0);
Select Case PeriodID
When 'January' Then eomonth(@firstOfYear, 00)
When 'February' Then eomonth(@firstOfYear, 01)
When 'March' Then eomonth(@firstOfYear, 02)
When 'April' Then eomonth(@firstOfYear, 03)
When 'May' Then eomonth(@firstOfYear, 04)
When 'June' Then eomonth(@firstOfYear, 05)
When 'July' Then eomonth(@firstOfYear, 06)
When 'August' Then eomonth(@firstOfYear, 07)
When 'September' Then eomonth(@firstOfYear, 08)
When 'October' Then eomonth(@firstOfYear, 09)
When 'November' Then eomonth(@firstOfYear, 10)
When 'December' Then eomonth(@firstOfYear, 11)
End;
You can also generate the first of the year using: datefromparts(year(getdate()), 1, 1)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 12, 2019 at 7:04 pm
thanks for all the posts. I have it now, and it works great. I really appreciate all of you posting these options. As you can tell I am not a proficient SQL person, but I am learning. thank you.
June 12, 2019 at 8:56 pm
thanks for all the posts. I have it now, and it works great. I really appreciate all of you posting these options. As you can tell I am not a proficient SQL person, but I am learning. thank you.
Excellent. Please post the code your using now. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2019 at 1:27 am
You can also use the following, which will take care of teh leap year
DECLARE @Year int = 2019;
SELECT PeriodID, EOMONTH(PeriodID + ' 01 ' + CONVERT(VARCHAR(4), @Year)) AS PeriodEndDate
June 13, 2019 at 3:06 am
You can also use the following, which will take care of teh leap year
DECLARE @Year int = 2019;
SELECT PeriodID, EOMONTH(PeriodID + ' 01 ' + CONVERT(VARCHAR(4), @Year)) AS PeriodEndDate
True enough but this is a 2008 forum.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply