CODEWITHSUNDEEP
sql-servert-sqlsql-server-2016
user5950313
user5950313

Reputation:

Add week in a table within SQL Server

I am looking to add a computed column to a table that contains dates

select datepart(wk, '01-01-2020')
1

select datepart(wk, '12-29-2019')
53----result wanted 52

Upvotes: 0

Views: 67

Answers (1)

gotqn
gotqn

Reputation: 43656

Try ISO_WEEK:

select datepart(iso_week,'01-01-2020')

select datepart(iso_week,'12-29-2019')

From the docs:

ISO 8601 includes the ISO week-date system, a numbering system for weeks. Each week is associated with the year in which Thursday occurs. For example, week 1 of 2004 (2004W01) covered Monday, 29 December 2003 to Sunday, 4 January 2004. European countries / regions typically use this style of numbering. Non-European countries / regions typically do not use it.

Note: the highest week number in a year could be either 52 or 53.

You can play with SET DATEFIRST, too:

SET DATEFIRST 1

SELECT @@DATEFIRST

select datepart(week,'01-01-2020') -- 1

select datepart(week,'12-29-2019') -- 52

select datepart(week,'12-30-2019') -- 53

If you are not able to populate this value on insert/update and you are not sure who is going to perform operation over the table, you can create a trigger like this:

CREATE TABLE [dbo].[X]
(
    [ID] INT IDENTITY(1,1)
   ,[Date] DATETIME2
   ,[Week] TINYINT
);

GO

CREATE TRIGGER [T] ON [dbo].[X] AFTER INSERT, UPDATE
AS
BEGIN

    SET DATEFIRST 1;

    UPDATE [dbo].[X]
    SET [Week] = DATEPART(WEEK,A.[Date])
    FROM [dbo].[X] A
    INNER JOIN inserted B
        ON A.[ID] = B.[ID]

END;

GO

INSERT INTO [dbo].[X] ([Date])
VALUES ('01-01-2020')
      ,('12-29-2019')
      ,('12-30-2019');

SELECT *
FROM [dbo].[X];

Upvotes: 1

Related Questions