SQL中日期的奇怪行为 [英] strange behaviour with date in SQL
问题描述
Access表格dayoff包含以下日期:
8/4/2004至8/14/2004(dd-mm-yy格式)。
''dat1''和''dat2 ''包含来自两个组合框的日期。
当''dat1''包含8/4/2004时和''dat2''包含8/9/2004,没问题:
有删除。
当''dat1''包含" 8 / 2004分之10"和''dat2''包含8/14/2004,没问题
要么:已删除
但是当''dat1''包含8时/ 4/2004年和QUOT;和''dat2''包含8/14/2004,仅删除
8/4/2004和8/14/2004。
所以它仍然存在2004年8月5日至2004年8月9日,然后''dat1''包含
" 8/4/2004"并且''dat2''包含8/13/2004,没有删除。
我的错在哪里? (我试过''之间''和> = ...< =)
谢谢
bjorn
<%
dat1 = cdate(request.form(" em3"))
dat2 = cdate(request.form(" em4"))
response.write(dat1&""& dat2)''这给出了例如2004年8月4日或
8/14/2004 ......
set objdc = Server.CreateObject(" ADODB.Connection")
objdc.Open(" provider = Microsoft.Jet.OLEDB.4.0;数据源
= d:\ access \ newres.mdb")
''strsql ="从verlof中删除其中cdate(datum)> =''" &安培; dat1& "''和
cdate(datum)< =''" &安培; dat2& "'';"
strsql ="从verlof中删除''"之间的cdate(datum) &安培; dat1& "''和''"
& dat2& "'';"
objdc.execute strsql ,, adcmdtext和adcmdexecutenorecords
%>
Bjorn写道:
Access表dayoff包含以下日期:
2004年8月4日至2004年8月14日(dd-mm-yy格式)。
什么是数据类型那些领域?如果它们是日期/时间字段,那么
它们将不会以任何格式存储。如果它们是文本字段,那么它们
将不会被视为日期。
以下是一些帮助您了解日期的链接:
http://www.aspfaq.com/show .asp?id = 2313 vbscript
http ://www.aspfaq.com/show.asp?id = 2040 帮助日期
http://www.aspfaq.com/show.asp?id=2260 dd / mm / yyy混淆
Bob Barrows
-
Microsoft MVP - ASP / ASP.NET
请回复新闻组。我的From
标题中列出的电子邮件帐户是我的垃圾邮件陷阱,因此我不经常检查它。通过发布到新闻组,您将获得更快的回复。
感谢您的回复。
Access中的字段是日期/时间(我将字段格式属性更改为
dd-mm-yy)。
当我添加转换函数时:
函数pd(n,totalDigits)
如果totalDigits> len(n)然后
pd = String(totalDigits-len(n)," 0")& n
其他
pd = n
结束如果
结束功能
dat1 = pd(DAY(dat1),2)& " - " &安培; _
pd(月(dat1),2)& " - " &安培; _
pd(右(年(数据1),2),2)
dat2 = pd(DAY(dat2),2)& " - " &安培; _
pd(月(dat2),2)& " - " &安培; _
pd(右(年(数据2),2),2)
这是最糟糕的:在任何情况下都没有删除..
??
" Bob Barrows [MVP]" <再****** @ NOyahoo.SPAMcom>在消息中写道
新闻:OD ************** @ tk2msftngp13.phx.gbl ...Bjorn写道:
Access表dayoff包含以下日期:
2004年8月4日至2004年8月14日(dd-mm-yy格式)。
这些字段的数据类型是什么?如果它们是日期/时间字段,那么它们将不会以任何格式存储。如果它们是文本字段,那么
它们将不被视为日期。
以下是一些帮助您了解日期的链接:
http://www.aspfaq.com/show .asp?id = 2313 vbscript
http ://www.aspfaq.com/show.asp?id = 2040 帮助日期
http://www.aspfaq.com/show.asp?id=2260 dd / mm / yyy混淆
Bob Barrows
-
Microsoft MVP - ASP / ASP.NET
请回复新闻组。我的From
标题中列出的电子邮件帐户是我的垃圾邮件陷阱,因此我不经常检查它。通过发布到新闻组,您将得到更快的回复。
更正:
该字段是日期/字段类型,但Windows的区域选项设置为
dd-mm-yy(我没有更改字段属性)
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
%>
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.
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:OD**************@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:
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.
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)
这篇关于SQL中日期的奇怪行为的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!