Storing dates not working correctly

benny28
benny28 used Ask the Experts™
on
The code below is trying to update recoeds in the access db, the date I need to input is yyyymmdd with no slashes. the db field is text 8 characters.
The result Im getting is 2009/8/4
<%
showdebug=0
 
if showdebug=1 then Response.Write("exiting" & "<BR>" )
 
if Request.form("action")="   Exit   " then
	%>
	<script>
	window.location="bwhmenu.asp"
	</script>
	<%
end if
 
no=Request.Form("no")
drumnumber=Request.Form("DrumNumber")
'drumnumber="0000000000" & Request.Form("DrumNumber")
'drumnumber=Right(drumnumber,10)
if showdebug=1 then Response.Write(drumnumber & "<BR>" )
 
if no ="" then response.end
 
if drumnumber ="" then response.end
 
set conn=Server.CreateObject("ADODB.Connection") 
conn.provider="Microsoft.Jet.OLEDB.4.0"
conn.open(server.mappath(".\beechworthhoney.mdb"))
 
if showdebug=1 then Response.Write("opened connection" & "<BR>" )
 
set rs = Server.CreateObject("ADODB.Recordset")
 
szsql="SELECT tblDrums.DrumKey, tblDrums.DrumNumber, tblDrums.DrumType, tblDrums.HoneyTypeID "
szsql=szsql+"FROM tblDrums WHERE tblDrums.DrumNumber=" & drumnumber 
 
if showdebug=1 then Response.Write(szsql & "<BR>")
 
rs.Open  szsql , conn
 
%>
<html>
<head>
 
<SCRIPT LANGUAGE = "JavaScript">
<!--
 
function timedMsg()
{
	var t=setTimeout("window.location = 'drumreturn.asp'",1500)
}
 
 
//-->
</SCRIPT>
 
<title>Return Drums</title>
</head>
<body>
<font face="Arial">
<h3>Drum Return</h3>
<form name="drumstatus" method="post" action="drumreturn.asp" >
 
<%
 
if showdebug=1 then response.write(drumnumber & "</BR>")
'	Response.Write("UNKNOWN DRUM" & "<BR><BR>")
if rs.eof or rs.bof then
	response.write("Drum: " & drumnumber & "</BR>")%>
	<FONT SIZE=4 COLOR="#FF0000" ><Strong> UNKNOWN DRUM</Strong></FONT><BR><BR>
	<input type="submit" name="action" value="Close">
	<%rs.close
	conn.close
	response.end
end if
 
 
if showdebug=1 then response.write(Date() & "</BR>")
 
sql="UPDATE tblDrums SET StockStatus='RETURNED', ReturnDate='20" & Right(Date(),2) & Mid(Date(),4,2) & Left(Date(),2) & "', LastModifiedDate='20" & Right(Date(),2) & Mid(Date(),4,2) & Left(Date(),2) & "', LastModifiedTime='" & formatDateTime(Time(),4) & "', ModifiedBy='SCANNER' WHERE tblDrums.DrumNumber=" & drumnumber
	if showdebug=1 then response.write(sql & "<BR>")		
	
	conn.Execute sql, Recordsaffected
 	if err <> 0 then
 		Response.Write("You do not have permission to update this database!")
 	else 
		Response.Write("Drum number " & drumno & " was updated successfully.")
 	end if 
 
%>
 
<%
rs.close
conn.close
if showdebug=1 then Response.Write("db closed" & "<BR>")
if showdebug=1 then 
else
%>
 
 
<script>
window.location="drumreturn.asp"
</script>
<% end if
%>
<br />
 
 
<input type=hidden name="drumid" value=<%=key%>>
<input name="no" type="hidden" value=<%=no%>>
</form>
 
<script language="JavaScript">
<!--
 
 
//-->
</script>
 
</body>
</html>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
not sure what the problem is you want solved, but it should be at least a 10 character text field i guess : 2009/10/10

also you probaly want to use the datepart function instead

Commented:
sorry, i missed the "no" in no slashes
not sure if i got it right but something like :
datpart("yyyy",date) & datepart("MM",date) & datepart("dd",date)

Commented:
datepart("yyyy",date) & datepart("MM",date) & datepart("dd",date)
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Author

Commented:
Thanks for the response, I will give that solution I try and post the results. I have been looking at this problem for a while now and not able to resolve, in looking I think I have found other problems. The code is not mine but I have been left to make it work

Author

Commented:
Solution did not work got the error
Microsoft VBScript runtime error '800a0005'
Invalid procedure call or argument: 'Datepart'
/drumreturnsubmit.asp, line 79

Author

Commented:
I have found this code
[Date]='" & year(d) & "" & month(d) & "" & day(d)
which does what I want but the date inserted is 18991230

Commented:
d = now()
[Date]='" & year() & "" & month(d) & "" & day(d)

Commented:
d = now()
[Date]='" & year(d) & "" & month(d) & "" & day(d)

Commented:
do make sure that it always returns 2 digits for the month and the day parts.
otherwise you will have trouble with 2009\11\1 and 2009\1\11:

myyear = year(now())
mymonth = month(now))
myday = day(now())
if len(mymonth) = 1 then mymonth = "0" & mymonth
if len(myday) = 1 then mmyday = "0" & myday
[Date]=myyear & mymonth & myday
Commented:
myyear = year(now())
mymonth = month(now))
myday = day(now())
if len(mymonth) = 1 then mymonth = "0" & mymonth
if len(myday) = 1 then myday = "0" & myday
[Date]=myyear & mymonth & myday

Author

Commented:
Hi thanks for your input, if I add d=now() it works but as you say it omits the "0" if the day or month are single digits. I am however confused in how to use the code you posted last, it all makes sense to me but I just cant get it to work.
The last line you posted
[Date]=myyear & mymonth & myday
Where does it fit with the update query

Author

Commented:
Hi guys, got it nailed with this code

strYear= datepart("yyyy",date)
strMon=datepart("m",date)
strDay=datepart("d",date)
if len(strMon) = 1 then strMon = "0" & strMon
if len(strDay) = 1 then strDay = "0" & strDay



sql="UPDATE tblDrums SET StockStatus='RETURNED', ReturnDate='" & strYear & "" & strMon & "" & strDay  & "' WHERE tblDrums.DrumNumber=" & DrumNumber
      if showdebug=1 then response.write(sql & "<BR>")            
Thanks for all your assistance could not have resolved this without you

Author

Commented:
Excellent support

Commented:
i'm not sure if the author intended to close the question or accept an answer..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial