manipulating the date and time from a datetime sql server type in vb.net

I'm trying to create an auction site using asp.net. My auction's start date & time and end date & time are stored in my sql server 2008 r2 database as datetime types. I'm using vb.net. I'm trying to create a select statement in which I can query the date and time portion开发者_开发百科 relative to the current date and time (so as to only display current ongoing auctions). I can successfully query the date portion using the Date.Now function, but the time is giving me a hassle. DateAndTime.Now doesn't work. I've even tried to separate the time and date in the database, but I still can't get an effective query.

Here are two examples of code which I've tried:

Dim Connection As SqlConnection = New SqlConnection(...)
    Dim Command As SqlCommand = New SqlCommand()
    Dim _Reader As SqlDataReader
    Command.Connection = Connection
    Command.CommandText = "Select ... from Auctions where Start_Date_Time > " & DateAndTime.Now & " And End_Date_Time > " & DateAndTime.Now

Whereby Start_Date_Time and End_Date_Time are of type DateTime.

And:

Dim Connection As SqlConnection = New SqlConnection(...)
    Dim Command As SqlCommand = New SqlCommand()
    Dim _Reader As SqlDataReader
    Command.Connection = Connection
    Command.CommandText = "Select ... from Auctions where Start_Date >= " & Date.Today & "Start_Time <= " & DateAndTime.Now.ToLongTimeString & " And End_Date >= " & Date.Today & " And End_Time <= " & DateAndTime.Now.ToLongTimeString

Whereby Start_Date and End_Date are of type Date, and Start_Time and End_Time are of type Time(7).

Any suggestions are appreciated, Thanks in advance

---

Have you tried using the DateAndTime.TimeString Property?

TimeString always returns the system time as "HH:mm:ss", which is a 24-hour format. So depending on how the time portion of your DateTime is stored in your db, you may need to do some formatting.

Dim Connection As SqlConnection = New SqlConnection(...) 
Dim Command As SqlCommand = New SqlCommand()
Dim _Reader As SqlDataReader
Command.Connection = Connection          
Command.CommandText = "Select ... from Auctions where Start_Date >= " & Date.Today & "Start_Time <= " & DateAndTime.TimeString & " And End_Date >= " & Date.Today & " And End_Time <= " & DateAndTime.TimeString      

---

dim myDateTime as Datetime = Now()

...

Select ... from Auctions where Start_Date_Time > " & myDateTime.ToShortTimeString() & ...

http://msdn.microsoft.com/en-us/library/system.datetime.toshorttimestring.aspx

---

Thanks for the suggestions guys. It was a simple error on my part. All I needed to do was put the DateAndTime.Now in inverted comma's.

So this code works :

Dim Connection As SqlConnection = New SqlConnection(...)
Dim Command As SqlCommand = New SqlCommand()
Dim _Reader As SqlDataReader
Command.Connection = Connection
Command.CommandText = "Select ... from Auctions where Start_Date_Time <= '" & DateAndTime.Now & "' And End_Date_Time >= '" & DateAndTime.Now & "'"