Clever Geek Handbook

SQL View - Date Subtraction

I have the following view

CREATE VIEW dbo.vw_IssueDates AS 
SELECT UserId,
       IssueDate

FROM users 
WHERE IssueDATE <= CONVERT(DATE,  '2015-01-20')
GO
SELECT * FROM dbo.vw_IssueDates

I'm trying to use a list of users who have outstanding penalties for more than 30 days, I know it will include GETDATE dynamically (like today's date) and subtract 30, but not sure where to start?

+3


source to share


2 answers


Use DATEADD(day,-30,GETDATE()) 

SELECT UserId, IssueDate
FROM users
WHERE IssueDate >= DATEADD(day,-30,GETDATE())


Also if you want it in the column how many days have passed.

SELECT UserId, IssueDate, DATEDIFF(day,IssueDate,GETDATE()) AS DaysOverDue
FROM users
WHERE IssueDate >= DATEADD(day,-30,GETDATE())
+3


source


Another way to solve this problem would be to use the datiff function. Here's what you can do:



CREATE VIEW dbo.vw_IssueDates AS 
SELECT UserId,
       IssueDate
FROM users 
WHERE DATEDIFF(day,IssueDATE,getdate()) > 30
GO
SELECT * FROM dbo.vw_IssueDates
+1


source






More articles:


All Articles