How to give alert before expiry date is reached

Question

1.00/5 (2 votes)

See more:

SQL-Server-2014

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.

Posted 30-Apr-16 15:24pm

prasanna204

Updated 2-May-16 7:31am

Add a Solution

Comments

JayantaChatterjee 30-Apr-16 21:54pm

Can you provide more info, code block which you have tried....
It would be helpful to know what is your problem and what would the solutions..

George Jonsson 30-Apr-16 22:16pm

You need to provide the type of the relevant column, otherwise it is difficult to give any advice.
Are you storing the date in a VARCHAR(x) ?
If so, why not use DATETIME2 ?

Sergey Alexandrovich Kryukov 30-Apr-16 23:31pm

Your problem is storing data "in format". You need to use time types, not string.
—SA

Herman<T>.Instance 2-May-16 4:50am

Exactly

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2016-05-02T07:31:00

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:

SQL

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

SQL

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

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