ASP中SQL数据库关于日期的错误

来源：百度知道 编辑：UC知道 时间：2024/06/27 10:09:30
大家好！我把ASP网站将原来ACCESS数据库改成SQL数据库后，关于日期的一些函数会不支持，总是出错！下面贴上我出错的这段代码，希望各位大侠帮忙改下！谢谢了！

sub ShowBirthday(iDiff)
dim sqlAnnounce,rsAnnounce,i

sqlAnnounce="select "
sqlAnnounce=sqlAnnounce & " name,format(birthday,"&"'mm'"&") as birthMonth,format(birthday,"&"'dd'"&") as birthDay, datediff("&"'d'"&",date(),format(birthday,"&"'mm dd'"&"))as sBe from PInformation where datediff('d','" & date() & "', format(birthday,'mm dd')) <=" & iDiff
sqlAnnounce=sqlAnnounce & " and datediff('d','" & date() & "', format(birthday,'mm dd')) >= 0 "
'sqlAnnounce=sqlAnnounce & " name,datediff('d', format(birthday,'mm dd'),'" & date() & "') as sBe from PInformation"
Set rsAnnounce= Server.CreateObject(&q

在sql中datediff(),d是不需要''的，你试试datediff(d,date1,date2)
----------------
我仔细看了下，错误不少
1、datediff中的d不要''
2、sql中的date()函数不对，应该是getdate()
3、format 函数不对，应该是CAST 或 CONVERT ，具体用法你自己去查sql索引
4、如果要得到天、月、年请使用day(getdate())、month(getdate())、year(getdate())
5、就算有format函数，format(birthday,"&"'mm'"&") as birthMonth，这句话我不知道你多加几个"&"有什么意思，多此一举

附：sql和access函数有很多不同点，希望你可以专门研究下他们的不同之处，祝你早日成功！

& 前后都要空格的
sqlAnnounce="select "
sqlAnnounce=sqlAnnounce & " name,format(birthday," & "'mm'" & ") as birthMonth,format(birthday," & "'dd'" & ") as birthDay, datediff(" & "'d'" & ",date(),format(birthday," & "'mm dd'" & "))as sBe from PInformation where datediff('d','" & date() & "', format(birthday,'mm dd')) <=" & iDiff
sqlAnnounce=s