SQL Datefield

[Cryothic]

Hi,

I have a problem with a SMALL-DATETIME field in SQL SERVER.
The fields are filled like this:
11/7/01 4:53:00 PM

So, a date and a time in one field.
But when I try to get all records from 11/7/01 I get none, because I don't know the times.

So, WHERE datefield = '11/7/01' returnes 0 results.
While WHERE datefield = '11/7/01 4:53:00 PM' returnes one record. but I want all records from 11/7/01

Can somebody please help?

Thanx,
Cryothic

 

[jimbopalmer]

At least in Oracle, WHERE trunc(datefield) = should work or WHERE datefield between '11/7/01' and '11/7/01'+1 I tried to remain child-like, all I acheived was childish.

 

[fluteplr]

You have a couple of options the one I usually use is.

Select * from mytable
where datefield between
'2001-11-07' and '2001-11-07 23:59:59'

You can also cast the datefield to a character field, truncate off the time and then compare it.

 

[jimbopalmer]

'2001-11-07'+ 86399/86400 = '2001-11-07 23:59:59', if that helps
I tried to remain child-like, all I acheived was childish.