Calculate the number of weeks between the initial date and the final date

I need a solution that takes the starting date and ending date as input and provides me with a list of week numbers between those dates. For instance, if I input 01/11/2019 as the starting date and 14/12/2019 as the ending date, the output will be:

1
2
3
4
5
1
2
3

(the first 5 weeks are for November and the next 3 weeks are for December)..... Now let's look at another example: If I provide the starting date as 14/11/2019 and the ending date as 14/12/2019, the output will be:

3
4
5
1
2
3

(the first 3 weeks are for November and the next 3 weeks are for December) ....

DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @CurrentDate AS DATETIME
SET @StartDate = '2019-11-01'
SET @EndDate = '2019-12-14'
SET @CurrentDate = @StartDate
WHILE (@CurrentDate < @EndDate)
BEGIN
Print datepart(day, datediff(day, 0, @CurrentDate)/7 * 7)/7 + 1
SET @CurrentDate = DATEADD(DAY, 7, @CurrentDate); 
END

Answer №1

Feel free to test out the script below:

DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @CurrentDate AS DATETIME
SET @StartDate = '2019-11-01'
SET @EndDate = '2019-12-14'
SET @CurrentDate = @StartDate


WHILE (@CurrentDate < @EndDate)
BEGIN

IF datepart(DD,@CurrentDate) <=7 
BEGIN 
    SET @CurrentDate = DATEADD(DD,-(DATEPART(DD,@CurrentDate)-1),@CurrentDate) 
END 


PRINT datepart(dd,@CurrentDate)/7  +1
SET @CurrentDate = DATEADD(DAY, 7, @CurrentDate); 
END

Answer №2

For optimal results, please utilize this code snippet for weekly data analysis with specified start and end dates.

DECLARE @StartDate   datetime, 
        @EndDate     datetime, 
        @CurrentDate datetime, 
        @IsTrue      BIT=0 

SET @StartDate = '2019-11-01' 
SET @EndDate = '2019-12-14' 
SET @CurrentDate=@StartDate 

WHILE ( @CurrentDate <= @EndDate ) 
  BEGIN 
      IF( Format(@CurrentDate, 'MM') != Format(@StartDate, 'MM') 
          AND @IsTrue = 0 ) 
        BEGIN 
            SET @CurrentDate=Dateadd(month, Datediff(month, 0, @CurrentDate), 0) 

            PRINT @CurrentDate 

            SET @IsTrue=1 
        END 

      SELECT CONVERT(VARCHAR(20), ( Datepart(week, @CurrentDate) - 
                                    Datepart(week, 
                                    Dateadd(day, 1, Eomonth( 
                                    @CurrentDate, -1))) ) + 1), 
             @CurrentDate, 
             Dateadd(day, 6, @CurrentDate) 

      PRINT( CONVERT(VARCHAR(20), ( Datepart(week, @CurrentDate) - 
                                    Datepart(week, 
                                    Dateadd(day, 1, Eomonth( 
                                    @CurrentDate, -1))) ) + 1) ) 

      SET @CurrentDate = Dateadd(day, 7, @CurrentDate); 
  END 

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Troubleshooting a mishap in setting up the change event for a dropdown in ASP.NET MVC

I am currently working on setting up a jQuery change event handler for a dropdown list in order to update a div called "payment" with HTML content returned from my controller action whenever a user selects a different value from the dropdown. Despite my b ...

Utilizing the navigator object in React Navigator and expo to enhance component functionality

My React Native application requires a redirection on a specific component. Within my main App.js component, I have the following code snippet: import React from "react"; import { Text } from "react-native"; import { createAppContainer } from "react-nav ...

Display previous messages in React JS chat when scrolling upwards

https://i.sstatic.net/mcJUp.png I am currently working on a chat application, as depicted in the image. Once the chat is initiated, it automatically scrolls down to display the most recent messages. My goal is to implement a feature where when a user sc ...

Tips for parsing JSON using JavaScript

