CreateODBCDate()
I'm stumped. This is an UPDATE query running on a CF8 server with an Access backend. (This is before I started using cfqueryparam and I haven't had a chance to update this code.) I've abbreviated the page. cfparam name=FORM.DuesExpire default= In the query: DuesExpire = cfif len(FORM.DuesExpire) EQ 0NULLcfelse#CreateODBCDate(FORM.DuesExpire)#/cfif, This has been working. I'm using cftry. Here's the message it sent me: Detail: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'd '20141-04-30'}' It shows the query: ..., DuesExpire = {d '20141-04-30'}, ... I tried a test, thinking that the FORM value was malformed (it's set by the program, not manually entered): cfset x = '20141/04/01' cfoutput#CreateODBCDate(x)#/cfoutput That throws: 20141/04/01 is an invalid date or time string. This implies to me that an error would have been thrown by the attempt to create the date via CreateODBCDate, but it looks to me like the function created a malformed ODBC date and the program choked when it tired to insert that into the table. Am I missing something? Any suggestions what happened? Larry Stephens ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356709 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: CreateODBCDate()
Your year is 20141 that is close to twenty thousand years in the future. -Original Message- From: Stephens, Larry V [mailto:steph...@iu.edu] Sent: Friday, September 06, 2013 8:33 AM To: cf-talk Subject: CreateODBCDate() I'm stumped. This is an UPDATE query running on a CF8 server with an Access backend. (This is before I started using cfqueryparam and I haven't had a chance to update this code.) I've abbreviated the page. cfparam name=FORM.DuesExpire default= In the query: DuesExpire = cfif len(FORM.DuesExpire) EQ 0NULLcfelse#CreateODBCDate(FORM.DuesExpire)#/cfif, This has been working. I'm using cftry. Here's the message it sent me: Detail: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'd '20141-04-30'}' It shows the query: ..., DuesExpire = {d '20141-04-30'}, ... I tried a test, thinking that the FORM value was malformed (it's set by the program, not manually entered): cfset x = '20141/04/01' cfoutput#CreateODBCDate(x)#/cfoutput That throws: 20141/04/01 is an invalid date or time string. This implies to me that an error would have been thrown by the attempt to create the date via CreateODBCDate, but it looks to me like the function created a malformed ODBC date and the program choked when it tired to insert that into the table. Am I missing something? Any suggestions what happened? Larry Stephens ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356710 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CreateODBCDate()
shouldn't it be 2014 not 20141 ? On Fri, Sep 6, 2013 at 1:33 PM, Stephens, Larry V steph...@iu.edu wrote: I'm stumped. This is an UPDATE query running on a CF8 server with an Access backend. (This is before I started using cfqueryparam and I haven't had a chance to update this code.) I've abbreviated the page. cfparam name=FORM.DuesExpire default= In the query: DuesExpire = cfif len(FORM.DuesExpire) EQ 0NULLcfelse#CreateODBCDate(FORM.DuesExpire)#/cfif, This has been working. I'm using cftry. Here's the message it sent me: Detail: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'd '20141-04-30'}' It shows the query: ..., DuesExpire = {d '20141-04-30'}, ... I tried a test, thinking that the FORM value was malformed (it's set by the program, not manually entered): cfset x = '20141/04/01' cfoutput#CreateODBCDate(x)#/cfoutput That throws: 20141/04/01 is an invalid date or time string. This implies to me that an error would have been thrown by the attempt to create the date via CreateODBCDate, but it looks to me like the function created a malformed ODBC date and the program choked when it tired to insert that into the table. Am I missing something? Any suggestions what happened? Larry Stephens ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356711 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: CreateODBCDate()
shouldn't it be 2014 not 20141 ? Your year is 20141 that is close to twenty thousand years in the future. I know that - that's why I get the error on UPDATE. My question is has anyone ever seen CreateODBCDate create something like this? My point below is that if FORM had passed 04/30/20141 to CreateODBCDate the error would have been thrown there and the query would never be attempted. It looks to me like FORM passed 04/30/2014 (which would be correct in this context) and CreateODBCDate added the 20,000 years. I don't know why it would do that. On Fri, Sep 6, 2013 at 1:33 PM, Stephens, Larry V steph...@iu.edu wrote: I'm stumped. This is an UPDATE query running on a CF8 server with an Access backend. (This is before I started using cfqueryparam and I haven't had a chance to update this code.) I've abbreviated the page. cfparam name=FORM.DuesExpire default= In the query: DuesExpire = cfif len(FORM.DuesExpire) EQ 0NULLcfelse#CreateODBCDate(FORM.DuesExpire)#/cfif, This has been working. I'm using cftry. Here's the message it sent me: Detail: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'd '20141-04-30'}' It shows the query: ..., DuesExpire = {d '20141-04-30'}, ... I tried a test, thinking that the FORM value was malformed (it's set by the program, not manually entered): cfset x = '20141/04/01' cfoutput#CreateODBCDate(x)#/cfoutput That throws: 20141/04/01 is an invalid date or time string. This implies to me that an error would have been thrown by the attempt to create the date via CreateODBCDate, but it looks to me like the function created a malformed ODBC date and the program choked when it tired to insert that into the table. Am I missing something? Any suggestions what happened? Larry Stephens ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356716 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: CreateODBCDate()
Are you sure that you aren't adding that twenty thousand years by accident? It is for a dues expire, are you doing a + 1 to it? For example: cfset Variables.mydate = '04/30/2014' / cfset Variables.mydate = Variables.mydate + 1 / Result in this case would be Variables.mydate = '04/40/20141'. Instead something like dateadd(m, 1, Variables.mydate) needs to be used. Just a possibility. We can only guess without seeing the code between where the date comes in and when it is actually processed by the createodbcdate. Steve -Original Message- From: Stephens, Larry V [mailto:steph...@iu.edu] Sent: Friday, September 06, 2013 10:31 AM To: cf-talk Subject: RE: CreateODBCDate() shouldn't it be 2014 not 20141 ? Your year is 20141 that is close to twenty thousand years in the future. I know that - that's why I get the error on UPDATE. My question is has anyone ever seen CreateODBCDate create something like this? My point below is that if FORM had passed 04/30/20141 to CreateODBCDate the error would have been thrown there and the query would never be attempted. It looks to me like FORM passed 04/30/2014 (which would be correct in this context) and CreateODBCDate added the 20,000 years. I don't know why it would do that. On Fri, Sep 6, 2013 at 1:33 PM, Stephens, Larry V steph...@iu.edu wrote: I'm stumped. This is an UPDATE query running on a CF8 server with an Access backend. (This is before I started using cfqueryparam and I haven't had a chance to update this code.) I've abbreviated the page. cfparam name=FORM.DuesExpire default= In the query: DuesExpire = cfif len(FORM.DuesExpire) EQ 0NULLcfelse#CreateODBCDate(FORM.DuesExpire)#/cfif, This has been working. I'm using cftry. Here's the message it sent me: Detail: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'd '20141-04-30'}' It shows the query: ..., DuesExpire = {d '20141-04-30'}, ... I tried a test, thinking that the FORM value was malformed (it's set by the program, not manually entered): cfset x = '20141/04/01' cfoutput#CreateODBCDate(x)#/cfoutput That throws: 20141/04/01 is an invalid date or time string. This implies to me that an error would have been thrown by the attempt to create the date via CreateODBCDate, but it looks to me like the function created a malformed ODBC date and the program choked when it tired to insert that into the table. Am I missing something? Any suggestions what happened? Larry Stephens ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356717 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CreateODBCDate()
In the query: DuesExpire = cfif len(FORM.DuesExpire) EQ 0NULLcfelse#CreateODBCDate(FORM.DuesExpire)#/cfif, So I suppose you're using the variable DuesExpire in the SQL code. In that case, since the ODBCDate contains single quotes, the pseudo function Preserve single quotes should be used, ie: DuesExpire = #preserveSingleQuotes(DuesExpire)# ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356718 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CreateODBCDate()
has anyone ever seen CreateODBCDate create something like this? Yes. If you enter a numeric expression (or something that evaluates to a number), rather than a date string, that could happen. For example: createODBCDate(6662510) But as mentioned, that is just guess without knowing more. -Leigh From: DURETTE, STEVEN J sd1...@att.com To: cf-talk cf-talk@houseoffusion.com Sent: Friday, September 6, 2013 10:37 AM Subject: RE: CreateODBCDate() Are you sure that you aren't adding that twenty thousand years by accident? It is for a dues expire, are you doing a + 1 to it? For example: cfset Variables.mydate = '04/30/2014' / cfset Variables.mydate = Variables.mydate + 1 / Result in this case would be Variables.mydate = '04/40/20141'. Instead something like dateadd(m, 1, Variables.mydate) needs to be used. Just a possibility. We can only guess without seeing the code between where the date comes in and when it is actually processed by the createodbcdate. Steve -Original Message- From: Stephens, Larry V [mailto:steph...@iu.edu] Sent: Friday, September 06, 2013 10:31 AM To: cf-talk Subject: RE: CreateODBCDate() shouldn't it be 2014 not 20141 ? Your year is 20141 that is close to twenty thousand years in the future. I know that - that's why I get the error on UPDATE. My question is has anyone ever seen CreateODBCDate create something like this? My point below is that if FORM had passed 04/30/20141 to CreateODBCDate the error would have been thrown there and the query would never be attempted. It looks to me like FORM passed 04/30/2014 (which would be correct in this context) and CreateODBCDate added the 20,000 years. I don't know why it would do that. On Fri, Sep 6, 2013 at 1:33 PM, Stephens, Larry V steph...@iu.edu wrote: I'm stumped. This is an UPDATE query running on a CF8 server with an Access backend. (This is before I started using cfqueryparam and I haven't had a chance to update this code.) I've abbreviated the page. cfparam name=FORM.DuesExpire default= In the query: DuesExpire = cfif len(FORM.DuesExpire) EQ 0NULLcfelse#CreateODBCDate(FORM.DuesExpire)#/cfif, This has been working. I'm using cftry. Here's the message it sent me: Detail: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'd '20141-04-30'}' It shows the query: ..., DuesExpire = {d '20141-04-30'}, ... I tried a test, thinking that the FORM value was malformed (it's set by the program, not manually entered): cfset x = '20141/04/01' cfoutput#CreateODBCDate(x)#/cfoutput That throws: 20141/04/01 is an invalid date or time string. This implies to me that an error would have been thrown by the attempt to create the date via CreateODBCDate, but it looks to me like the function created a malformed ODBC date and the program choked when it tired to insert that into the table. Am I missing something? Any suggestions what happened? Larry Stephens ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356719 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CreateODBCDate()
Here's the message it sent me: Detail: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'd '20141-04-30'}' It shows the query: ..., DuesExpire = {d '20141-04-30'}, ... What is the complete error message and generated SQL? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356722 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CreateODBCDate()
On 9/6/2013 5:10 PM, Leigh wrote: Here's the message it sent me: Detail: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'd '20141-04-30'}' It shows the query: ..., DuesExpire = {d '20141-04-30'}, ... What is the complete error message and generated SQL? Access will not process 20141 as a valid year in a date field. It only goes up to . Dates are stored as double floating point fields with the fraction holding the time and the value representing the date. Access code will take dates as #mm/dd/# format so DuesExpire = #04/30/2014#, would be valid in your UPDATE query. I would not assume the limits on IsDate() or createODBCDate() and the internal database engine date/time representation to be working off the same rules. -- LinkedIn: http://www.linkedin.com/pub/8/a4/60 Twitter: http://twitter.com/RogerTheGeek Blog: http://rogerthegeek.wordpress.com/ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356724 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CreateODBCDate()
Access will not process 20141 as a valid year in a date field. Yep, but if the error message is to be believed, it suggests a different problem like an empty variable, bad quotes, etcetera. So I would be interested in seeing the full error and sql. -Leig ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356726 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
createodbcdate
Hi there I am trying to finish an application. I want to create two dates - a start date and and an ending date which is 5 days after the start date. I have #CreateODBCDate(eventdate)#, for creating the start date and I tried to use #CreateODBCDate(eventdate)+5# for the end date - but it did not like this - just wondering if anyone has any ideas. Thanks in advance. ~| Create Web Applications With ColdFusion MX7 Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:283749 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: createodbcdate
That should work. I've done the same thing with DateFormat. Try DateAdd(). -Original Message- From: Peter Tanswell [mailto:[EMAIL PROTECTED] Sent: Monday, July 16, 2007 9:15 AM To: CF-Talk Subject: createodbcdate Hi there I am trying to finish an application. I want to create two dates - a start date and and an ending date which is 5 days after the start date. I have #CreateODBCDate(eventdate)#, for creating the start date and I tried to use #CreateODBCDate(eventdate)+5# for the end date - but it did not like this - just wondering if anyone has any ideas. Thanks in advance. ~| Create Web Applications With ColdFusion MX7 Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:283750 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: createodbcdate
Well there is the dataAdd() function in the documentation. Something like CreateODBCDate(dateAdd(d,5,eventDate))# Peter Tanswell wrote: Hi there I am trying to finish an application. I want to create two dates - a start date and and an ending date which is 5 days after the start date. I have #CreateODBCDate(eventdate)#, for creating the start date and I tried to use #CreateODBCDate(eventdate)+5# for the end date - but it did not like this - just wondering if anyone has any ideas. Thanks in advance. ~| Create Web Applications With ColdFusion MX7 Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:283751 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: createodbcdate
I tried to use #CreateODBCDate(eventdate)+5# for the end date - but it did not like this - just wondering if anyone has any ideas. CreateODBCDate(eventdate+5) should work better. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| ColdFusion 8 beta â Build next generation applications today. Free beta download on Labs http://www.adobe.com/cfusion/entitlement/index.cfm?e=labs_adobecf8_beta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:283754 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: createodbcdate
Definitely Use DateAdd(). Robert B. Harrison Director of Interactive services Austin Williams 125 Kennedy Drive, Suite 100 Hauppauge NY 11788 T : 631.231.6600 Ext. 119 F : 631.434.7022 www.austin-williams.com Great advertising can't be either/or... It must be . -Original Message- From: Andy Matthews [mailto:[EMAIL PROTECTED] Sent: Monday, July 16, 2007 10:30 AM To: CF-Talk Subject: RE: createodbcdate That should work. I've done the same thing with DateFormat. Try DateAdd(). -Original Message- From: Peter Tanswell [mailto:[EMAIL PROTECTED] Sent: Monday, July 16, 2007 9:15 AM To: CF-Talk Subject: createodbcdate Hi there I am trying to finish an application. I want to create two dates - a start date and and an ending date which is 5 days after the start date. I have #CreateODBCDate(eventdate)#, for creating the start date and I tried to use #CreateODBCDate(eventdate)+5# for the end date - but it did not like this - just wondering if anyone has any ideas. Thanks in advance. ~| CF 8 â Scorpio beta now available, easily build great internet experiences â Try it now on Labs http://www.adobe.com/cfusion/entitlement/index.cfm?e=labs_adobecf8_beta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:283753 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Best use for createODBCdate(), cfqueryparam CF_SQL_DATE vs. CF_SQL_TIMESTAMP
I was wondering if someone can point out the main differences (advantages/disadvantages of each) between #createODBCdate()#, cfqueryparam cfsqltype=CF_SQL_DATE., and cfqueryparam cfsqltype=CF_SQL_TIMESTAMP. when used on inserts and updates. Thanks in advance. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:253924 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Best use for createODBCdate(), cfqueryparam CF_SQL_DATE vs. CF_SQL_TIMESTAMP
Not every DB supports every CFQueryParam cfsqltype. For instance, I used to use _DATE in MS SQL Server, but then one day it started throwing errors on SQL Server 2005. If you look in the documentation, it will show you how everything maps. _TIMESTAMP is supported by MS SQL Server. As far as CreateODBCDate() and CreateODBCDateTime()... Not sure how important they are. If you use CFQueryParam, I believe the tag will format things properly for you. I guess you could use CreateODBCDate() if you wanted to pass a Date/time object with zerod out time. But then again, I don't know if there is any advantage to doing that over just formatting the value you pass to the tag. I imagine that CreateODBCDate() type functions are used mainly for databases that do not allow data binding and therefore do not allow CFQueryParam?? More of a backwards compatability thing... But I might be talking out of you know where. .. Ben Nadel Certified Advanced ColdFusion Developer www.bennadel.com -Original Message- From: Michael E. Carluen [mailto:[EMAIL PROTECTED] Sent: Friday, September 22, 2006 4:52 PM To: CF-Talk Subject: Best use for createODBCdate(), cfqueryparam CF_SQL_DATE vs. CF_SQL_TIMESTAMP I was wondering if someone can point out the main differences (advantages/disadvantages of each) between #createODBCdate()#, cfqueryparam cfsqltype=CF_SQL_DATE., and cfqueryparam cfsqltype=CF_SQL_TIMESTAMP. when used on inserts and updates. Thanks in advance. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:253926 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Best use for createODBCdate(), cfqueryparam CF_SQL_DATE vs. CF_SQL_TIMESTAMP
PS. Here is where you can see what DB's support which CF_SQL_ http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/comm on/html/wwhelp.htm?context=ColdFusion_Documentationfile=0317.htm .. Ben Nadel Certified Advanced ColdFusion Developer www.bennadel.com -Original Message- From: Michael E. Carluen [mailto:[EMAIL PROTECTED] Sent: Friday, September 22, 2006 4:52 PM To: CF-Talk Subject: Best use for createODBCdate(), cfqueryparam CF_SQL_DATE vs. CF_SQL_TIMESTAMP I was wondering if someone can point out the main differences (advantages/disadvantages of each) between #createODBCdate()#, cfqueryparam cfsqltype=CF_SQL_DATE., and cfqueryparam cfsqltype=CF_SQL_TIMESTAMP. when used on inserts and updates. Thanks in advance. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:253927 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Best use for createODBCdate(), cfqueryparam CF_SQL_DATE vs. CF_SQL_TIMESTAMP
Thanks Ben. I've been using both createOBDC... and cfqueryparam... on mySQL/JDBC and have not encountered a problem- which actually lead to this thread. If both are OK, then which is better. Thanks for mentioning your experience with MSSQL, as I'm sure it's a consideration when writing modular apps. -Original Message- From: Ben Nadel [mailto:[EMAIL PROTECTED] Sent: Friday, September 22, 2006 2:02 PM To: CF-Talk Subject: RE: Best use for createODBCdate(), cfqueryparam CF_SQL_DATE vs. CF_SQL_TIMESTAMP PS. Here is where you can see what DB's support which CF_SQL_ http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/comm on/html/wwhelp.htm?context=ColdFusion_Documentationfile=0317.htm .. Ben Nadel Certified Advanced ColdFusion Developer www.bennadel.com -Original Message- From: Michael E. Carluen [mailto:[EMAIL PROTECTED] Sent: Friday, September 22, 2006 4:52 PM To: CF-Talk Subject: Best use for createODBCdate(), cfqueryparam CF_SQL_DATE vs. CF_SQL_TIMESTAMP I was wondering if someone can point out the main differences (advantages/disadvantages of each) between #createODBCdate()#, cfqueryparam cfsqltype=CF_SQL_DATE., and cfqueryparam cfsqltype=CF_SQL_TIMESTAMP. when used on inserts and updates. Thanks in advance. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:253929 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Best use for createODBCdate(), cfqueryparam CF_SQL_DATE vs. CF_SQL_TIMESTAMP
As far as with is better Not sure. Personally, I never use CreateODBCDate() or CreateODBCDateTime(). If your database supports CFQueryParam tag, I would recommend that one. It has several advantages in terms of query performance. Ex. I would do WHERE date_created = cfqueryparam value=#Now()# cfsqltype=CF_SQL_TIMESTAMP / Instead of ... WHERE date_created = #CreateODBCDateTime( Now() )# In fact, if you are using Now(), you wouldn't even need the CreateODBCDateTime() method call as Now() is already ODBC time stamp. If you want to know about speed and Cfqueryparam... Here is a good post: http://www.harelmalka.com/?m=200608 .. Ben Nadel Certified Advanced ColdFusion Developer www.bennadel.com -Original Message- From: Michael E. Carluen [mailto:[EMAIL PROTECTED] Sent: Friday, September 22, 2006 5:24 PM To: CF-Talk Subject: RE: Best use for createODBCdate(), cfqueryparam CF_SQL_DATE vs. CF_SQL_TIMESTAMP Thanks Ben. I've been using both createOBDC... and cfqueryparam... on mySQL/JDBC and have not encountered a problem- which actually lead to this thread. If both are OK, then which is better. Thanks for mentioning your experience with MSSQL, as I'm sure it's a consideration when writing modular apps. -Original Message- From: Ben Nadel [mailto:[EMAIL PROTECTED] Sent: Friday, September 22, 2006 2:02 PM To: CF-Talk Subject: RE: Best use for createODBCdate(), cfqueryparam CF_SQL_DATE vs. CF_SQL_TIMESTAMP PS. Here is where you can see what DB's support which CF_SQL_ http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/co mm on/html/wwhelp.htm?context=ColdFusion_Documentationfile=0317.htm .. Ben Nadel Certified Advanced ColdFusion Developer www.bennadel.com -Original Message- From: Michael E. Carluen [mailto:[EMAIL PROTECTED] Sent: Friday, September 22, 2006 4:52 PM To: CF-Talk Subject: Best use for createODBCdate(), cfqueryparam CF_SQL_DATE vs. CF_SQL_TIMESTAMP I was wondering if someone can point out the main differences (advantages/disadvantages of each) between #createODBCdate()#, cfqueryparam cfsqltype=CF_SQL_DATE., and cfqueryparam cfsqltype=CF_SQL_TIMESTAMP. when used on inserts and updates. Thanks in advance. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:253931 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
CreateOdbcDate(column - not variable) within a CFQuery
Help!! This is not going well. Need to run a query returning all events that happened during the 0405 fiscal year (1 August 2004 - 31 July 2005). Realdate is the column in the events table storing the eventdate (format: dd-mmm-) in a date field in Oracle. Keep getting the error: Variable REALDATE is undefined. And I agree, realdate is not a variable, but that does not help me, as I need to convert the value stored in that column to an odbc date in order to do the 'between' comparison. Can anyone help me?? Please? Gettin' really frustrated!! And I need this to work! Thanks. Molly cfquery name=findlastyear datasource=enrichment select * from mydevelopmentevents where #createodbcdate(createdate (dateformat(realdate,''),dateformat(realdate,'mm'),dateformat(realdate,'dd')))# Between #CreateODBCDate(begin)# and #CreateODBCDate(end)# order by realdate /cfquery ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:218621 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: CreateOdbcDate(column - not variable) within a CFQuery
Looks like you are mixing CF and Oracle processing. CreateODBCDate() is a CFML function, it is processed by CF so the data you pass to it must exist within CF, it can't be a database column. For this filtering you need to use Oracle date functions, not CF ones. --- Ben -Original Message- From: M [mailto:[EMAIL PROTECTED] Sent: Monday, September 19, 2005 1:20 PM To: CF-Talk Subject: CreateOdbcDate(column - not variable) within a CFQuery Help!! This is not going well. Need to run a query returning all events that happened during the 0405 fiscal year (1 August 2004 - 31 July 2005). Realdate is the column in the events table storing the eventdate (format: dd-mmm-) in a date field in Oracle. Keep getting the error: Variable REALDATE is undefined. And I agree, realdate is not a variable, but that does not help me, as I need to convert the value stored in that column to an odbc date in order to do the 'between' comparison. Can anyone help me?? Please? Gettin' really frustrated!! And I need this to work! Thanks. Molly cfquery name=findlastyear datasource=enrichment select * from mydevelopmentevents where #createodbcdate(createdate (dateformat(realdate,''),dateformat(realdate,'mm'),dateformat(realdate,' dd')))# Between #CreateODBCDate(begin)# and #CreateODBCDate(end)# order by realdate /cfquery ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:218622 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CreateOdbcDate(column - not variable) within a CFQuery
Nooo. . . . though I know the answer is yes. . . I was just hoping that the fact that my head hurts over this was just a function of it being Monday morning! Thank you! So sad. . . I really needed to do the conversion/comparison within the query so that I could create the lovely pie charts they like so much for the annual report (one pie chart -- all programs). Thanks again! M On 9/19/05, Ben Forta [EMAIL PROTECTED] wrote: Looks like you are mixing CF and Oracle processing. CreateODBCDate() is a CFML function, it is processed by CF so the data you pass to it must exist within CF, it can't be a database column. For this filtering you need to use Oracle date functions, not CF ones. --- Ben -Original Message- From: M [mailto:[EMAIL PROTECTED] Sent: Monday, September 19, 2005 1:20 PM To: CF-Talk Subject: CreateOdbcDate(column - not variable) within a CFQuery Help!! This is not going well. Need to run a query returning all events that happened during the 0405 fiscal year (1 August 2004 - 31 July 2005). Realdate is the column in the events table storing the eventdate (format: dd-mmm-) in a date field in Oracle. Keep getting the error: Variable REALDATE is undefined. And I agree, realdate is not a variable, but that does not help me, as I need to convert the value stored in that column to an odbc date in order to do the 'between' comparison. Can anyone help me?? Please? Gettin' really frustrated!! And I need this to work! Thanks. Molly cfquery name=findlastyear datasource=enrichment select * from mydevelopmentevents where #createodbcdate(createdate (dateformat(realdate,''),dateformat(realdate,'mm'),dateformat(realdate,' dd')))# Between #CreateODBCDate(begin)# and #CreateODBCDate(end)# order by realdate /cfquery ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:218623 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CreateOdbcDate(column - not variable) within a CFQuery
You can do it in the query. You just need to use Oracle date functions to do it. Try this: cfquery name=findlastyear datasource=enrichment select * from mydevelopmentevents where realdate Between cfqueryparam cfsqltype=cf_sql_timestamp value=#CreateODBCDate(begin)# and cfqueryparam cfsqltype=cf_sql_timestamp value=#CreateODBCDate(end)# order by realdate /cfquery Not knowing what format begin and end are in means this might still need some fine-tuning. You might not need the createodbcdate at all, though I don't _think_ it will hurt anything as long as you use cfqueryparam - it _should_ convert it to the proper oracle date/time format. You will also want to consider whether time is of importance, here. If you don't add a time for end, I believe you're going to get midnight, which is probably not what you want, as you could be loosing some events that are right at the end of your range. You can use trunc(realdate) to drop off the time from your oracle column (no # signs - it's an oracle function). On 9/19/05, M [EMAIL PROTECTED] wrote: Nooo. . . . though I know the answer is yes. . . I was just hoping that the fact that my head hurts over this was just a function of it being Monday morning! Thank you! So sad. . . I really needed to do the conversion/comparison within the query so that I could create the lovely pie charts they like so much for the annual report (one pie chart -- all programs). ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:218624 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CreateOdbcDate(column - not variable) within a CFQuery
D: You are a rockstar!! Thank you!!! You turned it around. . taught me something new. . .and completely saved a day that looked like it was terminally tanking!! Cannot wait to use cfqueryparam again. . . M ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:218627 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: CreateOdbcDate(column - not variable) within a CFQuery
Cannot wait to use cfqueryparam again. . . M For many reasons, one should almost always use cfqueryparam. There are, unfortunately, a few times when you can not. But otherwise use them. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:218629 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: CreateOdbcDate(column - not variable) within a CFQuery
I hate to sound as ignorant as I am, but I don't use cfqueryparam. Where should I use it and why. Is it just for selects? Mark -Original Message- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: Monday, September 19, 2005 2:56 PM To: CF-Talk Subject: RE: CreateOdbcDate(column - not variable) within a CFQuery Cannot wait to use cfqueryparam again. . . M For many reasons, one should almost always use cfqueryparam. There are, unfortunately, a few times when you can not. But otherwise use them. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:218639 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: CreateOdbcDate(column - not variable) within a CFQuery
You should strive to use cfqueryparam *anywhere* you currently use a CF variable in any SQL statement, not just a select statement. I add the disclaimer in that I don't use cfqueryparam for values that are created in my CF pages. I use them anywhere I accept user-supplied values. * They reduce the threat of sql injection attacks. * They auto-escape single quotes that can bring down the most-powerful of queries. * They increase performance of the query (negligible, I guess). * They look damn impressive. M!ke -Original Message- From: Mark Fuqua [mailto:[EMAIL PROTECTED] Sent: Monday, September 19, 2005 3:36 PM To: CF-Talk Subject: RE: CreateOdbcDate(column - not variable) within a CFQuery I hate to sound as ignorant as I am, but I don't use cfqueryparam. Where should I use it and why. Is it just for selects? Mark ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:218641 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: CreateOdbcDate(column - not variable) within a CFQuery
I hate to sound as ignorant as I am, but I don't use cfqueryparam. Where should I use it and why. Is it just for selects? Mark You should use cfqueryparam in any SQL statement that uses dynamic, especially user provided values, for security and performance. It is not just for selects but updates and inserts as well. By using cfqueryparam you are allowing your database (if it supports this feature) to create bind variables. This allows the database to create more efficient and reusable processing plans. This usually provides for improved performance. For security; since you have declared your values to be variables, not just a string fragment, this largely mitigates SQL injection attacks where mean users provide SQL code as part of their values to your database, allowing them to circumnavigate security and/or alter or destroy data. Thus, one should use cfqueryparam whenever possible for all values used in SQL built in ColdFusion code. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:218642 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
CreateODBCDate issue
Hi All, I am a small issue with CreateODBCDate I have a variable that is a string its value is '20040109' I am trying this Dateformat(CreateODBCDate(#variables.strfilename#), mm/dd/) But is is returning '12/14/56767' instead of the proper date.Is there something else I should be doing to the sting? Thanks Mike [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: CreateODBCDate issue
The keyword here is that you are using a string. Use createDate() first, then createODBCDateTime() -Original Message- From: Mickael [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 11:55 AM To: CF-Talk Subject: CreateODBCDate issue Hi All, I am a small issue with CreateODBCDate I have a variable that is a string its value is '20040109' I am trying this Dateformat(CreateODBCDate(#variables.strfilename#), mm/dd/) But is is returning '12/14/56767' instead of the proper date.Is there something else I should be doing to the sting? Thanks Mike _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: CreateODBCDate issue
Hi Bryan, Doesn't Createdate() need to have year,month, day ? My string is just one continues string 8 characters long How can I make that work? - Original Message - From: Bryan F. Hogan To: CF-Talk Sent: Wednesday, January 14, 2004 11:57 AM Subject: RE: CreateODBCDate issue The keyword here is that you are using a string. Use createDate() first, then createODBCDateTime() -Original Message- From: Mickael [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 11:55 AM To: CF-Talk Subject: CreateODBCDate issue Hi All, I am a small issue with CreateODBCDate I have a variable that is a string its value is '20040109' I am trying this Dateformat(CreateODBCDate(#variables.strfilename#), mm/dd/) But is is returning '12/14/56767' instead of the proper date.Is there something else I should be doing to the sting? Thanks Mike _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: CreateODBCDate issue
Bryan I answered to quickly.Thanks you advice helped - Original Message - From: Bryan F. Hogan To: CF-Talk Sent: Wednesday, January 14, 2004 11:57 AM Subject: RE: CreateODBCDate issue The keyword here is that you are using a string. Use createDate() first, then createODBCDateTime() -Original Message- From: Mickael [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 11:55 AM To: CF-Talk Subject: CreateODBCDate issue Hi All, I am a small issue with CreateODBCDate I have a variable that is a string its value is '20040109' I am trying this Dateformat(CreateODBCDate(#variables.strfilename#), mm/dd/) But is is returning '12/14/56767' instead of the proper date.Is there something else I should be doing to the sting? Thanks Mike _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: CreateODBCDate issue
Yes use year month date createODBCDateTime(createDate(year(yourString), month(yourString), day(yourString))) Might want to make that a UDF if your going to use it alot. -Original Message- From: Mickael [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 12:03 PM To: CF-Talk Subject: Re: CreateODBCDate issue Hi Bryan, Doesn't Createdate() need to have year,month, day ? My string is just one continues string 8 characters long How can I make that work? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: CreateODBCDate issue
I am a small issue with CreateODBCDate I have a variable that is a string its value is '20040109' I am trying this Dateformat(CreateODBCDate(#variables.strfilename#), mm/dd/) But is is returning '12/14/56767' instead of the proper date. Is there something else I should be doing to the sting? Your string isn't a date - it's an 8 digit long number CreateODBCDate() is expecting a date value, not a numeric - so it's problably looking at it as 20,040,109 days from 1900, which would put it at a stupid year Now, which order are your entries in? Is it MMDD? If so, then convert it to a real date value You can just output it as Mid(strfilename, 5, 2) / Right(strfilename, 2) / Left(strfilename, 4) if you want that output [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: CreateODBCDate - SOLVED
Just so everyone knows there were registry entries left-over in the Web Server OS which ODBC was looking at to pull an incorrect format ODBC date even after we had changed the server date/time to US English (date/time format was still pulling as British english). A call to Microsoft pointed us to incorrect registry entries and BAM problem solved. Something to keep in mind when implementing applications in the UK. CC Casey C Cook/TMG/CSC To: CF-Talk [EMAIL PROTECTED] @CSC cc: Subject: RE: CreateODBCDate 12/12/01 08:14 PM Please respond to cf-talk Bruce - Now when you say cf server clock, are you talking about the operating system clock the coldfusion server is running on, or is there a separate setting which the coldfusion server keeps track of, and if it keeps track of it is this a setting set when coldfusion is installed? Where in the administrator can I check this date/time setting? I havent found one yet. Thanks for your reply! CC Dunwiddie, Bruce To: CF-Talk [EMAIL PROTECTED] BDunwiddie cc: @henrywurst.cSubject: RE: CreateODBCDate om 12/12/01 04:08 PM Please respond to cf-talk createodbcdate is a function all on it's own. it doesn't get a date. you would have to use something like createodbcdate(now()) to get a date, and now() would use the time according to the cf server clock. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 12, 2001 3:06 PM To: CF-Talk Subject: CreateODBCDate Hello - Can anyone tell me exactly where the createODBCdate function in coldfusion gets its date from? The problem we are running into is dateadd function not working in the UK. We think we've tracked the problem to the web server and not necessarily the coldfusion server itself because we have the same version running in the same enviroment in the US. Your insight is appreciated! CC Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: CreateODBCDate
CC, Can I assume that you're having problems with dates being treated as though they were being represented as MM/DD/YY? This is a common stumbling block and sadly doesn't affect anyone in the US. I have attached one of my many FAQs on this subject for your information. Hope this is of use. Steve -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 13 December 2001 02:14 To: CF-Talk Subject: RE: CreateODBCDate Bruce - Now when you say cf server clock, are you talking about the operating system clock the coldfusion server is running on, or is there a separate setting which the coldfusion server keeps track of, and if it keeps track of it is this a setting set when coldfusion is installed? Where in the administrator can I check this date/time setting? I havent found one yet. Thanks for your reply! CC Dunwiddie, Bruce To: CF-Talk [EMAIL PROTECTED] BDunwiddie cc: @henrywurst.cSubject: RE: CreateODBCDate om 12/12/01 04:08 PM Please respond to cf-talk createodbcdate is a function all on it's own. it doesn't get a date. you would have to use something like createodbcdate(now()) to get a date, and now() would use the time according to the cf server clock. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 12, 2001 3:06 PM To: CF-Talk Subject: CreateODBCDate Hello - Can anyone tell me exactly where the createODBCdate function in coldfusion gets its date from? The problem we are running into is dateadd function not working in the UK. We think we've tracked the problem to the web server and not necessarily the coldfusion server itself because we have the same version running in the same enviroment in the US. Your insight is appreciated! CC ~~ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
CreateODBCDate
Hello - Can anyone tell me exactly where the createODBCdate function in coldfusion gets its date from? The problem we are running into is dateadd function not working in the UK. We think we've tracked the problem to the web server and not necessarily the coldfusion server itself because we have the same version running in the same enviroment in the US. Your insight is appreciated! CC ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: CreateODBCDate
createodbcdate is a function all on it's own. it doesn't get a date. you would have to use something like createodbcdate(now()) to get a date, and now() would use the time according to the cf server clock. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 12, 2001 3:06 PM To: CF-Talk Subject: CreateODBCDate Hello - Can anyone tell me exactly where the createODBCdate function in coldfusion gets its date from? The problem we are running into is dateadd function not working in the UK. We think we've tracked the problem to the web server and not necessarily the coldfusion server itself because we have the same version running in the same enviroment in the US. Your insight is appreciated! CC ~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: CreateODBCDate
Bruce - Now when you say cf server clock, are you talking about the operating system clock the coldfusion server is running on, or is there a separate setting which the coldfusion server keeps track of, and if it keeps track of it is this a setting set when coldfusion is installed? Where in the administrator can I check this date/time setting? I havent found one yet. Thanks for your reply! CC Dunwiddie, Bruce To: CF-Talk [EMAIL PROTECTED] BDunwiddie cc: @henrywurst.cSubject: RE: CreateODBCDate om 12/12/01 04:08 PM Please respond to cf-talk createodbcdate is a function all on it's own. it doesn't get a date. you would have to use something like createodbcdate(now()) to get a date, and now() would use the time according to the cf server clock. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 12, 2001 3:06 PM To: CF-Talk Subject: CreateODBCDate Hello - Can anyone tell me exactly where the createODBCdate function in coldfusion gets its date from? The problem we are running into is dateadd function not working in the UK. We think we've tracked the problem to the web server and not necessarily the coldfusion server itself because we have the same version running in the same enviroment in the US. Your insight is appreciated! CC ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: CreateODBCDate
Now when you say cf server clock, are you talking about the operating system clock the coldfusion server is running on, or is there a separate setting which the coldfusion server keeps track of, and if it keeps track of it is this a setting set when coldfusion is installed? Where in the administrator can I check this date/time setting? I havent found one yet. The Now function returns the time from the operating system clock. CF doesn't have its own internal clock. Can anyone tell me exactly where the createODBCdate function in coldfusion gets its date from? The problem we are running into is dateadd function not working in the UK. We think we've tracked the problem to the web server and not necessarily the coldfusion server itself because we have the same version running in the same enviroment in the US. The CreateODBCDate function returns an ODBC-compliant date string, which looks something like this: {d '2001-12-13'} which is 13 December 2001. My guess is that your operating system is set to UK English settings, and that CF is picking up on that. You might look at the International Functions section of your CF function documentation to see how to deal with the issue programmatically. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Repost - CreateODBCDate Error
Try this: CFSET EndDay = CreateODBCDate(CreateDate(ByYear,ByMonth, DaysInMonth(createdate(byyear,bymonth,1 Regards, Howie Hamlin -- inFusion Project Manager; On-Line Data Solutions, Inc. (631)737-4668 www.CoolFusion.com Please vote for iMS in the Most Innovative category here: *** http://www.sys-con.com/ColdFusion/readerschoice2000/ *** Check out inFusion Mail Server - the world's most configurable email server - Original Message - From: Kevin Queen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 26, 2000 12:07 AM Subject: Repost - CreateODBCDate Error I sent this on Tuesday and never saw it come up on the list (I thought we had this slow list issue fixed) so I am reposting, sorry if you are getting this twice. -Kevin -Original Message- From: Kevin Queen [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 25, 2000 4:18 PM To: '[EMAIL PROTECTED]' Subject: CreateODBCDate Error I have a 'function' : CFSET EndDay = #CreateODBCDate(CreateDate(#ByYear#, #ByMonth#, DaysInMonth(#ByMonth#)))# When I set #ByMonth# to 9 or 11 the function sets days to 31 and the template fails (obviously) is this a known error with CF4.5, NT 4.0, IIS 4.0, and SP6? Is there a valid work around? TIA, Kevin Queen -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: simple createODBCdate question
Date is definitely a reserved word in Access, and will throw errors. -d Deanna Schneider Interactive Media Developer UWEX Cooperative Extension Electronic Publishing Group 103 Extension Bldg 432 N. Lake Street Madison, WI 53706 (608) 265-7923 -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
simple createODBCdate question
Ok, I thought I had the jist of this but. no... I am doing a query on a table that has a date field. Here is the code: CFSET NewDate=CreateODBCDate(#dateformat(Now(),"mm/dd/yy")#) CFQUERY NAME="myquery" DATASOURCE="mydb" select field_1,field_2 FROM mytable where date = #newdate# /CFQUERY I get the ole' "[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. " error on this. Am I not using the CreatODBCdate properly? Thanks George Loch Stephen Hales Creative [EMAIL PROTECTED] -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: simple createODBCdate question
Here is the code: CFSET NewDate=CreateODBCDate(#dateformat(Now(),"mm/dd/yy")#) Should be cfset NewDate = #CreateODBCDate(Now())# Pound signs always go on the outside, any text not escaped (inside single or double quotes) insides the pound signs are interpreted as variables. And creating a date will automatically format the date correctly, so no need for the dateformat command. And the date format for ODBC dates aren't mm/dd/yy anyway, they are {d '-mm-dd'} Hope this helps. Judah -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: simple createODBCdate question
Hmmm... I get the same error -Original Message- From: Judah McAuley [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 19, 2000 3:52 PM To: [EMAIL PROTECTED] Subject: Re: simple createODBCdate question Here is the code: CFSET NewDate=CreateODBCDate(#dateformat(Now(),"mm/dd/yy")#) Should be cfset NewDate = #CreateODBCDate(Now())# Pound signs always go on the outside, any text not escaped (inside single or double quotes) insides the pound signs are interpreted as variables. And creating a date will automatically format the date correctly, so no need for the dateformat command. And the date format for ODBC dates aren't mm/dd/yy anyway, they are {d '-mm-dd'} Hope this helps. Judah -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: simple createODBCdate question
Hello George, cfset newdate = Now() is just as sufficient as it can get... WHERE date = newdate should work then... I am hesitating, 'cause 'date' is possibly a reserved word either in CF or in SQL. Somebody help me out here:-)) Birgit Wednesday, April 19, 2000, 5:32:39 PM, you wrote: Ok, I thought I had the jist of this but. no... I am doing a query on a table that has a date field. Here is the code: CFSET NewDate=CreateODBCDate(#dateformat(Now(),"mm/dd/yy")#) CFQUERY NAME="myquery" DATASOURCE="mydb" select field_1,field_2 FROM mytable where date = #newdate# /CFQUERY I get the ole' "[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. " error on this. Am I not using the CreatODBCdate properly? Thanks George Loch Stephen Hales Creative [EMAIL PROTECTED] -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: simple createODBCdate question
DOH! That was the issue. I had changed my field name to be compliant and didn't change it in the cfm document. I will sulk back into my read-only mode :) Thanks for all the help folks! George -Original Message- From: Birgit Pauli-Haack [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 19, 2000 5:23 PM To: George Loch Subject: Re: simple createODBCdate question Hello George, cfset newdate = Now() is just as sufficient as it can get... WHERE date = newdate should work then... I am hesitating, 'cause 'date' is possibly a reserved word either in CF or in SQL. Somebody help me out here:-)) Birgit Wednesday, April 19, 2000, 5:32:39 PM, you wrote: Ok, I thought I had the jist of this but. no... I am doing a query on a table that has a date field. Here is the code: CFSET NewDate=CreateODBCDate(#dateformat(Now(),"mm/dd/yy")#) CFQUERY NAME="myquery" DATASOURCE="mydb" select field_1,field_2 FROM mytable where date = #newdate# /CFQUERY I get the ole' "[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. " error on this. Am I not using the CreatODBCdate properly? Thanks George Loch Stephen Hales Creative [EMAIL PROTECTED] -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: simple createODBCdate question
in this case, the pound signs really aren't even necessary because you're already in a cfset statement. i think the recommendation is to not use them inside of cf tags because you're "over-coding". try: cfset NewDate = CreateODBCDate(Now()) -emily CFSET NewDate=CreateODBCDate(#dateformat(Now(),"mm/dd/yy")#) Should be cfset NewDate = #CreateODBCDate(Now())# Pound signs always go on the outside, any text not escaped (inside single or double quotes) insides the pound signs are interpreted as variables. And creating a date will automatically format the date correctly, so no need for the dateformat command. And the date format for ODBC dates aren't mm/dd/yy anyway, they are {d '-mm-dd'} Hope this helps. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.