Calculate Days to Expiry

The application I am designing needs to display records that have not expired. How can MS SQL database system help me calculate and update the expiry date such that I can display all records that have not expired, and show how many days to expiry?
fredphuaAsked:
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.

Aneesh RetnakaranDatabase AdministratorCommented:
if you are storing the date of expiry in 'DateofExpiry' column

select *
from urTable
where DateOfExpiry > convert(varchar, GetDate(), 112)
0
TextReportCommented:
You can use DateDiff to determine the number of days

select T.*, datediff(day,DateOfExpiry,getdate()) As ExpiresIn
from urTable T
where DateOfExpiry > GetDate()
0
ee_rleeCommented:
try this
SELECT RecID, ExpDate, DateDiff(day,ExpDate,Now) as DaysLeft From MyTable

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

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 for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ee_rleeCommented:
i forgot to put the where :D
SELECT RecID, ExpDate, DateDiff(day,ExpDate,Now) as DaysLeft From MyTable WHERE ExpDate>Now

Open in new window

0
TextReportCommented:
ee_rlee NOW is used in Access and not SQL Server.
Cheers, Andrew
0
fredphuaAuthor Commented:
Is datediff strictly for VBScript? I am using javascript.
aneeshattingal, your proposed solution is pretty clean too. I suppose I can type the phrase as seen directly as a SQL query?
0
fredphuaAuthor Commented:
what is the "112" below?
where DateOfExpiry > convert(varchar, GetDate(), 112)
0
TextReportCommented:
DateDiff is a function in MS SQL Server that returns the difference between 2 dates, in this example we have asked to return the difference in DAYS.

convert(varchar, GetDate(), 112) converts a date to a VarChar field in the format of YYYYMMDD, this format is accepted as a universal format within MS SQL Server for comparing dates.

Cheers, Andrew

0
TextReportCommented:
DateDiff is a function in MS SQL Server that returns the difference between 2 dates, in this example we have asked to return the difference in DAYS.

convert(varchar, GetDate(), 112) converts a date to a VarChar field in the format of YYYYMMDD, this format is accepted as a universal format within MS SQL Server for comparing dates.

Cheers, Andrew

0
fredphuaAuthor Commented:
Hmm..., I just changed my datetime data to yyyymmdd format manually (the web site is not LIVE yet). Is this a valid datetime format? MS SQL didn't report an error.
I am also scratching my head about how to retrieve the date in yyyymmdd from SQL database and display on the webpage as DD MONTH YYYY (Eg: 29 January 2008). Any idea? You will really save my day if you could help with this one.
0
TextReportCommented:
Leave your date as a datetime field and try this in SQL Server

SELECT getdate() AS GD, convert(varchar, GetDate(), 112)  AS YYYYMMDD
GD             Today's Date and Time in Your Setting              
YYYYMMDD 20080128

The where condition below will return all records from your datetime field that are on or after 1st January 2008 and this is not dependent on any regional settings. This can also be optermized by the Query Analyzer should you have an index on your date field.
WHERE MyDateField >= '20080101'

In order to get the full month you need to use DateName(Month,Getdate()) and string it together with the day and year.

SELECT CAST(Day(GetDate()) AS VARCHAR(2)) + ' ' + datename(month,getdate()) + ' ' + CAST(Year(GetDate()) AS VARCHAR(4)) AS D

Cheers, Andrew
0
fredphuaAuthor Commented:
Thanks! I will give it a try.
0
fredphuaAuthor Commented:
I use "replyby" as the field name in SQL to store my date info in datetime format. Since I am using "replyby" instead of getDate(), how do I modify the SQL query such that I can display the date in "DD/MM/YYYY" on the webpage? I am retrieving the date together with other data.

0
TextReportCommented:
As below, Cheers, Andrew

SELECT CAST(Day(replyby) AS VARCHAR(2)) + ' ' + datename(month,replyby) + ' ' + CAST(Year(replyby) AS VARCHAR(4)) AS D
0
TextReportCommented:
Sorry just to be clear the bit without the select is teh definition for a field in your select.

CAST(Day(replyby) AS VARCHAR(2)) + ' ' + datename(month,replyby) + ' ' + CAST(Year(replyby) AS VARCHAR(4)) AS D
0

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
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
Microsoft SQL Server

From novice to tech pro — start learning today.