truncate time from date in DataTable.Select()

[Eddie]

I have a DataTable.Select() statement with a DateTime field that is
being compared to a Date:

Dim drCal as DataRow() = tblCal.Select("CalendarItemDate
='12/1/2003'", "CalendarItemDate", DataViewRowState.CurrentRows)

This never returns any rows because all the CalendarItemDate fields
that contain the date 12/1/2003 also contain a time component! I
searched until I was SICK and could not find how to truncate the time
from the DateTime field in the Select expression. :(

I would prefer to do it in the Select expression, but if I have to add
another field to the SQL statement that populates the DataSet, I guess
that will have to do. However, MS SQL 2000 no longer has the Trunc()
function!! I guess I have to use Round(n,0,1)??

Thanks in advance for any clues.

Cheers
Eddie

[Arthur Yousif]

Hi,

I don't normally set up a select like this, but you can try an old trick
like:

CalendarItemDate >= '12/1/2003 00:00:00' AND
CalendarItemDate <= '12/1/2003 23:59:59'

I'm surprised it's not working. Have you tried the select manually in Query
Analyzer? The select should match regardless of the time portion as long as
the date portion matches.

See the Transact-SQL help and look up the DatePart. You can use that to
construct a date-only value. Sorry I wasn't more helpful. I hope you get
it working, I know how frustrating this can be. Good luck.

--
Arthur
"Eddie" <ed********@yahoo.com> wrote in message
news:85**************************@posting.google.c om...

I have a DataTable.Select() statement with a DateTime field that is
being compared to a Date:

Dim drCal as DataRow() = tblCal.Select("CalendarItemDate
='12/1/2003'", "CalendarItemDate", DataViewRowState.CurrentRows)

This never returns any rows because all the CalendarItemDate fields
that contain the date 12/1/2003 also contain a time component! I
searched until I was SICK and could not find how to truncate the time
from the DateTime field in the Select expression. :(

I would prefer to do it in the Select expression, but if I have to add
another field to the SQL statement that populates the DataSet, I guess
that will have to do. However, MS SQL 2000 no longer has the Trunc()
function!! I guess I have to use Round(n,0,1)??

Thanks in advance for any clues.

Cheers
Eddie