1

I'm trying to format a date string from YYYY-MM-DD to YYYY-WW (year-week) in T-SQL. I've tried browsing online on how to do it without luck. I know it can be done fairly straightforward in MySQL using DATE_FORMAT(), however I haven't had luck in finding a corresponding way in T-SQL.

Example: 2017-01-01 to 2017-01

Artem
  • 751
  • 2
  • 10
  • 30
  • By `YYYY-WW` you mean for digits year and two digits week of year, right? What if the year starts on Wednesday? Does the first week of the year starts at January first, or does it start on the first Sunday (or Monday) of that year? – Zohar Peled Sep 02 '19 at 07:39
  • Yes, exactly. It doesn't matter if the year starts midweek. Weeks should be Mon-Sun. If the first day of the year is Wednesday, week 01 will only have 5 days in it (Wed, Thur, Fri, Sat, and Sun). – Artem Sep 02 '19 at 07:43

1 Answers1

3

If the source data is a string, you first need to convert it to a date. If it's a date, it doesn't have a display format.

So let's start with the simpler case and move on to the more complicated one: Assuming the source data is of type Date (DateTime, DateTime2 and DateTimeOffset will also work fine for this) - you can use a combination of concat, datepart and right:

DECLARE @source Date = '2017-01-01';

SELECT  CONCAT(DATEPART(YEAR, @source), '-', RIGHT(CONCAT('00', DATEPART(WEEK, @source)), 2)) As TargetDate;

You use RIGHT(CONCAT('00', <SomeIntValue>), 2) to ensure a two digit result.

The slightly more complicated case is when the source data is a string.
In this case, with this specific format, I would use left to get the year part, and cast(@DateString as date) to convert the string to a date. With any other format (except yyyymmdd) you should use convert with the appropriate style to convert a string to a date. (Why?)

DECLARE @DateString char(10) = '2017-01-01';

SELECT CONCAT(LEFT(@DateString, 4), '-', RIGHT(CONCAT('00', DATEPART(WEEK, CAST(@DateString as date))), 2)) As TargetDate;

Both will result with 2017-01.

One last thing - to address the conversation we had in the comments - if you wanted to count the first week of the year starting with the first Monday, you would need to change the WEEK date part identifier to a ISO_WEEK.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121