We help IT Professionals succeed at work.

Calculate Days to Expiry

fredphua
fredphua asked
on
Medium Priority
763 Views
Last Modified: 2008-01-29
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?
Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
if you are storing the date of expiry in 'DateofExpiry' column

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

select T.*, datediff(day,DateOfExpiry,getdate()) As ExpiresIn
from urTable T
where DateOfExpiry > GetDate()
Top Expert 2008

Commented:
try this
SELECT RecID, ExpDate, DateDiff(day,ExpDate,Now) as DaysLeft From MyTable

Open in new window

Top Expert 2008
Commented:
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

ee_rlee NOW is used in Access and not SQL Server.
Cheers, Andrew

Author

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?

Author

Commented:
what is the "112" below?
where DateOfExpiry > convert(varchar, GetDate(), 112)
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

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

Author

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.
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

Author

Commented:
Thanks! I will give it a try.

Author

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.

As below, Cheers, Andrew

SELECT CAST(Day(replyby) AS VARCHAR(2)) + ' ' + datename(month,replyby) + ' ' + CAST(Year(replyby) AS VARCHAR(4)) AS D
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