SQL date compare problem

[Darrel]

I'm trying to check a few fields in my SQL query to ensure that they fall
between a range (namly that today's date is greater than one field and less
than another field.)

This is what I have:

=========================================
dim strCurrentDate as String =
microsoft.VisualBasic.DateAndTime.Today().ToString("mm/dd/yyyy")

Dim strChk As String

strChk = "SELECT ALL seminarID, seminarTitle, seminarAttendanceLimit,
seminarEventDate, seminarTimes FROM LottsaA_dminDBA.webSeminars WHERE
(seminarPostDate <=" & strCurrentDate & ") AND (seminarPullDate > " &
strCurrentDate & ")"
=========================================

It's not working. If I remove the WHERE statement alltogether, I can grab
the records, but with it there, it's not finding anything. I'm wondering if
the issue is that I'm passing the currentdate as a string...instead of an
actual date. In SQL, they are set as 'datetime' fields.

-Darrel

 

[avnrao]

append quotes for date fields in the query..

strChk = "SELECT ALL seminarID, seminarTitle, seminarAttendanceLimit,
seminarEventDate, seminarTimes FROM LottsaA_dminDBA.webSeminars WHERE
(seminarPostDate <='" & strCurrentDate & "') AND (seminarPullDate > '" &
strCurrentDate & "')"

Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet

 

[Darrel]

append quotes for date fields in the query..

Hmm...doing that returns this error:

Error accessing Database.
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.

-Darrel

 

[Amar]

If you want to check today date why don't you use getdate() of sql server?


strChk = "SELECT ALL seminarID, seminarTitle, seminarAttendanceLimit,
seminarEventDate, seminarTimes FROM LottsaA_dminDBA.webSeminars WHERE
(seminarPostDate <=getdate()) AND (seminarPullDate > getdate())"

 

[darrel]

If you want to check today date why don't you use getdate() of sql server?

Becuase I didn't know it existed. Hence me asking the question. ;o)

strChk = "SELECT ALL seminarID, seminarTitle, seminarAttendanceLimit,
seminarEventDate, seminarTimes FROM LottsaA_dminDBA.webSeminars WHERE
(seminarPostDate <=getdate()) AND (seminarPullDate > getdate())"

Ah...this looks exactly the solution. Thanks! I saw references to getdate()
but didn't make the connection that this is a SQL command that you put
directly in the query.

-Darrel

 

[Amar]

OK

Becuase I didn't know it existed. Hence me asking the question. ;o)


Ah...this looks exactly the solution. Thanks! I saw references to getdate()
but didn't make the connection that this is a SQL command that you put
directly in the query.

-Darrel