How to calculate date in Access 2003

bradbritton
bradbritton used Ask the Experts™
on
I have a query where I need to calculate the number of days from January 1st 1960, based on the data pulled.

For Example:

December 31 1959 = -1
Janurary 2nd 1960 = 1

I have tried the DateDiff ('d', 01/01/1960, [tablename].[date]) , but this doesnt seem to work properly...

Thanks,

Brad
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jet SQL will handle date math of this type natively, so try something like:

select [mydatefield]-#1/1/1960#  as NumberofDays from mytable;

Author

Commented:
I am using Access to build my queries, but I am actually connected to an Oracle DB. Is this still possible?
Are you querying against Linked tables or are you using a passthru query?

My syntax will work fine for Linked Tables.  

If you're using a passthru query you'll need to write it in Oracle's SQL dialect, but Oracle also overloads the + & - operators for dates so try:

SELECT [mydatefield] - to_date('19600101','YYYYMMDD') FROM mytable;

If you find you're getting a fraction back your date field may really be a date time so try:

SELECT trunc( [mydatefield]) - to_date('19600101','YYYYMMDD') FROM mytable;
 


Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
I am using linked tables.
SharathData Engineer

Commented:
Did you try the first post #26440626 ?
Curious  - As indicated, my 1st post should have been the correct Syntax for your scenario with Linked Tables.

Did this work for you?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial