CODEWITHSUNDEEP
sqlsql-server
Nareik
Nareik

Reputation: 3

Get week number from a list of dates in SQL

I'm trying to get listed the corresponding week number from the dates that result from this query. 

select Date,Time,EndDate,EndTime
FROM Test
WHERE (StartDate >= '01.01.2019')
ORDER BY StartDate

Basically, I want adding to the end column the week number from this query. I can't edit the database in anyway, I just want to extract the week number from the dates and have it as a column at the end of my results.

Sample data below:

Results

Upvotes: 0

Views: 3207

Answers (6)

Igor Aldabaev
Igor Aldabaev

Reputation: 11

If you use Vertica, try this

 date_part('ISODOW', date)

'ISODOW' - The ISO day of the week, an integer between 1 and 7 where Monday is 1.

Upvotes: 0

DDS
DDS

Reputation: 2478

In UK ISO week is used: the first year's week is the one including 4th of Jan.

So:

set datefirst 1 --this sets Monday as first day of the week 
set dateformat dmy -- nosrmal date format
select Date,Time,EndDate,EndTime,datepart(iso_week,date)as week
FROM Test
WHERE (StartDate >= '01.01.2019')
ORDER BY StartDate

Remember that first days of Jan may be 52nd or 53rd week of previous year and also last day of December may belong to first week of new year.

the check to see the week number and postponed to the yeear it belongs to is the following:

week_and_year = case when datepart(iso_week,date)>=52 and month(date)=1 
                           then concat(year(date)-1,datepart(iso_week,date))
                      when datepart(iso_week,date)=1 and month(date)=12
                           then concat(year(date)+1,datepart(iso_week,date))
                      else concat(year(date),datepart(iso_week,date))
                      end

Upvotes: 2

Chris Hackett
Chris Hackett

Reputation: 449

I usually use the ROW_NUMBER() function to accomplish this:

select 
    Date,
    Time,
    EndDate,
    EndTime, 
    ROW_NUMBER() over (partition by year(EndDate), datepart(weekday, EndDate) order by EndDate) as WeekNumInYear
FROM Test
WHERE 
    (StartDate >= '01.01.2019')
ORDER BY 
    StartDate

Upvotes: -2

maddy
maddy

Reputation: 50

use datepart(wk,date):-

select Date,Time,EndDate,EndTime,datepart(wk,date)as week
FROM Test
WHERE (StartDate >= '01.01.2019')
ORDER BY StartDate

Upvotes: 1

Caldazar
Caldazar

Reputation: 3812

Just use datepart function:

select datepart(week, Date), Date,Time,EndDate,EndTime
FROM Test
WHERE (StartDate >= '01.01.2019')
ORDER BY StartDate

Upvotes: 2

Gary Dixon
Gary Dixon

Reputation: 3

SELECT DATEPART(WEEK,GETDATE()-14)
SELECT DATEPART(WEEK,GETDATE()-7)
SELECT DATEPART(WEEK,GETDATE())
SELECT DATEPART(WEEK,GETDATE()+7)
SELECT DATEPART(WEEK,GETDATE()+14)

Upvotes: 0

Related Questions