Date Dim Week population

Sanjeewan Kumar 1 Reputation point
2021-05-07T11:26:22.357+00:00

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.

94759-image.png

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
23,838 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Loski, Russel 1 Reputation point
    2021-05-07T12:24:52.443+00:00

    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))
    
    No comments