CODEWITHSUNDEEP
sqlsql-server
ambrrrgris
ambrrrgris

Reputation: 157

Convert particular string to last day of month

I've got a dataset in SQL Server and I need to transform one of the columns.

The string (nvarchar(max)) is a combination of the year and week of year, so the 8th week of 2021 looks like 202108, the 32nd week of 2019 looks like 201932, and so on.

What I need is to convert this into the last day of the month of the given week, so for 202302, I need 01/31/2023, in datetime format. I've tried a few different methods like CAST() to no avail.

Upvotes: 1

Views: 91

Answers (3)

RF1991
RF1991

Reputation: 2265

data Thanks John Cappelletti

Declare @YourTable Table ([SomeCol] varchar(50));
Insert Into @YourTable
([SomeCol]) Values 
 ('202108')
,('201932')
,('202302');

query

SELECT 
DATEADD(wk,cast(right([SomeCol],2) as int),DATEADD(yy,cast((left([SomeCol],4)) as int)-1900,0))-1 AS WeekEnd
from  @YourTable

dbfiddle

Upvotes: -1

Joel Coehoorn
Joel Coehoorn

Reputation: 415690

in datetime format

Once more for those in back: datetime values do not have a human-readable format. Anything you see indicating otherwise is a convenience provided by the tooling.

Your goal at the database level should be to return a basic datetime value, and let the client code worry about the formatting. But we can do that:

EOMonth(DATEADD(week, cast(right([MyColumn],2) as int),CONVERT(datetime, left([MyColumn], 4) + '0101')))

Of course, the main key here is the EOMonth() function. Everything else is just string parsing

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81930

Perhaps this will help, but it really depends on how you count weeks. Perhaps a calendar table would be a better option.

Declare @YourTable Table ([SomeCol] varchar(50))  Insert Into @YourTable Values 
 (202108)
,(201932)
,(202302)
 
Select * 
      ,NewVal = EOMonth(dateadd(week,try_convert(int,right(SomeCol,2)),convert(date,left(SomeCol,4)+'0101')))
 from @YourTable

Results

SomeCol NewVal
202108  2021-02-28
201932  2019-08-31
202302  2023-01-31

Upvotes: 3

Related Questions