When you got the error converting to ODBC Date were you removing the day-of-week (Tues / Thurs) and the time-zone prior to applying createodbcdatetime()? ... It's unfortunate that the date format required by RSS is so horribly "left-field" -- but you don't need to store that date format in your db (unless you just _want_ to store it as a string in a separate column for convenience). Just store the time-zone in a separate CHAR(3) column and then remove everything but the date before you apply createodbcdatetime() to insert it into a datetime column in your database. When you pull the data back out afterward, you can then display the proper date format for the RSS field using dateformat() and timeformat(). I was struggling a bit with this myself just the other day and posted some relevant(?) info on my blog at:
http://www.turnkey.to/ontap/docs/blog.cfm?netaction=view&blogdate=2003-08-20 Anyway, I hope that's at all helfpul. :) Isaac ------ Original Message ------ From: James Edmunds <[EMAIL PROTECTED]> To: CF-Talk <[EMAIL PROTECTED]> Sent: Aug 21, 2003 09:31 AM Subject: Re: What's wrong with Tuesday and Thursdays??? >Isaac, > >Thanks for those good suggestions... however, I've fished in that end of >the pond quite a bit and can't seem to make it work. > >I've tried putting createodbcdatetime in the QuerySetCell parameter but >that doesn't work. I've tried a cfloop after the creation of MyQuery >with CFSCRIPT to convert the pubDate variable with createodbcdate >operating on the puDate variable, and get a "could not be converted to a >date" expression exception. However, this only occurs when the dates >are Tue or Thur! > >As you can see from the original post with "working" version, the one >that works is different only in that the source XML has no Tue or Thur >dates. BTW, this is the date format for RSS, and the RSS won't validate >if not in this format. I have done this with non-validatalbe XML that >stores the date in a different format, and it works then, but I am >trying to build a page that will concatenate and display multiple >validated-RSS feeds. > >For some reason, the database can convert the strings to dates unless >they are Tue or Thur. CFQUERYPARAM doesn't help because the problem is >not with parameters passed into the query, but the data itself. I think >that what happens in the Query of queries I have further down the page >is that the types mismatch because some of the dates have been converted >into odbcdates and others (with Tue or Thur) were not. > >In any case, thanks for your help, and I shall continue to plumb the >waters in hopes of finding a solution. > >Best regards, > >James Edmunds > > >[EMAIL PROTECTED] wrote: > >>When you insert your dates into the query, convert them to odbcdate time values >>first by stripping the day of week and the time zone like this (if you need the time >>zone, put it in a separate column): >> >>createodbcdatetime(rereplace(thedate,"^[^,]*,[[:space:]]*(.*)[[:space:]]*[[:alpha:]]{2,2}T[[:space:]]*$","\1")) >> >>If that doesn't work, specify individual column names and use a filter on the date >>with cfqueryparam like: >> >>where pubdate < <cfqueryparam value="9900-01-01" cfsqltype="cf_sql_datetime"> >> >>hth >> >>Isaac >> >>------ Original Message ------ >>From: James Edmunds <[EMAIL PROTECTED]> >>To: CF-Talk <[EMAIL PROTECTED]> >>Sent: Aug 20, 2003 02:24 PM >>Subject: Re: What's wrong with Tuesday and Thursdays??? >> >> >> >>>I have two templates of code that concatenate and display two XML files >>>in RSS 2.0 format, identical except that one of them calls an RSS XML >>>file that has dates whose day of the week begin with a "T", and chokes! >>>It stops with a database exception, with this error message: >>>Can't convert the string Tue, 23 Sep 2003 00:00:00 CDT to java type DATE >>>(In earlier tests, I have had this same stop at Thu dates) >>> >>>You can navigate to each of these with these URLs, and you'll note the >>>CFDUMPs and Error reporting: >>> >>>The one that works: >>> >>>http://www.jamesedmunds.com/rssgetYES.cfm >>> >>>The one that doesn't: >>> >>>http://www.jamesedmunds.com/rssgetNO.cfm >>> >>>Below my signature, the code for each. Thanks in advance for anyone with >>>insight into this issue, which seems to have to do with the way the >>>dates are read in the database engine. I am getting these errors in CFMX >>>(what we would now call 6.0) through shared hosting on CrystalTech, as >>>well as locally testing on CFMX 6.1. >>> >>>Best to all, >>> >>>James Edmunds >>>--------------------------------------------- >>>CODE FOR RSSGETYES.CFM: >>> >>><CFTRY> >>><cfhttp url="http://lapresenters.org/rss/lajazdanz.xml"; method="get"> >>><cfset myQuery=QueryNew('Title, Link, Description,pubDate')> >>><cfset myDoc=XMLParse(CFHTTP.FileContent)> >>><cfset Items=myDoc.rss.channel.XMLChildren> >>><cfset myItemsArrayLength=ArrayLen(Items)-8> >>> >>><cfloop from="1" to="#myItemsArrayLength#" index="idx"> >>> >>><cfset QueryAddRow(myQuery,1)> >>><cfset >>>QuerySetCell(myQuery,'Title',myDoc.rss.channel.item[idx].Title.XMLText)> >>><cfset >>>QuerySetCell(myQuery,'Link',myDoc.rss.channel.item[idx].Link.XMLText)> >>><cfset >>>QuerySetCell(myQuery,'Description',myDoc.rss.channel.item[idx].Description.XMLText)> >>><cfset >>>QuerySetCell(myQuery,'pubDate',myDoc.rss.channel.item[idx].pubDate.XMLText)> >>> >>></cfloop> >>> >>><CFOUTPUT> >>><CFDUMP var="#myQuery#"> >>></CFOUTPUT> >>> >>><cfhttp url="http://www.lapresenters.org/rss/PrincessTheaterInc.xml"; >>>method="get"> >>><cfset myQuery2=QueryNew('Title, Link, Description,pubDate')> >>><cfset myDoc=XMLParse(CFHTTP.FileContent)> >>><cfset Items=myDoc.rss.channel.XMLChildren> >>><cfset myItemsArrayLength=ArrayLen(Items)-8> >>><cfloop from="1" to="#myItemsArrayLength#" index="idx"> >>><cfset QueryAddRow(myQuery2,1)> >>><cfset >>>QuerySetCell(myQuery2,'Title',myDoc.rss.channel.item[idx].Title.XMLText)> >>><cfset >>>QuerySetCell(myQuery2,'Link',myDoc.rss.channel.item[idx].Link.XMLText)> >>><cfset >>>QuerySetCell(myQuery2,'Description',myDoc.rss.channel.item[idx].Description.XMLText)> >>> >>><cfset >>>QuerySetCell(myQuery2,'pubDate',myDoc.rss.channel.item[idx].pubdate.XMLText)> >>> >>></cfloop> >>> >>><CFOUTPUT> >>><CFDUMP var="#myQuery2#"> >>></CFOUTPUT> >>> >>><cfquery dbtype="query" name="GetBoth"> >>> SELECT * FROM MyQuery >>> UNION >>> SELECT * FROM MyQuery2 >>> ORDER BY pubDate >>></cfquery> >>><table width="550" border="0" cellspacing="0" cellpadding="2"> >>><TR> >>> <TD colspan=2 valign=top><font size="2" face="Georgia, Times New >>>Roman, Times, serif">XML >>> Parsed and combined listings >>> from two different web site sources<br> >>> </font></TD> >>> </TR> >>><cfoutput query="GetBoth"> >>><tr> >>> <td valign=top width=100 align=right> >>> >>></td> >>><td valign=top width=450 align=left> >>> <font size="1" face="Arial, Helvetica, sans-serif"> <a >>>href="#link#"><strong>#title#</strong></a> >>> #description#</font> >>> </td> >>> >>> </tr> >>> </cfoutput> >>></table> >>><CFCATCH type="any"> >>><CFOUTPUT>Didn't happen. A #cfcatch.type# exception occurred. >>>#cfcatch.message# #cfcatch.Detail#<br> >>><br> >>><CFDUMP var="#cfcatch.tagcontext#"> >>></CFOUTPUT> >>></CFCATCH> >>> >>></CFTRY> >>> >>>====END OF CODE FOR RESSGETYES.CFM============== >>>+++++++++++++++++++++++++++++++++++++++++++++ >>>CODE FOR RSSGETNO.CFM: >>> >>><CFTRY> >>><cfhttp url="http://pasa-online.org/rss/pasa.xml"; method="get"> >>><cfset myQuery=QueryNew('Title, Link, Description,pubDate')> >>><cfset myDoc=XMLParse(CFHTTP.FileContent)> >>><cfset Items=myDoc.rss.channel.XMLChildren> >>><cfset myItemsArrayLength=ArrayLen(Items)-8> >>> >>><cfloop from="1" to="#myItemsArrayLength#" index="idx"> >>> >>><cfset QueryAddRow(myQuery,1)> >>><cfset >>>QuerySetCell(myQuery,'Title',myDoc.rss.channel.item[idx].Title.XMLText)> >>><cfset >>>QuerySetCell(myQuery,'Link',myDoc.rss.channel.item[idx].Link.XMLText)> >>><cfset >>>QuerySetCell(myQuery,'Description',myDoc.rss.channel.item[idx].Description.XMLText)> >>><cfset >>>QuerySetCell(myQuery,'pubDate',myDoc.rss.channel.item[idx].pubDate.XMLText)> >>> >>></cfloop> >>> >>><CFOUTPUT> >>><CFDUMP var="#myQuery#"> >>></CFOUTPUT> >>> >>><cfhttp url="http://www.lapresenters.org/rss/PrincessTheaterInc.xml"; >>>method="get"> >>><cfset myQuery2=QueryNew('Title, Link, Description,pubDate')> >>><cfset myDoc=XMLParse(CFHTTP.FileContent)> >>><cfset Items=myDoc.rss.channel.XMLChildren> >>><cfset myItemsArrayLength=ArrayLen(Items)-8> >>><cfloop from="1" to="#myItemsArrayLength#" index="idx"> >>><cfset QueryAddRow(myQuery2,1)> >>><cfset >>>QuerySetCell(myQuery2,'Title',myDoc.rss.channel.item[idx].Title.XMLText)> >>><cfset >>>QuerySetCell(myQuery2,'Link',myDoc.rss.channel.item[idx].Link.XMLText)> >>><cfset >>>QuerySetCell(myQuery2,'Description',myDoc.rss.channel.item[idx].Description.XMLText)> >>> >>><cfset >>>QuerySetCell(myQuery2,'pubDate',myDoc.rss.channel.item[idx].pubdate.XMLText)> >>> >>></cfloop> >>> >>><CFOUTPUT> >>><CFDUMP var="#myQuery2#"> >>></CFOUTPUT> >>> >>><cfquery dbtype="query" name="GetBoth"> >>> SELECT * FROM MyQuery >>> UNION >>> SELECT * FROM MyQuery2 >>> ORDER BY pubDate >>></cfquery> >>><table width="550" border="0" cellspacing="0" cellpadding="2"> >>><TR> >>> <TD colspan=2 valign=top><font size="2" face="Georgia, Times New >>>Roman, Times, serif">XML >>> Parsed and combined listings >>> from two different web site sources<br> >>> </font></TD> >>> </TR> >>><cfoutput query="GetBoth"> >>><tr> >>> <td valign=top width=100 align=right> >>> >>></td> >>><td valign=top width=450 align=left> >>> <font size="1" face="Arial, Helvetica, sans-serif"> <a >>>href="#link#"><strong>#title#</strong></a> >>> #description#</font> >>> </td> >>> >>> </tr> >>> </cfoutput> >>></table> >>><CFCATCH type="any"> >>><CFOUTPUT>Didn't happen. A #cfcatch.type# exception occurred. >>>#cfcatch.message# #cfcatch.Detail#<br> >>><br> >>><CFDUMP var="#cfcatch.tagcontext#"> >>></CFOUTPUT> >>></CFCATCH> >>> >>></CFTRY> >>> >>> >>> >>> >>> >> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
