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(requ est.form("em3") )
dat2=cdate(requ est.form("em4") )

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

set objdc = Server.CreateOb ject("ADODB.Con nection")
objdc.Open("pro vider=Microsoft .Jet.OLEDB.4.0; Data Source
=d:\access\newr es.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 adcmdexecutenor ecords
%>

[Bob Barrows [MVP]]

Bjorn wrote:

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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[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(totalDig its-len(n),"0") & n
else
pd = n
end if
End Function
dat1=pd(DAY(dat 1),2) & "-" & _
pd(MONTH(dat1), 2) & "-" & _
pd(RIGHT(YEAR(d at1),2),2)
dat2=pd(DAY(dat 2),2) & "-" & _
pd(MONTH(dat2), 2) & "-" & _
pd(RIGHT(YEAR(d at2),2),2)

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

??
"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcom> wrote in message
news:OD******** ******@tk2msftn gp13.phx.gbl...

Bjorn wrote:

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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[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]]

Aaron [SQL Server MVP] wrote:

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

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

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[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(totalDig its-len(n),"0") & n
else
pd = n
end if
End Function
dat1=pd(DAY(dat 1),2) & "-" & _
pd(MONTH(dat1), 2) & "-" & _
pd(RIGHT(YEAR(d at1),2),2)
dat2=pd(DAY(dat 2),2) & "-" & _
pd(MONTH(dat2), 2) & "-" & _
pd(RIGHT(YEAR(d at2),2),2)

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

What can i do more?

[Bob Barrows [MVP]]

Aaron [SQL Server MVP] wrote:

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

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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[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.

--
http://www.aspfaq.com/
(Reverse address to reply.)


"Bjorn" <no****@rt.sw > wrote in message
news:uB******** ******@TK2MSFTN GP11.phx.gbl...

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(totalDig its-len(n),"0") & n
else
pd = n
end if
End Function
dat1=pd(DAY(dat 1),2) & "-" & _
pd(MONTH(dat1), 2) & "-" & _
pd(RIGHT(YEAR(d at1),2),2)
dat2=pd(DAY(dat 2),2) & "-" & _
pd(MONTH(dat2), 2) & "-" & _
pd(RIGHT(YEAR(d at2),2),2)

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

What can i do more?