如何在到期日之前发出警报 [英] How to give alert before expiry date is reached

问题描述

亲爱的朋友们，



我有一项任务，当达到产品到期日期时，警报应在一个月之前发出，例如您的产品在30天后过期。



我以mm / yyyyy格式存储到期日。



如何实现这个没有dd部分的任务？



请建议我。



问候

Prasanna。



我尝试了什么：



Sql中的DateDiff函数但是没有实现结果。

Dear Friends,

I have one task, When product expiry date is reached alert should come before one month like"your product is expired after 30 days".

I am storing expiry date in the format "mm/yyyyy".

how to acheive this task without dd part?

Please suggest me.

Regards
Prasanna.

What I have tried:

DateDiff function in Sql but not acheived the result.

推荐答案

正如您将从上面的评论中看到的那样，您应该真正将到期日期存储在正确的列类型中 - Date，DateTime或DateTime2。



但是，假设您已将其存储为 varchar ，那么下面的代码片段演示了如何仍然在 DateDiff 函数中使用它：

As you will see from the comments above then you should really be storing the expiry date in the correct column type - Date, DateTime or DateTime2.

However, assuming you have stored this as a varchar then the following code snippet demonstrates how you can still use it in the DateDiff function:

DECLARE @badIdea varchar(7) = '05/2016' -- your data

-- extracted out to make it clearer what I'm doing
DECLARE @extendedDate varchar(10) = SUBSTRING(@badIdea, 4, 4) + '-' + SUBSTRING(@badIdea, 1, 2) + '-01' 

-- use the data in the function
SELECT datediff(dd, CAST(@extendedDate AS date), GETDATE())

或者作为查询的一部分，它看起来像

Or as part of a query it would look like

SELECT datediff(dd, CAST(SUBSTRING(expiry, 4, 4) + '-' + SUBSTRING(expiry, 1, 2) + '-01' AS date), GETDATE())
FROM yourTable

我不能强调你应该真正存储数据i你桌子上的日期类型！

由于这一天似乎并不重要，所以选择一个每月存在的任意数字，例如： 1,15或28

I cannot stress strongly enough that you should really store the data in a Date type on your table!
As the day appears to be unimportant then choose an arbitrary number that will exist for every month e.g. 1, 15 or 28

这篇关于如何在到期日之前发出警报的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！