Get WeekNumber of Month from varchar date provided

Nody88 1 Reputation point
2020-08-27T14:44:37.677+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,541 questions
{count} votes

6 answers

Sort by: Most helpful
  1. Tom Phillips 17,511 Reputation points
    2020-08-27T14:51:57.943+00:00
    No comments

  2. Tom Phillips 17,511 Reputation points
    2020-08-27T17:56:16.143+00:00

    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

    No comments

  3. EchoLiu-MSFT 14,416 Reputation points
    2020-08-28T08:16:04.113+00:00

    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


  4. Ronen Ariely 13,786 Reputation points Microsoft MVP
    2020-08-28T12:35:49.257+00:00

    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)  
    
    No comments

  5. Guoxiong 7,681 Reputation points
    2020-08-28T21:40:31.717+00:00

    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?