June 13, 2019 at 3:22 am
Much like Des Norton, I don't see the need for CASE in this case (pun intended).
--===== Create a table of random PeriodID's.
-- This is NOT a part of the solution. We're just building test data here.
DROP TABLE #TestTable
SELECT TOP 1000000
PeriodID = DATENAME(mm,DATEADD(dd,ABS(CHECKSUM(NEWID())%365),'2019'))
INTO #TestTable
FROM sys.all_columns pc1
CROSS JOIN sys.all_columns pc2
;
--===== Demo one CASE-less solution.
-- The year comes from whatever year GETDATE() returns. You can change that to a variable if needed.
-- This works on SS 2005 and up and does NOT require anything special for Leap Years
-- If the target can handle dates with midnight times, remove the CONVERT for a bit of extra performance.
SELECT PeriodID
,EOMDate = CONVERT(CHAR(10),DATEADD(dd,-1,DATEADD(mm,1,PeriodID + DATENAME(yy,GETDATE()))),101)
FROM #TestTable
;
--===== This does the same thing for SS 2012 and up.
SELECT PeriodID
, ConvertedToEOM = EOMONTH(CONVERT(DATE,PeriodID+' '+ DATENAME(yy,GETDATE())))
FROM #TestTable
;
And, yeah... the screwball concatenation with no day and no spacing works just fine. For example...
SELECT CONVERT(DATETIME,'January2019');
... returns 2019-01-01 00:00:00.000
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2019 at 4:29 am
DesNorton wrote: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 PeriodEndDateTrue enough but this is a 2008 forum.
Oh snap.
Note to self. Don't answer posts as 02h30
June 13, 2019 at 4:54 pm
ok thanks for all the posts. First this is a view, and I now know declare statements do not work in a view
so I updated the statement to this, but it gives the date as 2019-04-30 00:00:00:000
however I need 04/30/2019
Can someone update my SQL to accomplish this?
Case
When PeriodID = 'January' THEN Dateadd(Day,-1,Dateadd(Month,1, Dateadd(Year,Datediff(Year,0,Getdate()),0)))
When PeriodID = 'February' THEN Dateadd(Day,-1,Dateadd(Month,2, Dateadd(Year,Datediff(Year,0,Getdate()),0)))
When PeriodID = 'March' THEN Dateadd(Day,-1,Dateadd(Month,3, Dateadd(Year,Datediff(Year,0,Getdate()),0)))
When PeriodID = 'April' THEN Dateadd(Day,-1,Dateadd(Month,4, Dateadd(Year,Datediff(Year,0,Getdate()),0)))
When PeriodID = 'May' THEN Dateadd(Day,-1,Dateadd(Month,5, Dateadd(Year,Datediff(Year,0,Getdate()),0)))
When PeriodID = 'June' THEN Dateadd(Day,-1,Dateadd(Month,6, Dateadd(Year,Datediff(Year,0,Getdate()),0)))
When PeriodID = 'July' THEN Dateadd(Day,-1,Dateadd(Month,7, Dateadd(Year,Datediff(Year,0,Getdate()),0)))
When PeriodID = 'August' THEN Dateadd(Day,-1,Dateadd(Month,8, Dateadd(Year,Datediff(Year,0,Getdate()),0)))
When PeriodID = 'September' THEN Dateadd(Day,-1,Dateadd(Month,9, Dateadd(Year,Datediff(Year,0,Getdate()),0)))
When PeriodID = 'October' THEN Dateadd(Day,-1,Dateadd(Month,10, Dateadd(Year,Datediff(Year,0,Getdate()),0)))
When PeriodID = 'November' THEN Dateadd(Day,-1,Dateadd(Month,11, Dateadd(Year,Datediff(Year,0,Getdate()),0)))
Else Dateadd(Day,-1,Dateadd(Month,12, Dateadd(Year,Datediff(Year,0,Getdate()),0))) END
AS PeriodEndDate,
June 13, 2019 at 5:01 pm
Use Convert function. It's in Jeff's example as well.
CONVERT(CHAR(10), <your date here>, 101)
--Vadim R.
June 13, 2019 at 5:37 pm
There is no need for the case statement.
This should do the trick
SELECT CONVERT(VARCHAR(10), DATEADD(DAY, -1, DATEADD(MONTH, 1, 'February' + ' 01 ' + CONVERT(VARCHAR(4), YEAR(GETDATE())))), 101) AS PeriodEndDate
June 13, 2019 at 5:44 pm
ok thanks for all the posts. First this is a view, and I now know declare statements do not work in a view so I updated the statement to this, but it gives the date as 2019-04-30 00:00:00:000 however I need 04/30/2019 Can someone update my SQL to accomplish this? Case When PeriodID = 'January' THEN Dateadd(Day,-1,Dateadd(Month,1, Dateadd(Year,Datediff(Year,0,Getdate()),0))) When PeriodID = 'February' THEN Dateadd(Day,-1,Dateadd(Month,2, Dateadd(Year,Datediff(Year,0,Getdate()),0))) When PeriodID = 'March' THEN Dateadd(Day,-1,Dateadd(Month,3, Dateadd(Year,Datediff(Year,0,Getdate()),0))) When PeriodID = 'April' THEN Dateadd(Day,-1,Dateadd(Month,4, Dateadd(Year,Datediff(Year,0,Getdate()),0))) When PeriodID = 'May' THEN Dateadd(Day,-1,Dateadd(Month,5, Dateadd(Year,Datediff(Year,0,Getdate()),0))) When PeriodID = 'June' THEN Dateadd(Day,-1,Dateadd(Month,6, Dateadd(Year,Datediff(Year,0,Getdate()),0))) When PeriodID = 'July' THEN Dateadd(Day,-1,Dateadd(Month,7, Dateadd(Year,Datediff(Year,0,Getdate()),0))) When PeriodID = 'August' THEN Dateadd(Day,-1,Dateadd(Month,8, Dateadd(Year,Datediff(Year,0,Getdate()),0))) When PeriodID = 'September' THEN Dateadd(Day,-1,Dateadd(Month,9, Dateadd(Year,Datediff(Year,0,Getdate()),0))) When PeriodID = 'October' THEN Dateadd(Day,-1,Dateadd(Month,10, Dateadd(Year,Datediff(Year,0,Getdate()),0))) When PeriodID = 'November' THEN Dateadd(Day,-1,Dateadd(Month,11, Dateadd(Year,Datediff(Year,0,Getdate()),0))) Else Dateadd(Day,-1,Dateadd(Month,12, Dateadd(Year,Datediff(Year,0,Getdate()),0))) END AS PeriodEndDate,
Again, there is no need for the CASE statement. See my previous post.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2019 at 6:10 pm
got it thanks
June 14, 2019 at 2:02 pm
Again many thanks for all your help. this was the final solution
CONVERT(CHAR(10), Dateadd(dd,-1, Dateadd(mm,1,PeriodID + DateName(yy,Getdate()))), 101) AS PeriodEndDate
June 14, 2019 at 11:29 pm
Sorry... duplicate post removed.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 14, 2019 at 11:30 pm
Thanks for taking the time to post back, Randy. Since you're one of the folks that needs to support it, do you know how it works?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply