1

I am using DATEPART(WEEK,mydate) to return the week number of my date, but when the week is only single number i.e. at the beginning of the year I want to format it as 01 instead of 1.

I am using SQL Server 2014, I have tried using the option format(datepart(week,mydate),'ww') but am just getting ww as my answer which as you can tell isn't quite right

Arulkumar
  • 12,966
  • 14
  • 47
  • 68
PJD
  • 743
  • 2
  • 12
  • 38

2 Answers2

2

Try this 

 SELECT RIGHT('0' + RTRIM(DATEPART(WEEK,GETDATE())), 2)
--selects 23
 SELECT RIGHT('0' + RTRIM(DATEPART(WEEK,'2017-01-01 03:58:13.110')), 2)
-- selects 01
Shivam Tiwari
  • 345
  • 3
  • 11
1

You can use FORMAT with d2

SELECT FORMAT(datepart(week,'2017-01-05'), 'd2')
-- RETURN 01

SELECT FORMAT(datepart(week,GETDATE()), 'd2')
-- RETURN 23
Arulkumar
  • 12,966
  • 14
  • 47
  • 68
  • 1
    That's brilliant Arulkumar, exactly what I wanted and I've learnt something new this morning. Thanks – PJD Jun 07 '17 at 08:07