Qeury Problem with Dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Qeury Problem with Dates

Author

Topic

farazm
Starting Member

1 Post

Posted - 2008-04-01 : 10:22:24

I have a main table where I have stored series of expiry dates.

I need to select those entries that are expiring within one month of todays date. Can anyone help me creating a SQL query for this ?

Thank You.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-04-01 : 10:50:20

From the help pages

Calculate the difference between two date or time values
Show All
Hide All
The following table lists examples of expressions that use the DateDiff function to calculate the difference between any two date and time values.

You can use these expressions in calculated controls (calculated control: A control that is used on a form, report, or data access page to display the result of an expression. The result is recalculated each time there is a change in any of the values on which the expression is based.) on forms, reports, and data access pages (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.).

Expression Description
=DateDiff("d", [OrderDate], [ShippedDate]) Displays the variance in days between the values of the OrderDate and ShippedDate fields.
=DateDiff("yyyy", [Birthday1], [Birthday2]) Displays the variance in years between the values of the Birthday1 and Birthday2 fields.
=DateDiff("m", #12/24/2000#, #11/26/2000#) Displays the variance in months between the two dates. The expression evaluates to -1, since the first date falls after the second date.
=DateDiff("yyyy", #12/31/2000#, #1/1/2001#) Displays the variance in years between the two dates. The expression evaluates to 1, even though only a day has elapsed.

You can use these expressions in a calculated field in a query.

Expression Description
LagTime: DateDiff("d", [OrderDate], [ShippedDate]) Displays in the LagTime field the number of days between the values of the OrderDate and ShippedDate fields.
YearsLapsed: DateDiff("yyyy", #12/31/2000#, #1/1/2001#) Displays in the YearsLapsed field the number of years between the two dates.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

sqlleaf
Starting Member

3 Posts

Posted - 2008-04-18 : 01:41:51

A simple query to get all records expiring within 30 days.

Sample table (MY_TABLE):
- MY_KEY (PK)
- EXP_DATE (Date/time)

Sample query:
SELECT date() AS cur_date, exp_date-date() AS diff, *
FROM my_table
WHERE exp_date-date()<=30;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources