How do i get today's transaction from sql server table

I have a transaction table which has start_date and end_date of datetime datatype.

I am passi开发者_StackOverflow中文版ng date from vb.net application. I wrote following code in vb.net application and query generated was somthing un-expected:

String.Format("SELECT * FROM {0}{1} WHERE {0}tran_date >= '{2}' and {0}tran_date <= '{3}' ;", _Pre, _Table, DateFrom.Date, DateTo.Date)

Here Both DateFrom and DateTo are date variables.

It produced output like:

SELECT * FROM rm07transaction 
WHERE 
    rm07tran_date >= '03/16/2011 12:00:00 AM' and 
    rm07tran_date <= '03/16/2011 12:00:00 AM' 

When i make query to find data of 03/16/2011 they are not populated

In your example you are searching for rows that have "rm07tran_date" exactly set to "03/16/2011 12:00:00 AM". What you want is to use the "start date" column and the "end date column". EG:

SELECT * FROM rm07transaction 
WHERE 
    rm07tran_StartDate >= '03/16/2011 00:00:00 AM' and 
    rm07tran_EndDate < '03/17/2011 00:00:00 AM'