Home Unable to get correct date T-SQL stored procedure
Reply: 0

Unable to get correct date T-SQL stored procedure

user1644
1#
user1644 Published in July 21, 2018, 9:45 am

The stored procedure below is suppose to get the OrderCompletionDate year as last year AND and adds a month to current month.

So if I run it in January of 2018, it returns data for February 2017, and in February 2018, it will return the data from March 2017, so on and so forth.

My error:

When we get to December, it looks for month 13, which does not exist. I want it to get the current year's January data in this case. So when we get to December 2018, it should return the January 2018 data.

My question is:

How can I change this stored procedure so it still returns the last year and month +1 data. But when we get to December, it returns data for January of the current year?

CREATE PROCEDURE [dbo].[usp_GetAnniversaryCustomers]
    @date DATE
AS
    SET NOCOUNT ON

    SELECT
        CustomerID,
        BAN_ADSL_TN,
        REPLACE(LandlinePhoneNumber, ',', '') LandlinePhoneNumber,
        REPLACE(MobilePhoneNumber, ',', '') MobilePhoneNumber,
        FirstName,
        LastName,
        REPLACE(ServiceAddress, ',', '') ServiceAddress,
        ServiceAddressAptNo,
        REPLACE(ServiceAddressCity, ',', '') ServiceAddressCity,
        ServiceAddressState,
        ServiceAddressZip,
        OrderCompletionDate = CONVERT(VARCHAR(10), OrderCompletionDate, 101)
    FROM 
        dbo.Customers 
    WHERE 
        StatusID = 115 AND 
        YEAR(OrderCompletionDate) = YEAR(@date) - 1 AND 
        MONTH(OrderCompletionDate) = MONTH(@date) + 1
    ORDER BY 
        OrderCompletionDate DESC
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.35156 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO