将在未来60天内到期的员工。使用SQL服务器 [英] Employee who is going to expire in next 60 days. Using SQL server

问题描述

我的数据库中有两列，即开始日期和到期日期。员工订阅将在两个月之前结束，以便需要向用户发送自动电子邮件，以确保您的订阅即将结束，请更新订阅。如何在SQL服务器中编写存储过程。



我尝试过：



使用sql查询，但它没有得到输出。你发布的示例。

I had two columns in my database i.e start-date and expiry-date . where employee subscription going to end before two months so that it needs to send automatic email to user regards your subscription going to end please renew subscription. how to write stored procedure in SQL server.

What I have tried:

using sql query but it doesn't get output.can you post example for that.

推荐答案

你的问题是因为你没有考虑那个数字来自你的 DATEDIFF 计算。



考虑这个例子：

Your problem is because you are not thinking about the numbers that are coming out of your DATEDIFF calculation.

Consider this example:

declare @SamplTable table (email nvarchar(255), expirydate date)
insert into @SamplTable (email, expirydate) values
('email.1@emailprovider', '14-MAY-2018'),	
('email.2@emailprovider', '13-JUL-2018'),	
('email.2@emailprovider', '13-JUL-2019'),	
('email.3@emailprovider', '15-MAR-2018'),	
('email.4@emailprovider', '14-MAR-2018')	
SELECT email, expirydate, DATEDIFF(day,expirydate, GETDATE()) from @SamplTable

结果s是

email.1@emailprovider	2018-05-14	0
email.2@emailprovider	2018-07-13	-60
email.2@emailprovider	2019-07-13	-425
email.3@emailprovider	2018-03-15	60
email.4@emailprovider	2018-03-14	61

所以当你把过滤器放在其中DATEDIFF（day，expirydate，GETDATE（））< = 60 您也将在2019年开始接收该日期。



我更愿意将未来的日期视为天数的正数 - 即转 DATEDIFF 一点点的值...即

so when you put your filter of where DATEDIFF(day,expirydate, GETDATE())<=60you are going to pick up that date way off in 2019 as well.

I prefer to think of dates in the future being a positive number of "days away" - i.e. turn the DATEDIFF values around a little ...i.e.

SELECT email, expirydate, DATEDIFF(day,GETDATE(), expirydate) from @SamplTable where DATEDIFF(day, GETDATE(),expirydate)<=60

给出结果

email.1@emailprovider	2018-05-14	0
email.2@emailprovider	2018-07-13	60
email.3@emailprovider	2018-03-15	-60
email.4@emailprovider	2018-03-14	-61

这种情况越来越好，但现在我过去也有过日期。



方法是使用 BETWEEN ...

This is getting better, but now I'm getting dates in the past as well.

The way around that is to use BETWEEN...

SELECT email, expirydate, DATEDIFF(day,GETDATE(), expirydate) from @SamplTable where DATEDIFF(day,GETDATE(), expirydate) BETWEEN 0 AND 60

，其中包含

email.1@emailprovider	2018-05-14	0
email.2@emailprovider	2018-07-13	60

这篇关于将在未来60天内到期的员工。使用SQL服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！