VB.NET deal with expiration dates using Views in SQL

Hi everyone, I have a question, If I have a database (Column for Date_Expiration) how can I use this in VB.net to warn me if the item is reached its expiration date?

OR

How to set this in Stores Procedure in SQL?

Thanks in advance.

Sample
Rowel VirgoVisual Studio .NETAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

it_saigeDeveloperCommented:
Convert the value to a DateTime and subtract DateTime.Now, if the TotalDays difference is negative, it is older (or expired); e.g. -
Module Module1
	Sub Main()
		Dim dates = New List(Of String) From {"2018-01-12", "2018-02-01", "2018-01-01"}
		For Each [date] In dates.Select(Of DateTime)(Function(d) DateTime.Parse(d))
			Dim difference = ([date] - DateTime.Now).TotalDays
			If difference < 0 Then
				Console.WriteLine("Expired - {0} has passed...", [date])
			Else
				Console.WriteLine("Valid - {0} is in the future...", [date])
			End If
		Next
		Console.ReadLine()
	End Sub
End Module

Open in new window

Produces the following output -Capture.PNG
-saige-
1
Rowel VirgoVisual Studio .NETAuthor Commented:
Thanks but how can I call this function?
0
it_saigeDeveloperCommented:
Which function?  My example is basically a parse of the string into a date time variable.  That variable's value is then subtracted from the value produced from DateTime.Now in order to produce a TimeSpan.  The TimeSpan object contains a property called TotalDays.

Depending upon how you read the data from your database you can make a function that gets passed either a string (which you would convert to a datatime variable) or a datetime variable.  The return of the function is a boolean which states whether it is expired.

-saige-
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Fernando SotoRetiredCommented:
Hi Rowel;

Are you wanting to know how to retrieve the date field from the database?
1
Ryan ChongCommented:
if the remark field in your table is constantly updated, you can directly refer to that field's value.

alternatively you can also do the checks at your DB end to reduce the overheads

select *,
case when datediff(d, date_expire, getdate()) >= 0 then 'Yes' else 'No' end isExpired
from rsv.inv_inventory

Open in new window


and then refer to this new field: isExpired
1
Rowel VirgoVisual Studio .NETAuthor Commented:
Disregarding the remarks. I cant call out those items which already expired based on Date_Expire.

Trying more, other ways...
0
Rowel VirgoVisual Studio .NETAuthor Commented:
I'm trying all "datediff" formulas, but I can't... Or, I need to change the date format???


or how to use views???

How can I apply this>>>

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `view_cream_validity` AS
    (SELECT 
        `c`.`creamid` AS `creamid`,
        `c`.`validitydate` AS `validitydate`,
        IF((NOW() > `c`.`validitydate`),
            'expired',
            'valid') AS `creamstatus`
    FROM
        `tblcreamsolder` `c`
    WHERE
        ISNULL(`c`.`thawingtime`))

Open in new window


Validity of a creamsolder sample... based on thawed time and date now to get the remaining time or date... Then I will get the remaining time or date if "0" then expire and in my VB., I will select from the column if Expired then msgbox("THis Item is expired!")
0
Rowel VirgoVisual Studio .NETAuthor Commented:
I found a solution once I Search the Item to Sell it for example,.. I created a cases if the item searched is valid or expired it will warn the user that the item is expired or still valid and using the views by calling the status:


CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `view_status` AS
    (SELECT 
        `c`.`Name` AS `Name`,
        `c`.`Date_Expire` AS `EndOfLife`,
        IF((NOW() > `c`.`Date_Expire`),
            'expired',
            'valid') AS `Status`
    FROM
        `inv_inventory` `c`
    WHERE
        ISNULL(`c`.`Remaining`))

Open in new window


save
But it only promp me when I search specific Item name, how can I automatically warn me if there's an expired Item, any Idea??

Thanks
0
Rowel VirgoVisual Studio .NETAuthor Commented:
Select (Status) From rsv.view_status Where Status = 'expired'

If (Label)expired then set msgbox() in a Timer.Tick??
0
Ryan ChongCommented:
But it only promp me when I search specific Item name, how can I automatically warn me if there's an expired Item, any Idea??
there are many approaches we can achieve that.

for MySQL stored procedure, we can define it to return multiple recordsets, in one of it, you can let it return a field value (like 1 or 0) for indication whether there's expired records and another recordset to return the list of data as per what currently you have.

OR in your .NET programming, we may use Linq or other methods (depends on what methods/ controls you're using) to determine if there's record with expired status.

it depends on what approach you prefer.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rowel VirgoVisual Studio .NETAuthor Commented:
Thanks everyone
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.