Calculate # days between TODAY and a DATETIME column value

Question

Post reply

Calculate # days between TODAY and a DATETIME column value

Express12

SSCoach

Points: 18518
More actions
July 19, 2021 at 7:54 pm

#3911795

Looking for some t-sql expertise. I need to present the # of days between today and the value in a DATETIME column.
My Database table houses a DATETIME column called Expired_Date which can be:
1) a date in the future
2) today
3) OR a date in the past.
I need to calculate the number of days between TODAY (getdate) and the Expired_Date then:
1) Render a POSITIVE number if Expired_Date is in the future.
2) Render a 0 if the Expired date is today
3) Render a NEGATIVE number if Expired_Date is in the past.
So using these 4 DATETIME values (today is July 19, 2021) my expected results would be:
2021-07-25 15:29:09.000 6
2021-07-28 17:31:22.000 9
2021-07-19 04:02:54.000 0
2021-07-18 12:01:31.000 -1
Any Help is greatly appreciated. Thx!
BT

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110209 More actions · Answer 1

DATEDIFF(did, ..., ...) should do.

_____________
Code for TallyGenerator

pietlinden SSC Guru Points: 63578 More actions · Answer 2

use tempdb;
go

SELECT x.ExpiredDate
, isfuture = iif(x.ExpiredDate>getdate(),'future','past')
,  daysAgo = DATEDIFF(day, getdate(),x.ExpiredDate)
, PassTest = IIF(DATEDIFF(day, getdate(),x.ExpiredDate) = x.ExpectedDaysAgo,1,0)
FROM (VALUES
('2021-07-25 15:29:09.000', 6)
,('2021-07-28 17:31:22.000', 9)
,('2021-07-19 04:02:54.000', 0)
,('2021-07-18 12:01:31.000', -1) 
) x(ExpiredDate,ExpectedDaysAgo);

Express12 SSCoach Points: 18518 More actions · Answer 3

thx for the quick replies.. My table has several thousand rows, so I cannot hard code dates as list in the example above. This SQL gets me kind of close:

SELECT DATEDIFF(day, (SELECT [ExpiredDate] FROM [MyTable]), (SELECT GETDATE()) )

but returns an error: Msg 512, Level 16, State 1, Line 4 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

(*** FYI: the output of all rows from this query will be displayed on an SSRS Report.. )

BT

pietlinden SSC Guru Points: 63578 More actions · Answer 4

SELECT DATEDIFF(day, [ExpiredDate], GETDATE() )

FROM MyTable

Jeff Moden SSC Guru Points: 1004595 More actions · Answer 5

Express12 wrote:

thx for the quick replies.. My table has several thousand rows, so I cannot hard code dates as list in the example above. This SQL gets me kind of close:
SELECT DATEDIFF(day, (SELECT [ExpiredDate] FROM [MyTable]), (SELECT GETDATE()) )
but returns an error: Msg 512, Level 16, State 1, Line 4 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
(*** FYI: the output of all rows from this query will be displayed on an SSRS Report.. )

The dates in the FROM VALUES bit of code are just sample dates. Replace the FROM VALUEs with FROM and your table name.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110209 More actions · Answer 6

pietlinden wrote:

SELECT DATEDIFF(day, [ExpiredDate], GETDATE() ) FROM MyTable

Almost correct. Except the order of dates:

declare @Today datetime;
set @Today = '20210719';

SELECT MyTable.ExpiredDate
, DATEDIFF(dd, @Today, MyTable.ExpiredDate) DaysLeft
FROM (VALUES
('2021-07-25 15:29:09.000')
,('2021-07-28 17:31:22.000')
,('2021-07-19 04:02:54.000')
,('2021-07-18 12:01:31.000') 
) MyTable (ExpiredDate);

_____________
Code for TallyGenerator

Express12 SSCoach Points: 18518 More actions · Answer 7

Using the query pietlinden recommended, I received the result I was looking for:.

SELECT DATEDIFF(day, [ExpiredDate], GETDATE() ) FROM MyTable

I just needed to reverse the 2 columns as:

SELECT DATEDIFF(day, GETDATE(), [ExpiredDate]) FROM MyTable

You guys are awesome! Thank you...

BT

Calculate # days between TODAY and a DATETIME column value

Cookies on SQLServerCentral