Access vs SQL Date Weirdness = Incompatibility?

[CBoyda]

I think I have stumbled across yet another Access vs SQL syntax issue.
Using MS Access 2003 front end mdb with linked ODBC tables to SQL 2000
backend.

In the Access form I am just getting the functions working with access
queries and as time or resources are required I expect to convert them to SQL
stored procedures as necessary. Also eventually I expect this application
would/could be ported over to a web asp or .net app.

VBA Code:
strSQL = "SELECT tbl_vacation.DaysVacationID, tbl_vacation.CustomerID,
tbl_vacation.StartVacation, tbl_vacation.EndVacation FROM tbl_vacation " _
& " WHERE (((cvdate(tbl_vacation.StartVacation)) Between #" & CVDate(RS!
PaidUntil) & "# And #" & CVDate(Me.InvoiceDate) & "#)) Or (((cvdate
(tbl_vacation.EndVacation)) Between #" & CVDate(RS!PaidUntil) & "# And #" &
CVDate(Me.InvoiceDate) & "#)) Or (((#" & CVDate(RS!PaidUntil) & "#) Between
cvdate(tbl_Vacation.StartVacation) And cvdate(tbl_Vacation.EndVacation))) Or
(((#" & CVDate(Me.InvoiceDate) & "#) Between cvdate(tbl_Vacation.
StartVacation) And cvdate(tbl_Vacation.EndVacation))) " _
& " GROUP BY tbl_vacation.DaysVacationID, tbl_vacation.CustomerID,
tbl_vacation.StartVacation, tbl_vacation.EndVacation HAVING (((tbl_vacation.
CustomerID)=" & RS!CustomerID & "));"
Debug.Print "Customer Vacation SQL: " & Chr(10) & strSQL
Set rstCustomerVacation = CurrentDb.OpenRecordset(strSQL,
dbOpenSnapshot, dbReadOnly)
If rstCustomerVacation.EOF Then yadda yadda

