Date Dim Week population

Question

Hello All

In MySQL Table I have field Date with value like 20210101 , I want to populate 3 new columns ,wkstartdate,Week,day
irrespective week should start from ist day of month till month End, New month week should start with again W1 so on.

Answer 1

I am going to answer this using recent SQL Server syntax. You will need to figure out how for older SQL Server and for MySQL. Some of the principals are the same.

UPDATE datetable
set wkstartdate = DATEADD(day, - DATEPART(day, cast(cast(date as varchar(8)) as date)) % 7,cast(cast(date as varchar(8)) as date))
, Week = 'W' + CAST(DATEPART(day, cast(cast(date as varchar(8)) as date)) / 7 as varchar(1))
, day = DATEPART(day, cast(cast(date as varchar(8)) as date))