0

I need a little help returning two values from the ISO week.

I have a list of over 1000 dates in a 'DD-MM-YYYY' format.

Using;

SELECT DATEPART(ISO_WEEK, SUBMITTED_DATE) AS ISOWK 
FROM [DATE_TABLE].SUBMITTED_DATE

I can successfully returns the ISO week for the dates listed.

What I need now is a way to use the ISO number to return the first and last dates of that ISO week , ideally in two separate columns.

Is this possible?

Thanks

eshirvana
  • 23,227
  • 3
  • 22
  • 38
Plump_Lil_Monk
  • 1
  • 2
    Date-related queries and reporting become a *lot* easier if you use a Calendar table. In that case finding what you want is as simple as a query to find the min and max date for a specific WeekNumber, semester, quarter, month or anything else you may need. – Panagiotis Kanavos Dec 15 '21 at 15:47
  • 4
    `a list of over 1000 dates in a 'DD-MM-YYYY' format.` dates have no format, they're binary values. If you store those dates as strings, you have a serious bug and your query won't work to begin with. It looks like `SUBMITTED_DATE` is a proper date though, that client applications on your machine display as `DD-MM-YYYY`. On a machine with a different locale the same date would appear in the way the locale specifies – Panagiotis Kanavos Dec 15 '21 at 15:47
  • ISO week is complicated. If USA can't you just subtract/add days based on day of week? – shawnt00 Dec 15 '21 at 15:56
  • Does this answer your question? [Get first day of week in SQL Server](https://stackoverflow.com/questions/7168874/get-first-day-of-week-in-sql-server) – Charlieface Dec 16 '21 at 09:32

0 Answers0