Get WeekNumber of Month from varchar date provided

Question

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.

Answer 1

Use a calendar table:
https://weblogs.sqlteam.com/dang/2010/07/19/calendar-table-and-datetime-functions/

Answer 2

Just to be clear.

The dates you provided are apparently in the format of DD/MM/YYYY. Your default server format must be MM/DD/YYYY. That is why you are getting an error casting the string to a date.

In order to convert DD/MM/YYYY format to a date, you must use CONVERT with a conversion type. Please see:
https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15#date-and-time-styles

Answer 3

Hi @Nody88 ,

'19/08/20 07:23:42' but works fine for '03/08/20 09:28:09'，because '19/08/20 07:23:42' is not a server supported time format.

Please try to change to'08/19/20 07:23:42' .

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Best Regards
Echo

Answer 4

Good day @Nody88

Let me add a bit to Tom's answer

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'

The main source of the issue is in your assumption that "but works fine for '03/08/20 09:28:09'"

Using the value "19/08/20" will work only if the setting fits the format dd/MM/yy. You get error since this is not the format which your session uses. The reason it is failing is because 19 cannot be the month number (after all we have only 12 months) and your setting is probably using the format MM/dd/yy

Using the value 03/08/20 will not raise error for anyone who use setting format MM/dd/yy but this also will not raise error for anyone using the setting format dd/MM/yy, since both 03 and 08 fits as month and as day, but you don't know if the convert worked as you expected.

It is better to get an error, and understand the issue then thinking it is working without even know if the string "03/08/20" is converted to 2020-08-03 or maybe it is converted to 2020-03-08 !!!

You must read the following post fully and understand what is displaying format vs stored format

https://ariely.info/Blog/tabid/83/EntryId/161/Date-displaying-format-vs-Date-storing-format.aspx

The short summarize is that you should never let the server do explicit format from string to date and vise versa! You should always use explicit CONVERT and declare the style of the string format

For example if your string is in format dd/MM/yy as I am guessing (this format is used in Israel for example) then you can simply use CONVERT with style 3

DECLARE @dateString nvarchar(20);  
SET @dateString = '19/08/20 07:23:42';  
SELECT CONVERT(DATETIME2, @dateString, 3)  

Answer 5

If the data part in the date string has the format dd/MM/yy, try this:

DECLARE @dateString varchar(20);
DECLARE @date datetime;
--SET @dateString = '03/08/20 09:28:09';
SET @dateString = '19/08/20 07:23:42';
SELECT @date = CONVERT(datetime2(0), '20' + SUBSTRING(@dateString, 7, 2) + '-' + SUBSTRING(@dateString, 4, 2) + '-' + LEFT(@dateString, 2) + + ' ' + RIGHT(@dateString, 8));
SELECT @date;

The week number of a month depends on which day as the first day of the week, i.e., Monday or Sunday? Does the first week start with the first day of the month?