15,614,943 members
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 @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
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.

## Solution 1

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