Get WeekNumber of Month from varchar date provided

Nody88 1

Hi I need to get Week number (in a month i.e 1/2/3 Not week in year)of a particular datetime.

I am trying to get weeknumber

example varchar datatype datetime values as below

'19/08/20 07:23:42'

'03/08/20 09:28:09'

Note i tried online solutions but any functions datepart/format/conver/cast is failing on the value '19/08/20 07:23:42' but works fine for '03/08/20 09:28:09'

Please provide a Generic solution to get WeekNumber of Month for any given date value along with proper convert etc function ASAP.

Williams, Jeffrey A 481 Reputation points

2020-08-27T16:03:33.753+00:00
The problem is with your varchar date string - any way you can get that value as an actual datetime?

From the format - it looks like you are getting the string in DD/YY/MM HH:MM:SS format. Lookup CONVERT in BOL and see which format and use the appropriate convert:

Select dt.DateValue , WeekInMonth = datediff(day, dateadd(month, datediff(month, 0, dt.DateValue), 0), dt.DateValue) / 7 + 1 From (Values (convert(datetime, '19/08/20 07:23:42', 3))) As dt(DateValue)
Viorel 112.5K Reputation points

2020-08-28T16:27:01.1+00:00

Which day is the first day of the week (Monday, Sunday)?
Ronen Ariely 15,096 Reputation points

2020-08-28T21:30:02.577+00:00

Which day is the first day of the week (Monday, Sunday)?

This is great question

This is why the function DATEPART with the datepart parameter week (wk, ww) or weekday (dw) is a non-deterministic function (may return different results each time they are called, depending on the session setting) and therefore, should never be used as it is (In my opinion)
EchoLiu-MSFT 14,571 Reputation points

2020-08-31T09:04:39.797+00:00

Hi @Nody88 ，
Has your problem been solved? If it is solved,please remember to accept the answers if they helped. This can help others who encounter similar problems..

Best Regards
Echo

6 answers

Jeff Moden 11

Just temporarily change the session-level date format like this...

--===== This simulates the issue the OP described.  
  PRINT 'Using MDY...';  
    SET DATEFORMAT MDY --Session level setting here.  
;  
 SELECT CONVERT(DATETIME,'19/08/20 07:23:42'); --This fails.  
;  
GO  
  PRINT REPLICATE('-',119);  
GO  
--===== This is the simple fix within a proc  
  PRINT 'Using DMY for the session level setting...';  
  PRINT 'Doe NOT change the server level setting!'  
;  
    SET DATEFORMAT DMY --Session level setting here.  
;  
 SELECT CONVERT(DATETIME,'19/08/20 07:23:42'); --Now it works.  
;  
GO

See the following link for more information.
REF: https://learn.microsoft.com/en-us/sql/t-sql/statements/set-statements-transact-sql