Comparing Dates (1 Viewer)

[Brian Robertson]

I'm trying to compare two Date/Time fields which are both long dates. Each field in a different table. The comparison takes the time into concideration and makes the query run time unnacceptable; the tables are large. I've tried using the Format() function to translate the Date/Time to a text field holding only the date but then I lose the ability to sort. Can anyone enlighten me how I change a Long Date/Time field to a Short Date field. Changing the format of the field in a query doesn't appear to stop the comparison including the time part. Help !

 

[raskew]

Brian-

If you are saving dates in date/time data type (versus strings), they
ARE NOT saved in the underlying table in any particular format.

Instead, they are saved as a double-precision, floating-point
number (up to 15 decimal places). - The integer portion of
the double-precision number representing the date -
the decimal portion representing the time.

To illustrate, try this from the debug window:

MyDateTime = now()
? cdbl(MyDateTime)
37580.7805092593

MyDate = date()
? cdbl(MyDate)
37580

To display MyDateTime as Date Only:
x = int(MyDateTime)
? x
11/20/02

To confirm the conversion:
y = cdbl(x)
? y
37580
? cdate
11/20/02

Hopefully the above will provide enough insight to work out
your query problem. Here are a couple of sites that will
provide more detailed discussion:

http://support.microsoft.com/support/kb/articles/Q130/5/14.ASP

http://support.microsoft.com/support/kb/articles/Q136/1/27.ASP


HTH,
Bob

 

[Jon K]

The Format() function returns text strings. Text strings in the format yyyymmdd or yyyy/mm/dd can be sorted, so try using:-

Format([DateTimeField], "yyyymmdd")

or:
Format([DateTimeField], "yyyy/mm/dd")


If you need to use the returned text strings in date calculations, use the CDate() function to convert the returned text strings to dates:-

CDate(Format([DateTimeField], "yyyy/mm/dd"))


Hope this helps.

 

[Brian Robertson]

Raskew - good info. I understand better now.

Jon K - the CDate function would solve my problem but it can't be used in a query, it's a VB function which will take ME more time to implement. Is there an equivilent function that can be used in a Query ?

 

[raskew]

Brian-

Here are query results…

StartDate	        EndDate	             mySpan
11/16/02 6:46:35 PM	11/30/02 5:48:35 PM  14

…created with this code:

SELECT tblTimes2.StartDate, tblTimes2.EndDate, 
Int(CDbl([EndDate]))-Int(CDbl([StartDate])) AS mySpan
FROM tblTimes2;

As stored,
StartDate = 37576.782349537
EndDate = 37590.7420717593

Int(Cdbl([EndDate])) returns 37590
Int(Cdbl([StartDate])) returns 37576

…from then on, it's just simple subtraction.

Bob

 

[Jon K]

>> Jon K - the CDate function would solve my problem but it can't be used in a query, it's a VB function which will take ME more time to implement. <<


Your statement is incorrect. The CDate function can be used in queries. (In fact most of the built-in conversion functions can be used in queries.)

I don't know how you implement it. I just used it like this in my query:
CDate(Format([Table1].[Date],"yyyy/mm/dd"))
and it worked.


Attached is my test DB. It contains the following query:-
SELECT Table1.ID, Table1.Date, Table2.Date,
CDate(Format([Table1].[Date],"yyyy/mm/dd")) AS CDate1,
CDate(Format([Table2].[Date],"yyyy/mm/dd")) AS CDate2,
CDate2-CDate1 AS [CDate2-CDate1]
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID;

If you use the function in a similar fashion in your query, it should work.