The trick with date and MSSQL is using > and <. The string '04/02/2007' is seen by MSSQL as '04/02/2007 00:00:00', so your condition will return only those records with that exact timestamp. You have to use:
WHERE dateCreated >= '04/02/2007' AND dateCreateted < '04/03/2007' Steve Brownlee http://www.fusioncube.net/ > -----Original Message----- > From: Chad Gray [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 03, 2007 9:59 AM > To: CF-Talk > Subject: SQL question > > I am using MS SQL and have a field with data type DateTime. > > I want to find all records with the day 4/2/2007? > > If I do > WHERE dateCreated = '04/02/2007' > > I get no records and I have lots of records like this in the database: > > 4/2/2007 9:57:57 AM > 4/2/2007 10:57:57 AM > 4/2/2007 5:57:57 AM > 4/2/2007 2:57:57 AM > > I want to keep the time in my data so I don't want to modify > the data type. > > How do I write my query to find all records on the day 4/2/2007? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274425 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