The debug print statement outputs this:
SELECT tbl_vacation.DaysVacationID, tbl_vacation.CustomerID, tbl_vacation.
StartVacation, tbl_vacation.EndVacation FROM tbl_vacation WHERE (((cvdate
(tbl_vacation.StartVacation)) Between #12/01/2006# And #31/12/2006#)) Or ((
(cvdate(tbl_vacation.EndVacation)) Between #12/01/2006# And #31/12/2006#)) Or
(((#12/01/2006#) Between cvdate(tbl_Vacation.StartVacation) And cvdate
(tbl_Vacation.EndVacation))) Or (((#31/12/2006#) Between cvdate(tbl_Vacation.
StartVacation) And cvdate(tbl_Vacation.EndVacation))) GROUP BY tbl_vacation.
DaysVacationID, tbl_vacation.CustomerID, tbl_vacation.StartVacation,
tbl_vacation.EndVacation HAVING (((tbl_vacation.CustomerID)=1));

Now if I copy and paste this into an access query in design mode and then
look at the results I see this:
SELECT tbl_vacation.DaysVacationID, tbl_vacation.CustomerID, tbl_vacation.
StartVacation, tbl_vacation.EndVacation FROM tbl_vacation WHERE (((cvdate
(tbl_vacation.StartVacation)) Between #12/01/2006# And #31/12/2006#)) Or ((
(cvdate(tbl_vacation.EndVacation)) Between #12/01/2006# And #31/12/2006#)) Or
(((#12/01/2006#) Between cvdate(tbl_Vacation.StartVacation) And cvdate
(tbl_Vacation.EndVacation))) Or (((#31/12/2006#) Between cvdate(tbl_Vacation.
StartVacation) And cvdate(tbl_Vacation.EndVacation))) GROUP BY tbl_vacation.
DaysVacationID, tbl_vacation.CustomerID, tbl_vacation.StartVacation,
tbl_vacation.EndVacation HAVING (((tbl_vacation.CustomerID)=1));

NOTICE that my December first date has changed to a January 12th date???? (in
the first between statement)

So I looked into the fields a little closer because this seems to be in the
code at:
the spot > CVDate(RS!PaidUntil) <

So it seems that getdate() is the default SQL way of populating that field
and when Access views it things start reverting the dd/mm vs mm/dd - *insert
incompaibility scream here*

I have check out http://allenbrowne.com/ser-36.html but the reality seems to
be one of 2 solutions.

a) do not use getdate (SQL) and everytime I insert a date use the cvdate()
function but then won't this die a horrible death when I try to convert this
access query into a stored procedure?

b) use getdate and ________ (magically somehow) tell access to quite doing
stupid automatic date flipping if the query says #01/12/2006# that means
december 1st not Janaury 12th... grrr.

For side notes:
This really comes down to how do I prepopulate the PaidUntil (date/time)
field? Using Getdate seems to be the easiest but then Access misinterprets
the information or have I totally missed something obvious?

 

[Ken Snell \(MVP\)]

Use the Format function to "force" the dates to the US format in the
query -- for example (excerpt from your query-building string):

& " WHERE (((cvdate(tbl_vacation.StartVacation)) Between #" &
Format(CVDate(RS!
PaidUntil), "mm\/dd\/yyyy") & "# And #" & Format(CVDate(Me.InvoiceDate),
"mm\/dd\/yyyy") & "#)) Or (((cvdate

 

[Douglas J. Steele]

Sounds as though you didn't read Allen's article that closely! <g>

You must use the Format function in conjunction with CVDate: rather than

....#" & CVDate(RS!PaidUntil) & "#...

you need

Format(CVDate(RS!PaidUntil), "\#mm\/dd\/yyyy\#")

In fact, if PaidUntil is a date field, you probably don't require the CVDate
function.

To be honest, though, I don't understand what that SQL statement has to do
with prepopulating a field, nor how getdate enters into the discussion in
the context of the code you've presented.

 

[CBoyda via AccessMonster.com]

Hi Doug,

Firstly thanks for the quick reply, especially with the Christmas season in
full swing!

You must use the Format function in conjunction with CVDate: rather than
you need
Format(CVDate(RS!PaidUntil), "\#mm\/dd\/yyyy\#")

That makes sense.. it just seemed that the get date stored it dd/mm and
Access (obviously because of my regional settings) was misinterpretating this
as dd/mm. So the only problem I see with this is if I moved it to a
different SQL server how can I guarantee it won't switch to dd/mm in the SQL
field? I don't understand why the formatting isn't stored with the data for
proper validation. I mean if I enter it one way why isn't it stored that way
so that retrieval never has these issues?

Sounds like storing a random bit and then wondering, is that a currency
dollar, decimal place, or time setting, day or month... oi Microsoft has an
interesting way of putting valuable data in and getting garbage out, by
design

In fact, if PaidUntil is a date field, you probably don't require the CVDate
function.

So when would you require the CVDate function then? When the user is just
randomly typing in a string you hope is a date? I realized the redundancy
was ridiculous but I thought Access had completely forgot what the field was
because of the results of the query so I thought if I explicitly restated the
result it may help.

 

[accessmonster3 via AccessMonster.com]

Use the Format function to "force" the dates to the US format in the
query -- for example (excerpt from your query-building string):

& " WHERE (((cvdate(tbl_vacation.StartVacation)) Between #" &
Format(CVDate(RS!
PaidUntil), "mm\/dd\/yyyy") & "# And #" & Format(CVDate(Me.InvoiceDate),
"mm\/dd\/yyyy") & "#)) Or (((cvdate

Brilliantly simple, warmest thanks!

 

[accessmonster3 via AccessMonster.com]

Wow I obviously need more sleep...

VBA:
If Format(rstCustomerVacation!EndVacation, "dd\/mm\/yyyy") <= Format(Me.
InvoiceDate, "dd\/mm\/yyyy") Then
Debug.Print "less" <-- this is the result
Else
Debug.Print "more"
End If
VBA: Debug.Print "Start of Vacation is " & Format(rstCustomerVacation!
StartVacation, "dd\/mm\/yyyy") & " End of Vacation is " & Format
(rstCustomerVacation!EndVacation, "dd\/mm\/yyyy") & "."
returns
Start of Vacation is 26/12/2006 End of Vacation is 02/01/2007.

VBA: Debug.Print "Invoice date is " & Format(Me.InvoiceDate, "dd\/mm\/yyyy")
& "." returns
Invoice date is 31/12/2006

And it thinks the 2007 is less then 2006 - what?

 

[Rick Brandt]

Wow I obviously need more sleep...

VBA:
If Format(rstCustomerVacation!EndVacation, "dd\/mm\/yyyy") <=
Format(Me. InvoiceDate, "dd\/mm\/yyyy") Then
Debug.Print "less" <-- this is the result
Else
Debug.Print "more"
End If
VBA: Debug.Print "Start of Vacation is " & Format(rstCustomerVacation!
StartVacation, "dd\/mm\/yyyy") & " End of Vacation is " & Format
(rstCustomerVacation!EndVacation, "dd\/mm\/yyyy") & "."
returns
Start of Vacation is 26/12/2006 End of Vacation is 02/01/2007.

VBA: Debug.Print "Invoice date is " & Format(Me.InvoiceDate,
"dd\/mm\/yyyy") & "." returns
Invoice date is 31/12/2006

And it thinks the 2007 is less then 2006 - what?

Format() returns a string so you get string comparisons, not date comparisons.
Just compare the fields directly.

You sometimes have to use Format() or specific date format strings when using
them for critria or for inserting/updating data. When comparing two actual
DateTime fields to each other though this is not necessary.

 

[Douglas J. Steele]

There's no need to store formatting with the dates: dates are stored as
numbers. I don't remember the details for SQL Server, but in Jet databases,
dates are 8 byte floating point numbers, where the integer portion
represents the date as the number of days relative to 30 Dec, 1899, and the
decimal portion represents the time as fraction of a day. In other words, as
long as the date is being correctly recognized on the machine where the data
is being input, it's going to be stored correctly, and handled correctly on
other machines, regardless of what the Short Date format is on the other
machines.

 

[CBoyda via AccessMonster.com]

There's no need to store formatting with the dates: dates are stored as
numbers. I don't remember the details for SQL Server, but in Jet databases,
dates are 8 byte floating point numbers, where the integer portion
represents the date as the number of days relative to 30 Dec, 1899, and the
decimal portion represents the time as fraction of a day. In other words, as
long as the date is being correctly recognized on the machine where the data
is being input, it's going to be stored correctly, and handled correctly on
other machines, regardless of what the Short Date format is on the other
machines.

Thank you very much for the reply and I am sorry to keep extending this
thread but the same damn ridiculous date issues still perplex me. This is
just an observation, I would love to hear a reasonable explain why this
happens.

This FAILS
If Format(rstCustomerVacation!StartVacation, "dd\/mm\/yyyy") >= Format(RS!
PaidUntil, "dd\/mm\/yyyy") Then

This WORKS
If rstCustomerVacation!StartVacation >= RS!PaidUntil Then

This should be the SAME function. The format function does just that -
formats the data, it should not change it, yet if a >= comparison is done on
formatted date values (stored in MS-SQL) all hell breaks loose.

I would love to learn why this is happening because these date discrepanies
seem to be completely ridiculous at this point. I would think the formatted
information would more desired since it cleared compares apples to apples??

 

[Rick Brandt]

Thank you very much for the reply and I am sorry to keep extending
this thread but the same damn ridiculous date issues still perplex
me. This is just an observation, I would love to hear a reasonable
explain why this happens.

This FAILS
If Format(rstCustomerVacation!StartVacation, "dd\/mm\/yyyy") >=
Format(RS! PaidUntil, "dd\/mm\/yyyy") Then

This WORKS
If rstCustomerVacation!StartVacation >= RS!PaidUntil Then

This should be the SAME function. The format function does just that
- formats the data, it should not change it, yet if a >= comparison
is done on formatted date values (stored in MS-SQL) all hell breaks
loose.

I would love to learn why this is happening because these date
discrepanies seem to be completely ridiculous at this point. I would
think the formatted information would more desired since it cleared
compares apples to apples??

But Format() DOES change the data. Format returns a STRING version of your data
in a particular format. The values are no longer dates, so date sorting and
date comparisons no longer apply. It is the same as if you wrapped each field
value in CStr().

 

[CBoyda via AccessMonster.com]

But Format() DOES change the data. Format returns a STRING version of your data
in a particular format. The values are no longer dates, so date sorting and
date comparisons no longer apply. It is the same as if you wrapped each field
value in CStr().

Ah right, so any comparison is more of an alphabetical then 'date value' - at
last it all makes sense!

 

[Douglas J. Steele]

Ah right, so any comparison is more of an alphabetical then 'date value' -
at
last it all makes sense!

No: it's not "any comparison is more of an alphabetical than 'date value'.
Only comparisons that use the Format function are alphabetical in nature*.
This is because, as Rick pointed out, the Format function changes whatever
it's formatting into a string.

* Of course, comparisons between string variables or text fields will also
be alphabetical in nature.