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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
TextReportConnect With a Mentor Commented:
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
 
Aneesh RetnakaranConnect With a Mentor Database 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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

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

Open in new window

0
 
ee_rleeConnect With a Mentor 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

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
All Courses

From novice to tech pro — start learning today.