Visual Studio - SQL & VB Dates/Times!!!

Author

SQL & VB Dates/Times!!!

Richard Gutery

2004-12-20, 3:59 pm

I've written an asp app that requires users to login - normal stuff.

As is typical with users, sometimes they just close the browser which
teminates the app. What I've done is created a new table in SQL that inserts
the date & time when a user logs in. All is well as other stuff.

If the user then comes along and tries to log in again, I place this
information into another table (for troubleshooting and reporting). However
the login in my program prevents them from logging in again (I do a query
against Active_Users table) and if the user id is in the table, they cannot
login again. We do this so that users don't run multiple copies of the
browser (we know that users minimize stuff). Our app cannot allow users to
log in more that once during an active session.

What I'd like to be able to do is to delete an OLD record (say from
yesterday in an ADMIN capacity) from the table based on the current
date/time.

My probleme is that all records get deleted from the table based on the
following SQL statement:

StrSql = "DELETE FROM active_users WHERE Last_Seen <=' " &
DateValue(StrOnlineTimedOut) & "'"

Date/Time format in SQL: 2004-12-20 05:58:00.
Format of StrOnlineTImedOut: 12/20/2004 6:18:20 AM

Script looks like this (essentially StrOnlineTimedOut is 5 minutes from
NOW):
onlinedate = now()
strTimeout = 1
StrOnlineTimedout = dateadd("n",-strtimeout*5,onlinedate)

So I believe that what I need to do is to convert the date time format from
VB to an SQL format?

Does anyone know of a VB Date/Time statement that will do this?

Thanks in advance.

RG

Mythran

2004-12-22, 4:04 pm

> StrSql = "DELETE FROM active_users WHERE Last_Seen <=' " &
> DateValue(StrOnlineTimedOut) & "'"
>
> Date/Time format in SQL: 2004-12-20 05:58:00.
> Format of StrOnlineTImedOut: 12/20/2004 6:18:20 AM

Hmm, according to my tests, 12/20/2004 6:18:20 AM works fine as a string in
SQL Server when running against a date field...

TEST SQL:

select * from tblCategory where UpdateDate <= '12/20/2004 6:18:20 AM'

Gave me the correct rows in the tblCategory table I have.

If you change DELETE FROM to SELECT * FROM, do you get the rows that you
would like to delete?

Mythran

Richard Gutery

2004-12-22, 9:14 pm

I tried this statement: select * from active_users where Last_Seen <=
'12/20/2004 6:18:20 AM'
and I got no records at all.

This one returned one record: select * from active_users where Last_Seen <=
'12/22/2004 12:42:00'

So, I'm guessing the Query Doesn't Like the AM. However, my problem is that
I want to select records based on the Current Date/Time. So how would I
programmatically get rid of the 'AM' in the time string?

I'm guessing something like this: strDatestring = len (StrOnLineTImedOut) -
2???

Richard
"Mythran" <kip_potter@hotmail.comREMOVETRAIL> wrote in message
news:uxgH7eE6EHA.1596@tk2msftngp13.phx.gbl...
>
>
>
> Hmm, according to my tests, 12/20/2004 6:18:20 AM works fine as a string
> in SQL Server when running against a date field...
>
> TEST SQL:
>
>
> select * from tblCategory where UpdateDate <= '12/20/2004 6:18:20 AM'
>
> Gave me the correct rows in the tblCategory table I have.
>
> If you change DELETE FROM to SELECT * FROM, do you get the rows that you
> would like to delete?
>
> Mythran
>
>

Mythran

2004-12-22, 9:14 pm

"Richard Gutery" <rgutery@mentorits.com> wrote in message
news:eBB0WBG6EHA.3856@tk2msftngp13.phx.gbl...
>I tried this statement: select * from active_users where Last_Seen <=
>'12/20/2004 6:18:20 AM'
> and I got no records at all.
>
> This one returned one record: select * from active_users where Last_Seen
> <= '12/22/2004 12:42:00'
>
> So, I'm guessing the Query Doesn't Like the AM. However, my problem is
> that I want to select records based on the Current Date/Time. So how would
> I programmatically get rid of the 'AM' in the time string?
>
> I'm guessing something like this: strDatestring = len
> (StrOnLineTImedOut) - 2???
>
> Richard

Unfortunately, all my tests correctly work with AM or PM. Without the AM or
PM, I believe it is taking it as military time. Anywho, try this:

select * from active_users where Last_Seen <= '12/22/2004 12:42:00 PM' then
swap PM with AM.

See if that returns any records.

Mythran

Sponsored Links