Comparing Dates

[jeff]

hi gurus!

I struggle with this one repeatedly!

I am an Australian and our date format is normally "dd/mm/yyyy" ie
10/12/2007 is the 10th of December 2007 and not the 12th of October 2007.

The current situation I have is:-

ScheduledDates stored against courses in an Access Table
A form with a Date field (normal format is dd/mm/yyyy)
I want to do a comparison in VB SQL as follows:-

"....WHERE ScheduledDate >= #" & me.formcontrolDate & "# ..."

what I am getting is failing due to the fact that the ScheduledDate from the
table is being returned as 10/12/2007 (ie dd/mm/yyyy) but the # date #
interprets the 10/12/2007 in the form control as 12/10/2007.

Is there a surefire method of ALWAYS comparing date data whether from Access
Tables, SQL Server Tables, Access Date Controls and regardless of
international settings?

cheers
Jeff

 

[jeff]

jeff here again....

I have done some more research and it would appear that the most surefire
method would be to compare using the CLNG function turning the Date Control
value into a number.

eg

"....WHERE ScheduledDate >= " & Clng(me.formcontrolDate) & " ..."

and hey presto it worked! Using the Clng function as above, Access honours
the current international settings and treats the date in the control as
dd/mm/yyyy and hence returns a valid number for the date. the Date stored in
the Access table (and I believe even in SQL Server tables) is a number as
well.

I guess the thing to watch though is that :-
If the field in the database is a Date/Time field and not just a Date field
then the Date and Time are stored in as a Double, the integer portion is the
Date and the decimal portion is the Time.

If I am on the right track then I hope this helps others...if not....please
correct the error of my ways....

cheers
Jeff

 

[Jamie Collins]

I have done some more research and it would appear that the most surefire
method would be to compare using the CLNG function turning the Date Control
value into a number.

eg

"....WHERE ScheduledDate >= " & Clng(me.formcontrolDate) & " ..."

and hey presto it worked! Using the Clng function as above, Access honours
the current international settings and treats the date in the control as
dd/mm/yyyy and hence returns a valid number for the date. the Date stored in
the Access table (and I believe even in SQL Server tables) is a number as
well.

I guess the thing to watch though is that :-
If the field in the database is a Date/Time field and not just a Date field
then the Date and Time are stored in as a Double, the integer portion is the
Date and thedecimalportion is the Time.

If I am on the right track then I hope this helps others...if not....please
correct the error of my ways....

Be aware than CLNG() will round values using the banker's rounding
algorithm e.g. take a careful look at these results:

SELECT
#2007-03-29 12:00:00# AS test_date_1,
CDATE(CLNG(test_date_1)) AS result_date_1,
#2007-03-30 12:00:00# AS test_date_2,
CDATE(CLNG(test_date_2)) AS result_date_2,
#2007-03-31 12:00:00# AS test_date_3,
CDATE(CLNG(test_date_3)) AS result_date_3

Put another way, there is no value in the set of 'middays' that will
round to #2007-03-30 00:00:00# -- don't you find that a little weird?
I do.

Your 'error' is to consider a day to be a single value. Time is a
continuum therefore you need two values (start and end) to demark a
period e.g.

