Print Page | Close Window

SQL Dates (Mysql)

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=8861
Printed Date: 01 April 2026 at 2:32am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com


Topic: SQL Dates (Mysql)
Posted By: twooly
Subject: SQL Dates (Mysql)
Date Posted: 13 January 2004 at 3:35pm

I am having some SQL problems with asp/mysql.  I am trying to write my script for mysql now.  Can you point me in the right direction for some examples on date formating with mysql/asp before I go and ask a question about my code.

 

thanks

--Todd




Replies:
Posted By: aalavar
Date Posted: 13 January 2004 at 4:08pm

http://www.codetoad.com/asp/format_date_time.asp - http://www.codetoad.com/asp/format_date_time.asp

http://www.asp-help.com/articles/dates.asp - http://www.asp-help.com/articles/dates.asp

http://www.4guysfromrolla.com/webtech/011701-1.shtml - http://www.4guysfromrolla.com/webtech/011701-1.shtml

Edit: Forgot to add one.



Posted By: twooly
Date Posted: 13 January 2004 at 6:07pm

Ok I looked at that and tried some things but I am still having some problems.  No value gets entered in the table.  All I see is 0000-00-00 00:00:00

If I response.write onlinedate before I format I see 1/13/2004 5:06:14 PM

If I response.write onlinedate after format I see 13-1-2004 17:6:14

I am using mysql and I am using the datetime type for the two columns Logon_time, Last_Seen

Here is my code

<!--#include file='dbconnection.inc'-->
<%

onlinedate = now()
OnlineUserIp = Request.ServerVariables("REMOTE_ADDR")
strUserID = Session("userName")

onlinedate = Day(onlinedate) & "-" & Month(onlinedate) & "-" & Year(onlinedate) & " " & Hour(onlinedate) & ":" & Minute(onlinedate) & ":" & Second(onlinedate)

lastseen = Day(onlinedate) & "-" & Month(onlinedate) & "-" & Year(onlinedate) & " " & Hour(onlinedate) & ":" & Minute(onlinedate) & ":" & Second(onlinedate)

'Set timeout in minutes
strTimeout = 5
StrOnlineTimedout = dateadd("n",-strtimeout*3,onlinedate)

StrSql = "Select * From Active_Users Where User_ID='" & strUserID & "'"
Set rs1 = adoCon.Execute (StrSql)

'Check if users id exists in active users database
if rs1.eof then
'Add user to database because they dont exist
StrSql = "INSERT INTO active_Users (Logon_time,User_ip,Last_Seen,Last_Page,User_ID) " &_
"VALUES ('" & onlinedate & "','" & OnlineUserIp & "','" & lastseen & "','" &_
request.servervariables("path_info") & "','" & strUserID & "')"
adoCon.Execute (StrSql)
else
'There Still active so lets update their info
StrSql = "UPDATE active_users SET Last_Seen='" & lastseen & "', Last_Page='" &_
request.servervariables("path_info") & "' WHERE User_ID='" & strUserID & "'"
adoCon.execute (StrSql)
end if

'Remove Users Who Have Timed Out
StrSql = "DELETE FROM active_users WHERE Last_Seen < " & StrOnlineTimedout &"
adoCon.execute (StrSql)


%>

 



Posted By: twooly
Date Posted: 13 January 2004 at 6:38pm

OK I think I have it.  But how can I still do the compare on my delete line?

 

<!--#include file='dbconnection.inc'-->


<%
function mySqlDate(myDate)

    mySqlDate = Year(myDate) & "-" & Month(myDate) & "-" & Day(myDate) & " " & Hour(myDate) & ":" & Minute(myDate)  & ":" & Second(myDate)

end function
%>

<%

onlinedate = now()

OnlineUserIp = Request.ServerVariables("REMOTE_ADDR")
strUserID = Session("userName")

'Set timeout in minutes
strTimeout = 5
StrOnlineTimedout = dateadd("n",-strtimeout*3,onlinedate)

StrSql = "Select * From Active_Users Where User_ID='" & strUserID & "'"
Set rs1 = adoCon.Execute (StrSql)

'Check if users id exists in active users database
if rs1.eof then
'Add user to database because they dont exist
StrSql = "INSERT INTO active_Users (Logon_time,User_ip,Last_Seen,Last_Page,User_ID) " &_
"VALUES ('" & onlinedate & "','" & OnlineUserIp & "','" & mySqlDate(now()) & "','" &_
request.servervariables("path_info") & "','" & strUserID & "')"
adoCon.Execute (StrSql)
else
'There Still active so lets update their info
StrSql = "UPDATE active_users SET Last_Seen='" & mySqlDate(now()) & "', Last_Page='" &_
request.servervariables("path_info") & "' WHERE User_ID='" & strUserID & "'"
adoCon.execute (StrSql)
end if

'Remove Users Who Have Timed Out
'StrSql = "DELETE FROM active_users WHERE Last_Seen < " & StrOnlineTimedout &"
'adoCon.execute (StrSql)

%>



Posted By: twooly
Date Posted: 13 January 2004 at 6:51pm

This is the part I am talking about with the compare and delete

strTimeout = 5
StrOnlineTimedout = dateadd("n",-strtimeout*3,mySqlDate(onlinedate))

strOnlineTimedout = mySqlDate(StrOnlineTimedout) 

'StrSql = "DELETE FROM active_users WHERE Last_Seen < " & StrOnlineTimedout &"
'adoCon.execute (StrSql)

 

I am real close just can't get it though.  When I response.write the value I see what I want.  I just get this error with the above code.

Syntax error

/login/mysql/activeusers.asp, line 42

StrSql = "DELETE FROM active_users WHERE Last_Seen < " & StrOnlineTimedout  &
----------------------------------------------------- -----------------------^


Posted By: twooly
Date Posted: 13 January 2004 at 8:26pm

Got it

 

StrSql = "DELETE FROM active_users WHERE Last_Seen < '" & StrOnlineTimedout & "'"




Print Page | Close Window

Forum Software by Web Wiz Forums® version 12.08 - https://www.webwizforums.com
Copyright ©2001-2026 Web Wiz Ltd. - https://www.webwiz.net