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

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:

[url]http://www.aspfaq.com/show.asp?id=2313[/url] vbscript
[url]http://www.aspfaq.com/show.asp?id=2040[/url] help with dates
[url]http://www.aspfaq.com/show.asp?id=2260[/url] 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(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 ..

??


"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:ODIvXJVeEHA.3476@tk2msftngp13.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:
>
> [url]http://www.aspfaq.com/show.asp?id=2313[/url] vbscript
> [url]http://www.aspfaq.com/show.asp?id=2040[/url] help with dates
> [url]http://www.aspfaq.com/show.asp?id=2260[/url] 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(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]]

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.

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




"Bjorn" <nomail@rt.sw> wrote in message
news:uBVfLaWeEHA.3632@TK2MSFTNGP11.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(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?
>
>
>
>
>
>

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

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

[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






"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:eEyvWpWeEHA.724@TK2MSFTNGP10.phx.gbl...

> 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.
>
> --
> [url]http://www.aspfaq.com/[/url]
> (Reverse address to reply.)
>
>
>
>
> "Bjorn" <nomail@rt.sw> wrote in message
> news:uBVfLaWeEHA.3632@TK2MSFTNGP11.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(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]]

Literal dates must be surrounded with # in JetSQL

Bjorn wrote:

> 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

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

That's it!
Thanks a lot