SELECT
#2007-03-28 12:00:00# AS test_date_1,
DATEADD('D', DATEDIFF('D', #1990-01-01 00:00:00#, test_date_1),
#1990-01-01 00:00:00#)
AS test_date_start,
DATEADD('D', DATEDIFF('D', #1990-01-01 00:00:00#, test_date_1),
#1990-01-01 23:59:59#)
AS test_date_end,
#2007-03-28 11:13:56# AS test_date_2,
CBOOL(test_date_2 BETWEEN test_date_start AND test_date_end) AS result

Jamie.

--

 

[Aussie Jeff]

mmmmmm Veeeeeeerry interesting....it would appear that one should not rely
on CLng but use CDbl and strip off the Integer portion if only needing to
compare the Date?

I will explore it further...thanks for the alert Jamie!

Cheers
Jeff

 

[Douglas J. Steele]

Check what your fellow countryman Allen Browne has at
http://www.allenbrowne.com/ser-36.html, or what I wrote in my September,
2003 "Access Answers" column in Pinnacle Publication's "Smart Access". You
can download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html`

 

[Jamie Collins]

it would appear that one should not rely
on CLng but use CDbl and strip off the Integer portion if only needing to
compare the Date?

That's not what I said!

I would encourage you to use temporal functionality for temporal data
e.g.

SELECT
#2007-03-28 12:00:00# AS date_1,
#2007-03-28 11:13:56# AS date_2,
CBOOL(
DATEADD('D', DATEDIFF('D', #1990-01-01 00:00:00#, date_1), #1990-01-01
00:00:00#)
=
DATEADD('D', DATEDIFF('D', #1990-01-01 00:00:00#, date_2), #1990-01-01
00:00:00#)
) AS result

Jamie.

--

 

[jeff]

Douglas

Thank you very much for the pointer to Allens article!! It is exactly what I
wanted and I suspect your article does the same!!

Thanks Jamie too..but the examples you were giving me may have been flying
over my head but all I am wanting to do is to take a date entered into a
text control on a form (in which it displays in correct australian format ie
dd/mm/yyyy) and compare it to entries in a table using SQL... exactly as
shown in Allens article!

Problem solvered....
cheers all
jeff

 

[Jamie Collins]

Thanks Jamie too..but the examples you were giving me may have been flying
over my head

Which part of "use temporal functionality for temporal data" did you

all I am wanting to do is to take a date entered into a
text control on a form (in which it displays in correct australian format ie
dd/mm/yyyy) and compare it to entries in a table using SQL... exactly as
shown in Allens article!

Which part of the article? This:?

The solution is to explicitly typecast all calculated date fields,
e.g.:

DueDate: CVDate([InvoiceDate] + 30)

(Note: CDate() fails on Null values, so CVDate() has more uses than
the "compatibility" issue highlighted in the Access documentation.)

[Unquote]

My approach would be:

The solution is to retain DATETIME values as DATETIME by operating on
them using temporal functionality only e.g.

SELECT DATEADD('D', 30, InvoiceDate) AS DueDate

Note temporal functions such as DATEADD() handle the null value
appropriately, as one would expect.

Ditto converting a DATETIME/Date to a String i.e. don't do it unless
it's for display purposes in the 'front end'.

Jamie.

--

 

[jeff]

Jamie

Sorry if I sounded rude... hehe... I did understand your points but was
after something quite simple.

All my dates are stored in an Access table or SQL server table. I only store
the DATE() I dont use the NOW() so the dates only have the Date component
and the Time component is set to 00:00:00.

All I usually ever need to do is to interface between the interface (ie Form
fields) and the database doing lookups for courses being run on or after
certain dates. I am not doing any calculations on dates other than to
compare ie "WHERE SchedDate >= #" & Me.txtSchedDate & "#". <- this does not
work correctly of "us" australians due the differing formats ie dd/mm vs
mm/dd the user enters the date into the Me.txtSchedDate control in the
format of dd/mm/yyyy. This wonderfully interpreted by Microsoft using the ##
delimiters as being mm/dd/yyyy if that was valid for it do so...ie it wont
convert 30/12/2007 to 12/30/2007 as this is not valid but it will convert
07/05/2007 to 05/07/2007 which is wrong for me.

So by incorporating Allen Brownes function:-
'-------------------------------------------------------------------------------
Function SQLDate(varDate As Variant) As String
'-------------------------------------------------------------------------------
'Purpose: Return a delimited string in the date format used natively by
JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time
component,
' or a date/time format if it does.
'Author: Allen Browne. (e-mail address removed), June 2006.

If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If

End Function

I am able to use "WHERE SchedDate >= " & SQLDate(Me.txtSchedDate) and get
the correct result.

But I do understand you reference to temporal functionality and will keep
that in mind if I ever need to do anything diferent.

regards
Aussie Jeff

Thanks Jamie too..but the examples you were giving me may have been
flying
over my head

Which part of "use temporal functionality for temporal data" did you

all I am wanting to do is to take a date entered into a
text control on a form (in which it displays in correct australian format
ie
dd/mm/yyyy) and compare it to entries in a table using SQL... exactly as
shown in Allens article!

Which part of the article? This:?

The solution is to explicitly typecast all calculated date fields,
e.g.:

DueDate: CVDate([InvoiceDate] + 30)

(Note: CDate() fails on Null values, so CVDate() has more uses than
the "compatibility" issue highlighted in the Access documentation.)

[Unquote]

My approach would be:

The solution is to retain DATETIME values as DATETIME by operating on
them using temporal functionality only e.g.

SELECT DATEADD('D', 30, InvoiceDate) AS DueDate

Note temporal functions such as DATEADD() handle the null value
appropriately, as one would expect.

Ditto converting a DATETIME/Date to a String i.e. don't do it unless
it's for display purposes in the 'front end'.

Jamie.

 

[Jamie Collins]

Sorry if I sounded rude... hehe...

No worries, I didn't think you were

I did understand your points but was
after something quite simple.

My advice _is_ simple: for temporal data, use the DATETIME data type
and temporal functionality.

I am not doing any calculations on dates other than to
compare ie "WHERE SchedDate >= #" & Me.txtSchedDate & "#". <- this does not
work correctly of "us" australians due the differing formats ie dd/mm vs
mm/dd the user enters the date into the Me.txtSchedDate control in the
format of dd/mm/yyyy. This wonderfully interpreted by Microsoft using the ##
delimiters as being mm/dd/yyyy

Thinking of temporal data as text formatted as a US dates misses the
point of a strongly-typed language such as SQL.

Instead of dynamic SQL:

"WHERE SchedDate >= #" & Me.txtSchedDate & "#"

use a parameter typed as DATETIME:

e.g. Access/Jet SQL

CREATE PROCEDURE GetData (
arg_SchedDate DATETIME = DATE()
)
AS
SELECT ...
WHERE SchedDate >= arg_SchedDate;

Do formatting/unformatting in the 'front end' and use only strongly-
typed values in the 'back end' e.g. use CDate(txtSchedDate) function
in VBA code to convert text to a strongly-typed VBA Date value that
Jet will handle natively or use an ADODB.Parameter strongly typed as
DATETIME which the OLE DB will handle natively (both approaches honour
regional settings).

All my dates are stored in an Access table or SQL server table. I only store
the DATE() I dont use the NOW() so the dates only have the Date component
and the Time component is set to 00:00:00.

Our we talking integrity constraints or merely a self-fulfilling
prophesy? e.g. "I do not _anticipate_ DATETIME values with a 'Time
component' other than '00:00:00' therefore there will never _be_ such
values." If you've opted out of constraints then I hope your wish
comes true ;-)

Jamie.

--

 

[jeff]

Jamie

Its just a wish...but of course I am not a fortune teller and cannot predict
the future...so of course you are right that I should not make these
assumptions and deal with it correctly in the first instance...I will spend
the time and do the job right...
thanks for all your advice mate!

I will be back!...if i have further issues....

Cheers
Jeff