strange behaviour with date in SQL

[Bjorn]

Hi,

The Access table "dayoff" contains following dates:
8/4/2004 till 8/14/2004 (in dd-mm-yy format).
'dat1' and 'dat2' contains dates coming from two combo-boxs.
When 'dat1' contains "8/4/2004" and 'dat2' contains "8/9/2004", no problem:
there are deleted.
When 'dat1' contains "8/10/2004" and 'dat2' contains "8/14/2004", no problem
either: there are deleted
But when 'dat1' contains "8/4/2004" and 'dat2' contains "8/14/2004", only
8/4/2004 and 8/14/2004 are deleted.
So it remains 8/5/2004 till 8/9/2004 and then when 'dat1' contains
"8/4/2004" and 'dat2' contains "8/13/2004", nothing is deleted.

Where is my fault? (i tried with 'Between' and with >= ... <=)
Thanks
bjorn

<%
dat1=cdate(request.form("em3"))
dat2=cdate(request.form("em4"))

response.write(dat1 & " " & dat2) 'this gives e.g. 8/4/2004 or
8/14/2004 ...

set objdc = Server.CreateObject("ADODB.Connection")
objdc.Open("provider=Microsoft.Jet.OLEDB.4.0; Data Source
=d:\access\newres.mdb")
'strsql="delete from verlof where cdate(datum) >= '" & dat1 & "' and
cdate(datum)<= '" & dat2 & "';"
strsql="delete from verlof where cdate(datum) between '" & dat1 & "' and '"
& dat2 & "';"
objdc.execute strsql, , adcmdtext and adcmdexecutenorecords
%>

 

[Bob Barrows [MVP]]

Hi,

The Access table "dayoff" contains following dates:
8/4/2004 till 8/14/2004 (in dd-mm-yy format).

What is the data type of those fields? If they are Date/Time fields, then
they will not be stored with any format. If they are Text fields, then they
will not be treated as dates.

Here are a few links to help you out with dates:

http://www.aspfaq.com/show.asp?id=2313 vbscript
http://www.aspfaq.com/show.asp?id=2040 help with dates
http://www.aspfaq.com/show.asp?id=2260 dd/mm/yyy confusion

Bob Barrows

 

[Bjorn]

Thanks for replying.

The field in Access is Date/Time (i change the field format property to
dd-mm-yy).
When i add the conversion function:

Function pd(n, totalDigits)
if totalDigits > len(n) then
pd = String(totalDigits-len(n),"0") & n
else
pd = n
end if
End Function
dat1=pd(DAY(dat1),2) & "-" & _
pd(MONTH(dat1),2) & "-" & _
pd(RIGHT(YEAR(dat1),2),2)
dat2=pd(DAY(dat2),2) & "-" & _
pd(MONTH(dat2),2) & "-" & _
pd(RIGHT(YEAR(dat2),2),2)

it's worst: nothing is deleted in any case ..

??

 

[Bjorn]

Correction:

the field is Date/Field type, but the regional options of Windows are set on
dd-mm-yy (i didn't change the field property)

 

[Aaron [SQL Server MVP]]

The field in Access is Date/Time (i change the field format property to

dd-mm-yy).

AAAARRGH!!! WHY???

Use YYYY-MM-DD! This is the only format that is going to prevent you from
getting d/m/y and m/d/y mixed up. Please do yourself a favor and do this
right! If you want to present d/m/y to users, do that at presentation.
Don't screw up the storage aspect.

A

 

[Bob Barrows [MVP]]

AAAARRGH!!! WHY???

Use YYYY-MM-DD! This is the only format that is going to prevent you
from getting d/m/y and m/d/y mixed up. Please do yourself a favor
and do this right! If you want to present d/m/y to users, do that at
presentation. Don't screw up the storage aspect.

A

The field format property only affects display and entry.
Dates are stored as Doubles, regardless of the format property setting.

Bob Barrows

 

[Aaron [SQL Server MVP]]

The field format property only affects display and entry.

I know that, but why have an ambiguous entry format? It makes no sense. If
I enter 08-06-04, it gets stored as June 8th, which isn't what everyone will
expect.

A

 

[Bjorn]

Thanks, but i posted a correction before you answered me (below) ...

the field is Date/Field type, but the regional options of Windows are set on
dd-mm-yy (i didn't change the field property).

I have changed the field property to YYYY-MM-DD but nothing changed.

In ASP, the SQL command gets dates in format 8/3/2004.
In my Access table, the format is dd-mm-yyy due to regional settings i can't
change.

The problem is: which format must 'dat1' and 'dat2' have in order to
understand each other?
I tried to convert the ASP dates with:
Function pd(n, totalDigits)
if totalDigits > len(n) then
pd = String(totalDigits-len(n),"0") & n
else
pd = n
end if
End Function
dat1=pd(DAY(dat1),2) & "-" & _
pd(MONTH(dat1),2) & "-" & _
pd(RIGHT(YEAR(dat1),2),2)
dat2=pd(DAY(dat2),2) & "-" & _
pd(MONTH(dat2),2) & "-" & _
pd(RIGHT(YEAR(dat2),2),2)

but it's worst: nothing is deleted in any cas

What can i do more?

 

[Bob Barrows [MVP]]

I know that, but why have an ambiguous entry format? It makes no
sense. If I enter 08-06-04, it gets stored as June 8th, which isn't
what everyone will expect.

A

OK, to expand on the point (for Bjorn's benefit), the Format property only
affects display and entry IN ACCESS. It has no affect on data entered from
or displayed in any external applications (such as ASP). IOW, it's totally
irrelevant to his ASP problems.

Bob Barrows

 

[Aaron [SQL Server MVP]]

Bjorn, you're missing the point. Your date in ASP, before sending it to
Access, should be YYYY-MM-DD. Your function makes it dd-mm-yy. Stop
worrying about the format in Access, that part is irrelevant. Get your
dates that you are passing TO access into an unambiguous format.

 

[Aaron [SQL Server MVP]]

OK, to expand on the point (for Bjorn's benefit), the Format property only

affects display and entry IN ACCESS. It has no affect on data entered from
or displayed in any external applications (such as ASP). IOW, it's totally
irrelevant to his ASP problems.

I wasn't suggesting it to solve "his ASP problems"... it was just a general
suggestion to avoid propogating these stupid regional formats everywhere
when they do nothing but confuse people, corrupt data, and cause errors...

 

[Bjorn]

Hi Aaron,

I have understand: Access format is irrelevant. Look at my new code:

dat1=cdate("2004-08-10") 'in YYYY-MM-DD format
'dat1="2004-08-10"
set objdc = Server.CreateObject("ADODB.Connection")
objdc.Open("provider=Microsoft.Jet.OLEDB.4.0; Data Source
=d:\access\newres.mdb")
sql="select datum from dayoff where cdate(datum) >= '" & dat1 & "'"
.....

The table 'dayoff' contains 04-08-04 till 14-08-04.
So i expect finally records from 10-08-04 to 14-08-04, but i get ALL of them
(also 04-08-04 ..).
I tried with and without CDATE.
I'm going to become crazy! There must be a solution, no?

Thanks anyway

 

[Bob Barrows [MVP]]

Literal dates must be surrounded with # in JetSQL

sql="select datum from dayoff where cdate(datum) >= '" & dat1 & "'"
....

should be (assuming datum is the date/time field)
sql="select datum from dayoff where datum >= #" & dat1 & "#"


It always helps to build your queries in Access using the query builder so
you can see what the sql statement is supposed to look like before you
attempt to run it from ASP. Debugging is also made easier when you can see
the actual sql statement that you are sending to the database engine. To see
the actual sql statement, do this:

response.write sql


HTH,
Bob Barrows

 

[Bjorn]

That's it!
Thanks a lot