I think there's an isdate() function in SQL Server which would allow you to do something like this:
SELECT CASE WHEN IsDate(mydatecolumn) THEN dateadd(d,1,convert(datetime,mydatecolumn)) ELSE NULL END AS mydatecolumn I had to do this with a database which usually needed integers in a particular column but sometimes needed an alpha character, and we needed a view which would perform calculations on that column only when the column was numeric... that was using IsNumeric() in ms sql server... 'course... I have no idea what db server you're using... Oracle or PostgreSQL will probably be capable of something similar (although the syntax is likely way different). I got no ideas re: Access/MySQL ... hth > Ok, That changes things. > Are you sure that one of your rows doesn't have improper > data? I used to > have a database that used strings instead of a datetime > and I always had the > problem of people putting in things like INC or NR or > other stuff where the > date needed to go. > Steve > -----Original Message----- > From: Eric Creese [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 01, 2003 2:59 PM > To: CF-Talk > Subject: RE: SQL DATE CONVERSION QUESTION > the date string in the 255 varchar field is stored as > Jul 01 2003 12:00 AM > now if I put the " > convert(datetime,substring(demographicvaluedesc,1,11),101" > in the select > clause it will return the data correctly, how ever I need > this logic in the > where clause. > -----Original Message----- > From: DURETTE, STEVEN J (AIT) [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 01, 2003 1:38 PM > To: CF-Talk > Subject: RE: SQL DATE CONVERSION QUESTION > Without seeing the actual data being converted, my guess > would be that > substring 11 is the problem. > IE: 06/30/2003 = 10 characters not 11. Also, could your > data not prepend 0 > to months and days? Such as 6/5/2003 or maybe even > 6/5/03? > Without knowing more about the data being processed, that > is about all that > I can tell you. > Steve > -----Original Message----- > From: Eric Creese [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 01, 2003 2:06 PM > To: CF-Talk > Subject: RE: SQL DATE CONVERSION QUESTION > No SQL Gurus out there? > -----Original Message----- > From: Eric Creese > Sent: Tuesday, July 01, 2003 7:49 AM > To: CF-Talk > Subject: RE: SQL DATE CONVERSION QUESTION > Can anyone see what I am doing wrong? If I use the convert > statement in the > select clause instead of the where clause it works but I > need to convert > from the where clause against my criteria. > -----Original Message----- > From: Eric Creese > Sent: Monday, June 30, 2003 5:24 PM > To: CF-Talk > Subject: SQL DATE CONVERSION QUESTION > I am trying to query on a field that is a varchar(255) but > has date > information. > I get the following error: > Server: Msg 241, Level 16, State 1, Line 1 > Syntax error converting datetime from character string. > My query: > select personid > from persondemographic > where convert(datetime,substring(demographicvaluedesc,1,1 > 1),101)> > '06/30/2003' > and demographicgroupid =4 > and demographicitemid = 4 > and demographicvaluedesc is not null > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > ~~~~~~~~~~~| > Archives: > http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 > Subscription: http://www.houseoffusion.com/cf_lists/index. > cfm?method=subscribe&forumid=4 > FAQ: http://www.thenetprofits.co.uk/coldfusion/faq > This list and all House of Fusion resources hosted by > CFHosting.com. The place for dependable ColdFusion > Hosting. > http://www.cfhosting.com > Unsubscribe: http://www.houseoffusion.com/cf_lists/uns > ubscribe.cfm?user=633.558.4 s. isaac dealey 972-490-6624 new epoch http://www.turnkey.to lead architect, tapestry cms http://products.turnkey.to tapestry api is opensource http://www.turnkey.to/tapi certified advanced coldfusion 5 developer http://www.macromedia.com/v1/handlers/index.cfm?ID=21816 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
