DateTime From ASP to SQL Server 2

[scohan]

I've got a session variable containing a datetime value populated via 'now'. I need to query a SQL Server 7 table using the datetime value in the where clause. How do I convert the Session value (e.g., '1/22/01 12:51:18 PM') into a datetime format I can use in my SQL select statement? Thanks.

 

[TomSark]

If you are sending an embedded sql string to be executed on the server, you can build it like this:

Dim sqlstr
Dim mydate ' replace this with your date

mydate = Now()
sqlstr = "SELECT * FROM myTable WHERE somedate = '" & mydate & "'"

You can use the CDate function to convert a string to a date if needed. In the string, you will need to put the date in single quotes before sending it to SQL Server.
Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[scohan]

Thanks. I have tried this approach, but I can't get the record returned. I checked and double checked the syntax and the values of the variables to make sure a record sould be returned. But I get nothing when I include the date in the where clause. Stumped.

 

[cdukes]

What kind of date comparison are you trying to do?

If you supply the date/time to SQL Server, a comparison will be made on the exact date
ie in a query

"SELECT * FROM myTable WHERE somedate = '1/22/01 12:51:18 PM'"

There MUST be a record in your database with the date/time as 1/22/01 12:51:18 PM

If you want to check for a particular data you will need to format as follows:

&quot;SELECT * FROM myTable WHERE somedate >= '1/22/01 00:00:00' AND somedate <= '1/22/01 23:59:99'&quot;

Note also that SQL Server does not like the date delimeters '/' it is best to format the date as a long date:


&quot;SELECT * FROM myTable WHERE somedate >= '22 Jan 2001 00:00:00' AND somedate <= '22 Jan 2001 23:59:99'&quot;

Hope this helps,


Chris Dukes

 

[scohan]

I am doing a comparison like:

&quot;SELECT * FROM myTable WHERE somedate = '1/22/01 12:51:18 PM'&quot;

There is a record in the database with this date, but I'm not getting it returned. For debug purposes, I read the record into a recordset, populate a session variable with the date value from the recordset, and then build my SQL string using that session variable:


strSQL = &quot;select DateCreated from stg_case where EnteredBy like 'scohan@bellatlantic.net' and CaseNumber like 11&quot;
rsCase.Open strSQL, conn

if rsCase.EOF then
' do something
else
Session(&quot;DateCreated&quot = rsCase(&quot;DateCreated&quot
end if

rsCase.Close

strSQL = &quot;select * from stg_case where EnteredBy like 'scohan@bellatlantic.net' and DateCreated like '&quot; &amp; Session(&quot;DateCreated&quot &amp; &quot;'&quot;

rsCase.Open strSQL, conn

if rsCase.EOF then
' do something
else
' do something else
end if

 

[TomSark]

Dude,

Try this...

&quot;SELECT * FROM myTable WHERE somedate BETWEEN '1/22/01 00:00:00 AM' AND '1/22/01 11:59:59 PM'&quot;

I have a feeling that it's the time in the datetime that is giving you fits... Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[scohan]

Thanks everyone. TomSark's original answer worked as well as everyone elses. I was too slow to realize I should have been using '=' instead of 'LIKE' for date queries.

 

[TomSark]

D'oh LOL Tom Davis
tdavis@sark.com

http://www.sarkdallas.com