Click here to Skip to main content
15,029,010 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Dear Members!
I Want to calculate expiry date in sql Server my given Quary work best for only one product but i need for whole table

What I have tried:

SQL
BEGIN

        DECLARE @TempDate Datetime ,
        @ExpiryDate   Datetime,
        @year     int ,
        @month    int ,
        @day      int

Set @ExpiryDate = (SELECT  [ExpiryDate] from [dbo].[Purchases] where [ProductId]  = 1  )

SELECT @TempDate = @ExpiryDate

    SELECT @year = DATEDIFF(YEAR,@TempDate ,GETDATE()) -
            CASE
                WHEN (MONTH(@ExpiryDate) > MONTH(GETDATE())) OR
                (MONTH(@ExpiryDate) = MONTH(GETDATE()) AND DAY(@ExpiryDate) > DAY(GETDATE()))
                THEN 1 ELSE 0
            END
SELECT @TempDate = DATEADD(YEAR , @year ,@TempDate)

    SELECT @month = DATEDIFF(MONTH,@TempDate , GETDATE()) -
            CASE
                WHEN DAY(@ExpiryDate) > DAY(GETDATE())
                THEN 1 ELSE 0
            END
SELECT @TempDate = DATEADD(MONTH,@month,@TempDate)

SELECT @day = DATEDIFF(DAY ,@TempDate,GETDATE())


SELECT @year * -12 + @month

--SELECT @year AS Years , @month as Months ,@day as [Days]




        SELECT p.[ProductName] , s.[Quantity],s.[ExpiryDate],s.[Date],s.Price
            FROM [dbo].[Purchases] s
        JOIN [dbo].[Product] p ON s.ProductId = p.ProductID

        SELECT 'Expire in ' + convert(varchar(10), @year * -1) + ' years '+ convert(varchar(10), @month) + ' months'


    END


this work fine but i need
SQL
SELECT p.[ProductName] , s.[Quantity],s.[ExpiryDate],s.[Date],s.Price
            FROM [dbo].[Purchases] s
        JOIN [dbo].[Product] p ON s.ProductId = p.ProductID

like this that give me result of all product of the tables
Posted
Updated 11-Apr-21 23:09pm
Comments
RedDk 11-Apr-21 15:34pm
   
Works fine for me as is.
You mention: "this work fine but I need ... like this that give me result of all product of the tables".
Like what? The typing in the second textbox is IDENTICAL to the typing in the first textbox...
Fahim ullah 11-Apr-21 23:59pm
   
I want to show the whole table data but the above mention query gives me only 1 row where the product id is 1 or 2 etc.
RedDk 12-Apr-21 13:06pm
   
Not enough information. Sorry. Also, try using PRINT statements for your variables (variables are those things that are prefixed with a "@" character). Any values at various stages, when run, will be printed in the "MESSAGES" window at the base of the Managment Studio.

For more technical debugging, see the BOL. Or look up functions and methods of TSQL adding some context, like "loop" and "MSSQL", in a search engine online.

1 solution

It looks like your calculation can be simplified to:
SQL
DECLARE @Today date = GetDate();
DECLARE @Y int = Year(@Today), @M int = Month(@Today), @D int = Day(@Today);

SELECT
    CASE
        WHEN Day(S.ExpiryDate) > @D THEN DateAdd(month, -1, DateFromParts(@Y, @M, Day(s.ExpiryDate)))
        ELSE DateFromParts(@Y, @M, Day(s.ExpiryDate))
    END As ExpiryDate
FROM
    dbo.Purchases As S
;
DATEFROMPARTS (Transact-SQL) - SQL Server | Microsoft Docs[^]
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900