Give the CONVERT function a try: WHERE CONVERT(varchar(10), dateCreated, 101) = '04/02/2007'
This will change the style of the dateCreated value into mm/dd/yyyy format, which is what the 101 means, when running the comparison. On 4/3/07, Chad Gray <[EMAIL PROTECTED]> wrote: > I tried that also and no records are returned. > > > -----Original Message----- > From: Che Vilnonis [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 03, 2007 10:01 AM > To: CF-Talk > Subject: RE: SQL question > > How about you simply use WHERE dateCreated = '4/2/2007' > Basically, lose the padding zeros. > > ~Che > > -----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? > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274418 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4