Calculate Days to Expiry

You can use DateDiff to determine the number of days

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

try this

SELECT RecID, ExpDate, DateDiff(day,ExpDate,Now) as DaysLeft From MyTable

Select allOpen in new window

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

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?

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

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

Thanks! I will give it a try.

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