Having problem in comparing a date value taken from textbox with the datetime value stored in the database

More Clear:

Actually I have datetime value stored in the database. What I am taking from the textbox is a time value. I want to extract those results from the database which are between the given time value....To be more concise, my application is a booking system for a sports club and provides an option to view alreaady made bookings.. Here i offer two options either to view all bookings for a specific game or to filter bookings. In filter bookings one option is to filter through date and time...Date option is running okay but problem lies in time part...I provide two times but unable to view bookings between them...

My code is:

    Dim prmtimefrom As New SqlParameter("@booking_time", SqlDbType.DateTime)
    prmtimefrom.Value = TextBox3.Text

    Dim prmtimeto As New SqlParameter("@booking_tim", SqlDbType.DateTime)
    prmtimeto.Value = TextBox4.Text




    Dim da As New SqlDataAdapter("select * from Bookings where booking_time Between @booking_time AND @booking_tim AND game = " 开发者_开发知识库& x, con)   ' x is the name of a specific game

    da.SelectCommand.Parameters.Add(prmtimefrom)
    da.SelectCommand.Parameters.Add(prmtimeto)
    da.Fill(ds, "Bookings")

I think your problem may be that when records are stored in your database in the DATETIME variable, they are stored with a date part. When you try to query those records, you are also searching for values that lie between 2 date/time values.

So in your database the times are probably being recorded like this: "2011-08-24 06:00:00', but when you run your query, if you don't specify a date, then the value for the date portion is going to be '01-01-1900'. That's why you don't find any matches.

To eliminate the date portion, you could do something like this:

select * from Bookings 
WHERE 
DATEADD(day, -datediff(day, 0, booking_time), booking_time) 
Between DATEADD(day, -datediff(day, 0, @booking_time), @booking_time) AND 
DATEADD(day, -datediff(day, 0, @booking_tim), @booking_tim) 
AND game = " & x

The DATEADD function strips off the date and gives only the time part of the date.

Or, you could change the process that stores the booking times in your database so that it always stores it with a constant date, say '01-01-1900'.

Try throwing your times in the format 'yyyy-MM-dd hh:mm:ss'. SQL Server will always be able to compare times in that format.

On a side note, I would suggest renaming your second time parameter. It's much too similar to the first one and will cause confusion.