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
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