I am looking to iterate through a JSON object that looks like {"key1":"val1","key2":"val2","key3":"val3"} in a loop. Here is my attempt: var inobj = '[{"key1":"val1","key2":"val2","key3":"val3"}]'; var obj = eval(inobj); for (var i = 0; i ...

What should I do to resolve the message 'Ignoring invalid configuration option passed to Connection' that I received?

This is the latest message: Warning - Invalid configuration option passed to Connection: createDatabaseTable. Currently a warning, future versions of MySQL2 will throw an error for passing invalid options. The application stops responding after enco ...

Guide to implementing a real-time countdown timer for days, hours, minutes, and seconds leading up to a specific date and time with JavaScript

Implement a dynamic countdown timer in JavaScript that counts up to a specific date. See the example image for reference: here ...

Import database from SQL script preserving Foreign Key Relationships

Currently, I am facing an issue while trying to restore a database using an SQL script due to foreign key constraints. The scenario involves migrating a MySQL database to PostgreSQL. Since the table creation syntax in MySQL differs significantly from Post ...

Error Message: Unable to Load User Profile Pictures from Microsoft Graph

Currently, I am developing an application that makes requests for user profile photos from Microsoft Graph. The process seems to be working smoothly as the request returns a 200 status code and a significant amount of data is retrieved. However, when I att ...

reveal concealed section and seamlessly glide to specified location inside the secret compartment

I have implemented a feature on my website where hidden divs are revealed one at a time, with the screen scrolling to display each specific div. While this code functions well, I am now looking to enhance it by opening the hidden div and smoothly scrolling ...

Tips for utilizing multiple ngFor directives for property binding within a single directive

After implementing the ng-drag and drop npm module with the draggable directive, I encountered an issue while trying to display a list of items from a 2D array using li elements. Since multiple ngFor's are not allowed in Angular, I needed to come up w ...

Retrieve the values that have repeated foreign keys

Imagine a scenario where we have customers and service providers. A customer can have multiple service providers (such as internet, phone, TV, etc.) and I want to identify customers who are using more than one provider. create table customers ( custom ...

Download button on Rshiny platform for collecting numerous files from different sources

I am on a quest for knowledge regarding the inclusion of a download button in my application that consolidates various files into a zip archive. Within my application, there are a timeline and a datatable, with files linked to entries on the datatable. Th ...

Discovering escape characters while iterating through a string in javascript

I have a situation where I must rearrange a string into an array for a unique user display. Whenever encountering an escape character (such as \n for a new line), it needs to be added as a separate item in the array. For example, if the string is: sa ...

Node.js and Express: tackling the problem of duplicate variables

I have a checkUser middleware that saves the user information when a JWT is verified. However, when I integrate it into my routes and attempt to log res.locals.user.username, the username appears twice in the console. This duplication is causing issues wit ...

Is UseEffect selectively triggering specific components?

I am attempting to ensure that each time my search field changes, the code within useEffect is executed. Strangely, only console.log('hi') seems to be running while the other code is not. I am struggling to understand why useEffect is choosing t ...

Adding a query string based on a specific URL with IIS URL rewriting

My site has multiple http bindings listed below: http://sub1.domain.com http://sub2.domain.com http://sub3.domain.com http://sub4.domain.com http://sub5.domain.com When a user accesses any of those URLs, I want to add a different query string to each one ...

Filter a Vue list based on a checkbox that can be either checked or unchecked

I am currently working on my Vue app and aiming to filter a list to display only entries that have been moderated. However, I am encountering an issue where when the checkbox is checked, I receive all the results that are true, and when the checkbox is un ...

What causes the discrepancy in the output values of the sha1 algorithm when using the packages object-hash and crypto/hashlib

On my frontend, I have implemented a JavaScript function that compares two sha1 hashes generated by the object-hash library. This is used to determine if there are any changes in the input data, triggering a rerun of a processing pipeline. To interact wit ...

When using the Vue Array.splice() method, the DOM element is not removed in the element-ui table

I am facing an issue with a table containing hierarchical data in Vue. When attempting to remove a child using "array.splice", the DOM structure does not update reactively. Has anyone encountered this before? Are there any potential solutions? This proble ...

Showing JSON information in an Angular application

Upon page load, I am encountering an issue with retrieving and storing JSON data objects in Angular scope for use on my page and in my controller. When attempting to access the value, I receive the error message: angular.js:11655 ReferenceError: data is ...