RE: [cfaussie] Display XML on webpage
Scott How about cfhttp url=http://aaronkarp.com/prettyXML/; method=POST cfhttpparam name=xml type=FORMFIELD value=rootsomethingsomethingelseHello/somethingelse/something/ro ot / /cfhttp cfset formattedXML = REMatch(pre[^]*/pre, CFHTTP.FileContent) / cfoutput#formattedXML[1]#/cfoutput Steve -Original Message- From: Scott Thornton [mailto:scott.thorn...@hnehealth.nsw.gov.au] Sent: Monday, 7 June 2010 2:54 PM To: cfaussie@googlegroups.com Subject: Re: [cfaussie] Display XML on webpage Hi, I have now. it will also need special formatting to make it more readable. Blair McKenzie shi...@gmail.com 07/06/2010 2:50 pm Have you tried the htmleditformat function? On Mon, Jun 7, 2010 at 2:44 PM, Scott Thornton scott.thorn...@hnehealth.nsw.gov.au wrote: Hi, interestingly it kinda of works, at least IE display the xml now. Originally, i replaced all of the gt; with etc in the query thinking that thwy would be a problem... Ill also try the formatter posted http://alexgorbatchev.com/wiki/SyntaxHighlighter On 07/06/2010 at 2:37 pm, in message 4c0c7794.1070...@ampersand.net.au, m...@ampersand.net.au m...@ampersand.net.au wrote: How about #replace(query.xml,,lt;,all)# Dodgy I know but does it work? On 7/06/2010 2:29 PM, Scott Thornton wrote: Thanks, however: cfcontent variable=#query.xml# returns: Attribute validation error for tag cfcontent. java.lang.String is not a supported variable type. The variable is expected to contain binary data. Andrew Scott andr...@andyscott.id.au ( mailto:andr...@andyscott.id.au ) 07/06/2010 2:22 pm Try using the cfcontent. On Mon, Jun 7, 2010 at 2:18 PM, Scott Thornton scott.thorn...@hnehealth.nsw.gov.au wrote: Hi, I was hoping to achieve something rather simple ( I thought), which was to display on a webpage the xml stored from within a database, in hopefully a formatted manner. The XML is stored unformatted within a database text field. When I save the following XML a text document with an .xml extension, Internet explorer formats it and displays it okay, but a simple output of the database field eg: cfqueryselect /cfquerycfoutput#query.xml#/cfoutput Internet explorer displays an error, The XML page cannot be displayed Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later. Firefox displays it as one long string. Wrapping the cfoutput in CODE/CODE doesn't help. Putting the cfoutput with a CFtextarea helps, but then I need to format it ( indent it to make it more readable ) I guess one potential problem is that the xml string contains another XML string ( partData ) ?xml version=1.0 encoding=UTF-8 standalone=yes? ns1:transmissionMessage transmissionGmt=20100517153248 transmissionId=WEB20117091e4bf161a06700 from=WEB20117 mode=FIXME contentsSigned=false contentsType=MCA/OlcHospital/inhospitalclaimreques...@1 xmlns:ns1= http://hic.gov.au/hiconline/protocol/model; ( http://hic.gov.au/hiconline/protocol/model ) adaptor minorVersion=28 majorVersion=6.10 vendor=HIC platform=os: Windows 2003 x86 5.2 jre: Sun 1.5.0_05 product=HicOnline /adaptor logic minorVersion=56 majorVersion=1 vendor=MCA platform=os: Windows 2003 x86 5.2 jre: Sun 1.5.0_05 product=OlcHospital /logic originator minorVersion=1 majorVersion=2.610 vendor=NOT SPECIFIED platform=os: Windows 2003 x86 5.2 jre: Sun 1.5.0_05 product=Platypus2 /originator pki minorVersion=0 majorVersion=2.3.3 vendor=HIC platform=os: Windows 2003 x86 5.2 jre: Sun 1.5.0_05 product=eSignature /pki content transactionId=WEB22 sendCount=1 requestId=1 role= dataRef=1 type=MCA/OlcHospital/inhospitalclaimreques...@1 signed=false /content partData ?xml version=1.0 encoding=UTF-8 standalone=yes? ns1:inHospitalClaimRequest xmlns:ns1= http://hic.gov.au/hiconline/eclipse/version-1; ( http://hic.gov.au/hiconline/eclipse/version-1 ) claim messagePartId=CID claimTypeCdePU/claimTypeCde facilityId0012790J/facilityId fundBrandIdABC/fundBrandId accountReferenceIdA1234567/accountReferenceId senderContact nameabc 123/name phone0249853321/phone email emailaddr...@hnehealth.nsw.gov.au/email /senderContact contiguousClaimCdeN/contiguousClaimCde urgencyCode9/urgencyCode compensationClaimCdeN/compensationClaimCde totalHospitalChargeAmount58800/totalHospitalChargeAmount totalChargeAmount58800/totalChargeAmount facilityTypeCode1/facilityTypeCode patient messagePartId=PAT patient gender=2 dateOfBirth=1939-01-16+10:00 identity firstName=firstName lastName=lastName/ residentialAddress postcode=2287/ medicare memberRefNum=1 memberNum=1234567707/ healthFund memberNum=1234567/ /patient medicalRecordId97654321/medicalRecordId /patient episode
Re: [cfaussie] Re: import DW sites to new windows install
On Mon, Jun 7, 2010 at 11:34 AM, flexboss compas...@gmail.com wrote: Vista cs4 hard disk:\Users\username\AppData\Roaming\Adobe\Dreamweaver CS4\Configuration Ya, I tried that...didn't work. :( But the export / import function worked fine. :) Thanks On Jun 7, 11:29 am, flexboss compas...@gmail.com wrote: on xp for dw cs3 the path is c:\Documents and Settings\Current User \Application Data\Adobe\Dreamweaver 9\Configuration\SiteCache . Cheers d On Jun 6, 4:19 am, Carl Vanderpal carl.vander...@gmail.com wrote: I think earlier version kept them in the registry..but not sure about the latest ones? Dawesi, the sites are still in the same directory, all I am doing is changing operating system but its still on the same PC, so I can still access them. As Vista is installed on C:\ and WIN 7 is on D:\ (which is just a partition of the same hard drive. :) On Sun, Jun 6, 2010 at 1:59 AM, Steve Onnis st...@cfcentral.com.au wrote: Where are they stored though? In a config file? In the registry? Gotta be somewhere where DW can load them on startup -Original Message- From: Dawesi [mailto:daw...@gmail.com] Sent: Sunday, 6 June 2010 1:58 AM To: cfaussie Subject: [cfaussie] Re: import DW sites to new windows install DW exports ste site files as Sean said... but if you change the directories, then it's all bad. Might be quicker to recreate them. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.comcfaussie%2bunsubscr...@googlegroups.com cfaussie%2bunsubscr...@googlegroups.comcfaussie%252bunsubscr...@googlegroups.com . For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.comcfaussie%2bunsubscr...@googlegroups.com cfaussie%2bunsubscr...@googlegroups.comcfaussie%252bunsubscr...@googlegroups.com . For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- === Postal: Po Box 3462 Dural, NSW 2158 Email: mailto:carl.vander...@gmail.com -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.comcfaussie%2bunsubscr...@googlegroups.com . For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- === Postal: Po Box 3462 Dural, NSW 2158 Email: mailto:carl.vander...@gmail.com -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Consuming a service that requires WSE 3.0 X509 encryption
Hi Brett, As some experts on this topic did not reply... ;) I think you need to obtain the certificate and install it to the server on which CF runs. Then based on the certificate information, you need to create the SAML, which will be included in the security header of your SOAP envelope. Finally you can post this SOAP envelope by CFHTTP. To generate SAML, I tried OpenSAML (if I recall it correctly) unsuccessfully. I ended up with writing a service class in C# and using the DLL from within CF app. I think your client should be able to provide you with the certificate and possibly the snippet of how the SAML would be like or even a service class sample in C# or VB.NET. Hope this helps. Cheers, Terry On 4 June 2010 15:29, brettpr bret...@gmail.com wrote: Hi, An enterprise authentication service I need to consume with CF is secured using Microsoft Web Service Enhancements 3.0 (WSE) and is signed with an X509 certificate. The documentation is quite thin and talks about security tokens embedded in requests without providing the detail. I'm chasing the detail but I'm guessing that I will need to encrypt the data appropriately before calling the service so I am wondering if anyone has had any experience with this and can provide any hints... Cheers, Brett B) -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Odd date behaviour - CF9/SQLServer2005
The problem is probably related to dates where the day could also be interpreted as the month. From the symptoms you're describing, I would guess that -mm-dd is the default but -dd-mm is used to somehow resolve ambiguous dates. Blair On Tue, Jun 8, 2010 at 9:23 AM, Mike Kear afpwebwo...@gmail.com wrote: I have a conundrum that's driving me crazy and frustrating my client too. We have an application where dates are being stored in our database in strange ways. If the day is less than or equal to the 12th of the month, the date is stored as -dd-mm but if the day is after the 12th of the month, it's stored as -mm-dd. (the latter is what we want)I dont understand why the program shouldnt be consistent - always store the date the same way around regardless of whether the day is 12 or less or not. For example if the date is 7th May, I can see using CFDUMP that all throughout the coldfusion app, the beans and other processing it's held as 07/05/2010. But if it's saved into the SQLServer2005 database, it's stored as 2010-07-05 00:00:00.However if the date is 23 May, the beans etc show it as 23/05/2010 but it's saved in the database as 2010-05-23 00:00:00 The database field is defined as smalldatetime.The insert statement for the record inserts the value ECRTTotal.getdateentered() as follows: #createodbcdate(ECRTTotal.getdateentered())# I've also used cfqueryparam value=#ECRTTotal.getdateentered()# cfsqltype=CF_SQL_TIMESTAMP / and that gives the same result. (the variable ECRTTotal.getdateentered() is definitely a date object being created by createdate() function. There is a huge amount of existing code that works ok except for this issue, so i need to be careful about changing anything that might break bits of the app that are already working. Anyone have any ideas where i should look for the cause/solution? -- Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.comcfaussie%2bunsubscr...@googlegroups.com . For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
RE: [cfaussie] Odd date behaviour - CF9/SQLServer2005
in what format is ECRTTotal.getdateentered() returning the date? _ From: Mike Kear [mailto:afpwebwo...@gmail.com] Sent: Tuesday, 8 June 2010 9:23 AM To: cfaussie Subject: [cfaussie] Odd date behaviour - CF9/SQLServer2005 I have a conundrum that's driving me crazy and frustrating my client too. We have an application where dates are being stored in our database in strange ways. If the day is less than or equal to the 12th of the month, the date is stored as -dd-mm but if the day is after the 12th of the month, it's stored as -mm-dd. (the latter is what we want)I dont understand why the program shouldnt be consistent - always store the date the same way around regardless of whether the day is 12 or less or not. For example if the date is 7th May, I can see using CFDUMP that all throughout the coldfusion app, the beans and other processing it's held as 07/05/2010. But if it's saved into the SQLServer2005 database, it's stored as 2010-07-05 00:00:00.However if the date is 23 May, the beans etc show it as 23/05/2010 but it's saved in the database as 2010-05-23 00:00:00 The database field is defined as smalldatetime.The insert statement for the record inserts the value ECRTTotal.getdateentered() as follows: #createodbcdate(ECRTTotal.getdateentered())# I've also used cfqueryparam value=#ECRTTotal.getdateentered()# cfsqltype=CF_SQL_TIMESTAMP / and that gives the same result. (the variable ECRTTotal.getdateentered() is definitely a date object being created by createdate() function. There is a huge amount of existing code that works ok except for this issue, so i need to be careful about changing anything that might break bits of the app that are already working. Anyone have any ideas where i should look for the cause/solution? -- Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Odd date behaviour - CF9/SQLServer2005
SQL Server is not ODBC compliant? -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Odd date behaviour - CF9/SQLServer2005
@Steve: the date is created by the following, so it should be a coldfusion date object: cfset thisdate = createdate( thisyear, thismonth, thisday ) / cfset ECRTTotal.setdateentered( thisdate) / @Blair, I think you're right - the difference is between dates less than or equal to 12th in the month, where the day value could be either month or day. But how to ensure that it always puts the day value in the correct place is the question I can't answer yet. @Peter, yes SQLServer is definitely ODBC compliant. In fact you have plenty of your own databases on this same box. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Tue, Jun 8, 2010 at 9:55 AM, Peter Tilbrook peter.tilbr...@gmail.comwrote: SQL Server is not ODBC compliant? - -- -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
RE: [cfaussie] Odd date behaviour - CF9/SQLServer2005
Have lots of code on SQL server. Using software run by both AU US users so we are dealing with these types of date issues all the time. But have never had the issue you are talking about. We always use createODBCDate and don't have any issues. Regards Dale Fraser http://dale.fraser.id.au http://cfmldocs.com http://cfmldocs.com/ http://learncf.com http://flexcf.com From: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] On Behalf Of Mike Kear Sent: Tuesday, 8 June 2010 10:20 AM To: cfaussie@googlegroups.com Subject: Re: [cfaussie] Odd date behaviour - CF9/SQLServer2005 @Steve: the date is created by the following, so it should be a coldfusion date object: cfset thisdate = createdate( thisyear, thismonth, thisday ) / cfset ECRTTotal.setdateentered( thisdate) / @Blair, I think you're right - the difference is between dates less than or equal to 12th in the month, where the day value could be either month or day. But how to ensure that it always puts the day value in the correct place is the question I can't answer yet. @Peter, yes SQLServer is definitely ODBC compliant. In fact you have plenty of your own databases on this same box. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Tue, Jun 8, 2010 at 9:55 AM, Peter Tilbrook peter.tilbr...@gmail.com wrote: SQL Server is not ODBC compliant? - -- -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
RE: [cfaussie] Odd date behaviour - CF9/SQLServer2005
Yeah good point Other things like AMF or renders might just be displaying it wrong. Regards Dale Fraser http://dale.fraser.id.au http://cfmldocs.com http://learncf.com http://flexcf.com -Original Message- From: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] On Behalf Of Kym Kovan Sent: Tuesday, 8 June 2010 10:32 AM To: cfaussie@googlegroups.com Subject: Re: [cfaussie] Odd date behaviour - CF9/SQLServer2005 On 8/06/2010 09:23, Mike Kear wrote: I have a conundrum that's driving me crazy and frustrating my client too. We have an application where dates are being stored in our database in strange ways. For a sanity check can you confirm that the date when you read it from the database with a query is different to the one you put in? Or are you just seeing it different in SQL Management Studio? -- Yours, Kym Kovan mbcomms.net.au -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
RE: [cfaussie] Odd date behaviour - CF9/SQLServer2005
I would say its a localisation issue on the server _ From: Mike Kear [mailto:afpwebwo...@gmail.com] Sent: Tuesday, 8 June 2010 10:20 AM To: cfaussie@googlegroups.com Subject: Re: [cfaussie] Odd date behaviour - CF9/SQLServer2005 @Steve: the date is created by the following, so it should be a coldfusion date object: cfset thisdate = createdate( thisyear, thismonth, thisday ) / cfset ECRTTotal.setdateentered( thisdate) / @Blair, I think you're right - the difference is between dates less than or equal to 12th in the month, where the day value could be either month or day. But how to ensure that it always puts the day value in the correct place is the question I can't answer yet. @Peter, yes SQLServer is definitely ODBC compliant. In fact you have plenty of your own databases on this same box. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Tue, Jun 8, 2010 at 9:55 AM, Peter Tilbrook peter.tilbr...@gmail.com wrote: SQL Server is not ODBC compliant? - -- -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
RE: [cfaussie] Odd date behaviour - CF9/SQLServer2005
Still think it might be a localisation problem _ From: Mike Kear [mailto:afpwebwo...@gmail.com] Sent: Tuesday, 8 June 2010 12:18 PM To: cfaussie@googlegroups.com Subject: Re: [cfaussie] Odd date behaviour - CF9/SQLServer2005 @Kym: you make an excellent point.I normally look at data in a table by running a query, so i'm looking at query results in Management Studio.So i used the Open table and looked at the data that way, and still see the same effect. I have dates in the table showing as 23/05/2010 12:00:00 AM and 5/02/2010 12:00:00 AM when they should all show as being in May. Also if i run a query on the table in coldfusion and cfdump the results, i see the same results. @Dale, thanks for your input. I also have 18 other databases on this server, and don't have this problem with them, and I usually use either createodbcdate() or cfqueryparam with a type of CF_SQL_TIMESTAMP or CF_SQL_DATE. However in this case I have about 300 files of historical code that has worked fine on a server at fasthit and now i have it on my server it's giving this result. Re-writing the whole application is not a reasonable option, because the client's not going to pay me to re-write his existing code, no matter how much I'd like to. The code is legacy, dating back to CF5 for the most part and i would dearly love to see it modernised for a lot of reasons. But that is going to have to wait. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Tue, Jun 8, 2010 at 11:19 AM, Steve Onnis st...@cfcentral.com.au wrote: I would say its a localisation issue on the server _ From: Mike Kear [mailto:afpwebwo...@gmail.com] Sent: Tuesday, 8 June 2010 10:20 AM To: cfaussie@googlegroups.com Subject: Re: [cfaussie] Odd date behaviour - CF9/SQLServer2005 @Steve: the date is created by the following, so it should be a coldfusion date object: cfset thisdate = createdate( thisyear, thismonth, thisday ) / cfset ECRTTotal.setdateentered( thisdate) / @Blair, I think you're right - the difference is between dates less than or equal to 12th in the month, where the day value could be either month or day. But how to ensure that it always puts the day value in the correct place is the question I can't answer yet. @Peter, yes SQLServer is definitely ODBC compliant. In fact you have plenty of your own databases on this same box. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Odd date behaviour - CF9/SQLServer2005
@Steve Yes It might well be a localisation problem.The server is sitting in California, so one would expect that the defaults might be different to the defaults on a box sitting in Australia. But how do I change the localisation in this database? I cant change any server-wide settings. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Tue, Jun 8, 2010 at 12:21 PM, Steve Onnis st...@cfcentral.com.au wrote: Still think it might be a localisation problem -- *From:* Mike Kear [mailto:afpwebwo...@gmail.com] *Sent:* Tuesday, 8 June 2010 12:18 PM *To:* cfaussie@googlegroups.com *Subject:* Re: [cfaussie] Odd date behaviour - CF9/SQLServer2005 @Kym: you make an excellent point.I normally look at data in a table by running a query, so i'm looking at query results in Management Studio. So i used the Open table and looked at the data that way, and still see the same effect. I have dates in the table showing as 23/05/2010 12:00:00 AM and 5/02/2010 12:00:00 AM when they should all show as being in May. Also if i run a query on the table in coldfusion and cfdump the results, i see the same results. @Dale, thanks for your input. I also have 18 other databases on this server, and don't have this problem with them, and I usually use either createodbcdate() or cfqueryparam with a type of CF_SQL_TIMESTAMP or CF_SQL_DATE. However in this case I have about 300 files of historical code that has worked fine on a server at fasthit and now i have it on my server it's giving this result. Re-writing the whole application is not a reasonable option, because the client's not going to pay me to re-write his existing code, no matter how much I'd like to. The code is legacy, dating back to CF5 for the most part and i would dearly love to see it modernised for a lot of reasons. But that is going to have to wait. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
RE: [cfaussie] Odd date behaviour - CF9/SQLServer2005
I would suggest that your code where you put it into the database is wrong then. When you generate the date you are getting it back to front. ColdFusion assumes USA, but if that isn't valid will swap it. So if you say 15/01/2010 You get 15th Jan 10/01/2010 You get 1st October Regards Dale Fraser http://dale.fraser.id.au http://cfmldocs.com http://cfmldocs.com/ http://learncf.com http://flexcf.com From: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] On Behalf Of Mike Kear Sent: Tuesday, 8 June 2010 12:18 PM To: cfaussie@googlegroups.com Subject: Re: [cfaussie] Odd date behaviour - CF9/SQLServer2005 @Kym: you make an excellent point.I normally look at data in a table by running a query, so i'm looking at query results in Management Studio.So i used the Open table and looked at the data that way, and still see the same effect. I have dates in the table showing as 23/05/2010 12:00:00 AM and 5/02/2010 12:00:00 AM when they should all show as being in May. Also if i run a query on the table in coldfusion and cfdump the results, i see the same results. @Dale, thanks for your input. I also have 18 other databases on this server, and don't have this problem with them, and I usually use either createodbcdate() or cfqueryparam with a type of CF_SQL_TIMESTAMP or CF_SQL_DATE. However in this case I have about 300 files of historical code that has worked fine on a server at fasthit and now i have it on my server it's giving this result. Re-writing the whole application is not a reasonable option, because the client's not going to pay me to re-write his existing code, no matter how much I'd like to. The code is legacy, dating back to CF5 for the most part and i would dearly love to see it modernised for a lot of reasons. But that is going to have to wait. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Tue, Jun 8, 2010 at 11:19 AM, Steve Onnis st...@cfcentral.com.au wrote: I would say its a localisation issue on the server _ From: Mike Kear [mailto:afpwebwo...@gmail.com] Sent: Tuesday, 8 June 2010 10:20 AM To: cfaussie@googlegroups.com Subject: Re: [cfaussie] Odd date behaviour - CF9/SQLServer2005 @Steve: the date is created by the following, so it should be a coldfusion date object: cfset thisdate = createdate( thisyear, thismonth, thisday ) / cfset ECRTTotal.setdateentered( thisdate) / @Blair, I think you're right - the difference is between dates less than or equal to 12th in the month, where the day value could be either month or day. But how to ensure that it always puts the day value in the correct place is the question I can't answer yet. @Peter, yes SQLServer is definitely ODBC compliant. In fact you have plenty of your own databases on this same box. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
RE: [cfaussie] Odd date behaviour - CF9/SQLServer2005
I don't think it is, Write some test code, my money is that you can't duplicate it unless you create the date incorrectly in the first place. Regards Dale Fraser http://dale.fraser.id.au http://cfmldocs.com http://cfmldocs.com/ http://learncf.com http://flexcf.com From: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] On Behalf Of Mike Kear Sent: Tuesday, 8 June 2010 12:28 PM To: cfaussie@googlegroups.com Subject: Re: [cfaussie] Odd date behaviour - CF9/SQLServer2005 @Steve Yes It might well be a localisation problem.The server is sitting in California, so one would expect that the defaults might be different to the defaults on a box sitting in Australia. But how do I change the localisation in this database? I cant change any server-wide settings. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Tue, Jun 8, 2010 at 12:21 PM, Steve Onnis st...@cfcentral.com.au wrote: Still think it might be a localisation problem _ From: Mike Kear [mailto:afpwebwo...@gmail.com] Sent: Tuesday, 8 June 2010 12:18 PM To: cfaussie@googlegroups.com Subject: Re: [cfaussie] Odd date behaviour - CF9/SQLServer2005 @Kym: you make an excellent point.I normally look at data in a table by running a query, so i'm looking at query results in Management Studio.So i used the Open table and looked at the data that way, and still see the same effect. I have dates in the table showing as 23/05/2010 12:00:00 AM and 5/02/2010 12:00:00 AM when they should all show as being in May. Also if i run a query on the table in coldfusion and cfdump the results, i see the same results. @Dale, thanks for your input. I also have 18 other databases on this server, and don't have this problem with them, and I usually use either createodbcdate() or cfqueryparam with a type of CF_SQL_TIMESTAMP or CF_SQL_DATE. However in this case I have about 300 files of historical code that has worked fine on a server at fasthit and now i have it on my server it's giving this result. Re-writing the whole application is not a reasonable option, because the client's not going to pay me to re-write his existing code, no matter how much I'd like to. The code is legacy, dating back to CF5 for the most part and i would dearly love to see it modernised for a lot of reasons. But that is going to have to wait. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Odd date behaviour - CF9/SQLServer2005
@Dale, I would have said that too. My first assumption was that i had an error in my code somewhere. So I did a CFDUMP of the variables that I'm inserting into the table and they show the dates the way I want them. The coldfusion createdate function receives 3 numeric variables that are definitely year, month, day, so I'm pretty sure that's working ok (i.e. createdate(thisyear, thismonth, thisday) ) and if i CFDUMP the resulting date object and output it with dateformat, I see it how i want it - the month is correctly identified there. Then i assumed i must be allowing some ambiguity in the date in my insert statement so I used the ColdFusion debugging to show me the actual query used to insert the record, and it shows the dates correctly too. Specifically, it shows an insert of a date 2010-05-08 that appears subsequently in the database as 2010-08-05 I think your earlier assumption is quite likely - that it's something to do with localisation on the database, rather than the ColdFusion code. Is there a way to set localisation on a database as a kind of database setting rather than in each query? (this is SQLServer2005) Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Tue, Jun 8, 2010 at 12:36 PM, Dale Fraser d...@fraser.id.au wrote: I don’t think it is, Write some test code, my money is that you can’t duplicate it unless you create the date incorrectly in the first place. Regards Dale Fraser http://dale.fraser.id.au http://cfmldocs.com http://learncf.com http://flexcf.com * * -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
[cfaussie] Re: Odd date behaviour - CF9/SQLServer2005
We had this error at RMIT and it took forever to work out what was causing the issue. We were using MS-SQL Server 2005 I don't remember the EXACT sytntax; but we ended up using SET LOCALE = US In all of our stored procs and handwritten (CFML-based) SQL) to get around this issue. Apparently there is a server wide setting for it - but we never found where it was - prior to me leaving... and thus ALWAYS ensured that we manually set the locale in every SQL we wrote. Here at my current job all Dates are preformatted; #dateformat(date, -MM-DD)# This is an OSI standard for date handling that all databases follow. Also a note for young rockers... don't leave the mask off your dateformat commands it defaults to mm-dd- - we had this in a few places and ended up with dates being stored inaccurately where the month was less than 12. We had to go through the tables and order by date then by PKey and hope there was a great enough difference between the timestamps and the PK's to identify the block of dates that needed to have the MM and DD swapped. - took us over a week to ensure that we correct / clean data - it was a major pain in the preverbial! Gavin. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Re: Odd date behaviour - CF9/SQLServer2005
Try this - http://blog.sqlauthority.com/2007/08/30/sql-server-2005-find-database-collation-using-t-sql-and-ssms/ - it has instructions for getting the collation value. What is it? On 8/06/2010 1:14 PM, Gavin Baumanis wrote: We had this error at RMIT and it took forever to work out what was causing the issue. We were using MS-SQL Server 2005 I don't remember the EXACT sytntax; but we ended up using SET LOCALE = "US" In all of our stored procs and handwritten (CFML-based) SQL) to get around this issue. Apparently there is a server wide setting for it - but we never found where it was - prior to me leaving... and thus ALWAYS ensured that we manually set the locale in every SQL we wrote. Here at my current job all Dates are preformatted; #dateformat(date, "-MM-DD")# This is an OSI standard for date handling that all databases follow. Also a note for young rockers... don't leave the mask off your dateformat commands it defaults to mm-dd- - we had this in a few places and ended up with dates being stored inaccurately where the month was less than 12. We had to go through the tables and order by date then by PKey and hope there was a great enough difference between the timestamps and the PK's to identify the block of dates that needed to have the MM and DD swapped. - took us over a week to ensure that we correct / clean data - it was a major pain in the preverbial! Gavin. -- You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Re: Odd date behaviour - CF9/SQLServer2005
@mgk: I'm doing the updates using ColdFusion. The only property I can find that's remotely associated with language or nationality or locale is the collation = SQL_Latin1_General_CP1_CI_AS Can this be set to something else? Does it set the default date format property? Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Tue, Jun 8, 2010 at 1:46 PM, m...@ampersand.net.au m...@ampersand.net.auwrote: I've been doing some more reading - slow day today - apparently the date format used can also depend on the user logged in to the database - if you get properties of the login you are using to do the updates (in management studio) is it English or British English? -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Re: Odd date behaviour - CF9/SQLServer2005
In management studio it's security logins [right click for properties] and there's a default language setting for that login. I guess if you have some tables that work and this one that doesn't compare the collation between the two and these login settings and see if they differ? I found this that might help - http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/setting-a-standard-dateformat-for-sql-se On 8/06/2010 1:51 PM, Mike Kear wrote: @mgk: I'm doing the updates using ColdFusion. The only property I can find that's remotely associated with language or nationality or locale is the collation =SQL_Latin1_General_CP1_CI_AS Can this be set to something else? Does it set the default date format property? Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Tue, Jun 8, 2010 at 1:46 PM, m...@ampersand.net.au m...@ampersand.net.au wrote: I've been doing some more reading - slow day today - apparently the date format used can also depend on the user logged in to the database - if you get properties of the login you are using to do the updates (in management studio) is it English or British English? -- You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Re: Odd date behaviour - CF9/SQLServer2005
Hi, pretty sure that collation refers to sort order. Language refers to date format however, but I could not find a server wide setting to change it. SELECT @@LANGUAGE AS 'Language Name' returns us_english in my SQL 2005 server. If the language was British it would default to dmy select * from master.dbo.syslanguages to check out the dateformats available. I gave up on CF and SQL working well with European\Aussie dates long ago. I like using SET DATEFORMAT DMY in all of my procedures, and within CF DateFormat( x, dd-MMM-) but I agree that we should not have to. Mike Kear afpwebwo...@gmail.com 08/06/2010 1:51 pm @mgk: I'm doing the updates using ColdFusion. The only property I can find that's remotely associated with language or nationality or locale is the collation = SQL_Latin1_General_CP1_CI_AS Can this be set to something else? Does it set the default date format property? Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Tue, Jun 8, 2010 at 1:46 PM, m...@ampersand.net.au m...@ampersand.net.auwrote: I've been doing some more reading - slow day today - apparently the date format used can also depend on the user logged in to the database - if you get properties of the login you are using to do the updates (in management studio) is it English or British English? -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
[cfaussie] Re: Odd date behaviour - CF9/SQLServer2005
Hi All, On Jun 8, 2:06 pm, Scott Thornton scott.thorn...@hnehealth.nsw.gov.au wrote: Hi, pretty sure that collation refers to sort order. Language refers to date format however, but I could not find a server wide setting to change it. SELECT @@LANGUAGE AS 'Language Name' returns us_english in my SQL 2005 server. If the language was British it would default to dmy select * from master.dbo.syslanguages to check out the dateformats available. I gave up on CF and SQL working well with European\Aussie dates long ago. I like using SET DATEFORMAT DMY in all of my procedures, and within CF DateFormat( x, dd-MMM-) but I agree that we should not have to. Ahhh thats the one - thanks Scott! Looks loie we do the same thing... manually ensure that the dateformat is manually handled prior to insertion into the database - regardless of format chosen. Sorry for the misleading LOCALE setting Mike! Gavin. Mike Kear afpwebwo...@gmail.com 08/06/2010 1:51 pm @mgk: I'm doing the updates using ColdFusion. The only property I can find that's remotely associated with language or nationality or locale is the collation = SQL_Latin1_General_CP1_CI_AS Can this be set to something else? Does it set the default date format property? Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworkshttp://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Tue, Jun 8, 2010 at 1:46 PM, m...@ampersand.net.au m...@ampersand.net.auwrote: I've been doing some more reading - slow day today - apparently the date format used can also depend on the user logged in to the database - if you get properties of the login you are using to do the updates (in management studio) is it English or British English? -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Re: Odd date behaviour - CF9/SQLServer2005
Thanks everyone for your help with this. I dont have it solved yet but i'm on the right track now I think. I dont have this problem with any of the code I've written from scratch, but in this case I'm inheriting a lot of code written in CF5 years ago, and moved to a new server and database server. I have determined by manually altering the values in the date fields that if the dates are stored in -mm-dd format in the database, all the legacy code works as expected, so all I have to do is make sure any insert and update statements force the dates always to be inserted the right way around. It seems that if i add a Set language = 'british' to my insert and update queries, that should make sure of that. That's my theory and I'm testing it now. Once this episode is all done, I'll write more about how i manage Australian format dates when i write my own code, because I've had that under control for a long time. I only see this problem because of all the legacy code I've inherited. @Gavin, no need for the meaculpa. I had travelled along the locale road in the documentation for SQLServer and had found SET LANGUAGE in my travels. So you led me to this path anyway. Thanks Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Tue, Jun 8, 2010 at 2:15 PM, Gavin Baumanis beauecli...@gmail.comwrote: Hi All, On Jun 8, 2:06 pm, Scott Thornton scott.thorn...@hnehealth.nsw.gov.au wrote: Hi, pretty sure that collation refers to sort order. Language refers to date format however, but I could not find a server wide setting to change it. SELECT @@LANGUAGE AS 'Language Name' returns us_english in my SQL 2005 server. If the language was British it would default to dmy select * from master.dbo.syslanguages to check out the dateformats available. I gave up on CF and SQL working well with European\Aussie dates long ago. I like using SET DATEFORMAT DMY in all of my procedures, and within CF DateFormat( x, dd-MMM-) but I agree that we should not have to. Ahhh thats the one - thanks Scott! Looks loie we do the same thing... manually ensure that the dateformat is manually handled prior to insertion into the database - regardless of format chosen. Sorry for the misleading LOCALE setting Mike! Gavin. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
RE: [cfaussie] Odd date behaviour - CF9/SQLServer2005
Mike, I find it hard to believe it is SQL Server, this smacks of ColdFusion its the exact type of behaviour that ColdFusion does. Hard to believe that SQL Server would modify an ODBC date due to a localisation setting. I would still try to duplicate the problem with your own code, can you write code that will put it in the database wrong. createODBCDate(createDate(year, month day)) I but you can't make that put a wrong date in unless you get the month and day back to front Regards Dale Fraser http://dale.fraser.id.au http://cfmldocs.com http://cfmldocs.com/ http://learncf.com http://flexcf.com From: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] On Behalf Of Mike Kear Sent: Tuesday, 8 June 2010 12:47 PM To: cfaussie@googlegroups.com Subject: Re: [cfaussie] Odd date behaviour - CF9/SQLServer2005 @Dale, I would have said that too. My first assumption was that i had an error in my code somewhere. So I did a CFDUMP of the variables that I'm inserting into the table and they show the dates the way I want them. The coldfusion createdate function receives 3 numeric variables that are definitely year, month, day, so I'm pretty sure that's working ok (i.e. createdate(thisyear, thismonth, thisday) ) and if i CFDUMP the resulting date object and output it with dateformat, I see it how i want it - the month is correctly identified there. Then i assumed i must be allowing some ambiguity in the date in my insert statement so I used the ColdFusion debugging to show me the actual query used to insert the record, and it shows the dates correctly too. Specifically, it shows an insert of a date 2010-05-08 that appears subsequently in the database as 2010-08-05 I think your earlier assumption is quite likely - that it's something to do with localisation on the database, rather than the ColdFusion code. Is there a way to set localisation on a database as a kind of database setting rather than in each query? (this is SQLServer2005) Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Tue, Jun 8, 2010 at 12:36 PM, Dale Fraser d...@fraser.id.au wrote: I don't think it is, Write some test code, my money is that you can't duplicate it unless you create the date incorrectly in the first place. Regards Dale Fraser http://dale.fraser.id.au http://cfmldocs.com http://cfmldocs.com/ http://learncf.com http://flexcf.com -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Odd date behaviour - CF9/SQLServer2005
@Dale, I've just done a complete 180degree on that. Yes i think it is coldfusion that's doing it. I did a few more inserts and looked at the debugging. The variable that the insert is being given is a date that is definitely 10 May 2010. I can CFDUMP it using formatting that shows that it knows that the month is 05. Then when i look at the variables in the insert statement, when i use the CFQUERYPARAM type=cf_sql_date its inserting {ts '2010-10-05 00:00:00'} I get the same when i use #createodbcdate( )# instead of the CFQUERYPARAM tag. So in Application.cfc, in onApplicationStart() I put setLocale( en_AU ) and that made no difference. AAARRGH!!! I get the same result on both my dev machine here in my ofi and the remote production server. Does anyone have any other suggestions as to where i might look? Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Tue, Jun 8, 2010 at 3:00 PM, Dale Fraser d...@fraser.id.au wrote: Mike, I find it hard to believe it is SQL Server, this smacks of ColdFusion its the exact type of behaviour that ColdFusion does. Hard to believe that SQL Server would modify an ODBC date due to a localisation setting. I would still try to duplicate the problem with your own code, can you write code that will put it in the database wrong. createODBCDate(createDate(year, month day)) I but you can’t make that put a wrong date in unless you get the month and day back to front Regards Dale Fraser http://dale.fraser.id.au http://cfmldocs.com http://learncf.com http://flexcf.com * * -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Odd date behaviour - CF9/SQLServer2005
How about CF_SQL_TIMESTAMP instead? On 8/06/2010 3:16 PM, Mike Kear wrote: @Dale, I've just done a complete 180degree on that. Yes i think it is coldfusion that's doing it. I did a few more inserts and looked at the debugging. The variable that the insert is being given is a date that is definitely 10 May 2010. I can CFDUMP it using formatting that shows that it knows that the month is 05. Then when i look at the variables in the insert statement, when i use the CFQUERYPARAM type=cf_sql_date its inserting {ts '2010-10-05 00:00:00'} I get the same when i use #createodbcdate( )# instead of the CFQUERYPARAM tag. So in Application.cfc, in onApplicationStart() I put setLocale( en_AU ) and that made no difference. AAARRGH!!! I get the same result on both my dev machine here in my ofi and the remote production server. Does anyone have any other suggestions as to where i might look? Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Tue, Jun 8, 2010 at 3:00 PM, Dale Fraser d...@fraser.id.au wrote: Mike, I find it hard to believe it is SQL Server, this smacks of ColdFusion its the exact type of behaviour that ColdFusion does. Hard to believe that SQL Server would modify an ODBC date due to a localisation setting. I would still try to duplicate the problem with your own code, can you write code that will put it in the database wrong. createODBCDate(createDate(year, month day)) I but you can’t make that put a wrong date in unless you get the month and day back to front Regards Dale Fraser http://dale.fraser.id.au http://cfmldocs.com http://learncf.com http://flexcf.com -- You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaussie@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
RE: [cfaussie] Odd date behaviour - CF9/SQLServer2005
lsParseDateTime Regards Dale Fraser http://dale.fraser.id.au http://cfmldocs.com http://cfmldocs.com/ http://learncf.com http://flexcf.com From: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] On Behalf Of Mike Kear Sent: Tuesday, 8 June 2010 3:17 PM To: cfaussie@googlegroups.com Subject: Re: [cfaussie] Odd date behaviour - CF9/SQLServer2005 @Dale, I've just done a complete 180degree on that. Yes i think it is coldfusion that's doing it. I did a few more inserts and looked at the debugging. The variable that the insert is being given is a date that is definitely 10 May 2010. I can CFDUMP it using formatting that shows that it knows that the month is 05. Then when i look at the variables in the insert statement, when i use the CFQUERYPARAM type=cf_sql_date its inserting {ts '2010-10-05 00:00:00'} I get the same when i use #createodbcdate( )# instead of the CFQUERYPARAM tag. So in Application.cfc, in onApplicationStart() I put setLocale( en_AU ) and that made no difference. AAARRGH!!! I get the same result on both my dev machine here in my ofi and the remote production server. Does anyone have any other suggestions as to where i might look? Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Tue, Jun 8, 2010 at 3:00 PM, Dale Fraser d...@fraser.id.au wrote: Mike, I find it hard to believe it is SQL Server, this smacks of ColdFusion its the exact type of behaviour that ColdFusion does. Hard to believe that SQL Server would modify an ODBC date due to a localisation setting. I would still try to duplicate the problem with your own code, can you write code that will put it in the database wrong. createODBCDate(createDate(year, month day)) I but you can't make that put a wrong date in unless you get the month and day back to front Regards Dale Fraser http://dale.fraser.id.au http://cfmldocs.com http://cfmldocs.com/ http://learncf.com http://flexcf.com -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Odd date behaviour - CF9/SQLServer2005
@MGK: Thanks. Good suggestion, but sadly doesn't win the prize. That gives the same result. Cheers Mike Kear On Tue, Jun 8, 2010 at 3:23 PM, m...@ampersand.net.au m...@ampersand.net.auwrote: How about CF_SQL_TIMESTAMP instead? * * On 8/06/2010 3:16 PM, Mike Kear wrote: @Dale, I've just done a complete 180degree on that. Yes i think it is coldfusion that's doing it. I did a few more inserts and looked at the debugging. The variable that the insert is being given is a date that is definitely 10 May 2010. I can CFDUMP it using formatting that shows that it knows that the month is 05. Then when i look at the variables in the insert statement, when i use the CFQUERYPARAM type=cf_sql_date its inserting {ts '2010-10-05 00:00:00'} I get the same when i use #createodbcdate( )# instead of the CFQUERYPARAM tag. So in Application.cfc, in onApplicationStart() I put setLocale( en_AU ) and that made no difference. AAARRGH!!! I get the same result on both my dev machine here in my ofi and the remote production server. Does anyone have any other suggestions as to where i might look? Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Tue, Jun 8, 2010 at 3:00 PM, Dale Fraser d...@fraser.id.au wrote: Mike, I find it hard to believe it is SQL Server, this smacks of ColdFusion its the exact type of behaviour that ColdFusion does. Hard to believe that SQL Server would modify an ODBC date due to a localisation setting. I would still try to duplicate the problem with your own code, can you write code that will put it in the database wrong. createODBCDate(createDate(year, month day)) I but you can’t make that put a wrong date in unless you get the month and day back to front Regards Dale Fraser http://dale.fraser.id.au http://cfmldocs.com http://learncf.com http://flexcf.com * * -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.comcfaussie%2bunsubscr...@googlegroups.com . For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en. -- Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
Re: [cfaussie] Odd date behaviour - CF9/SQLServer2005
On 8/06/2010 15:16, Mike Kear wrote: @Dale, I've just done a complete 180degree on that. Yes i think it is coldfusion that's doing it. I did a few more inserts and looked at the debugging. Then when i look at the variables in the insert statement, when i use the CFQUERYPARAM type=cf_sql_date its inserting {ts '2010-10-05 00:00:00'} So you are saying that if you create a date object with create(Date) properly with defined year. month and day you are getting the wrong ts? oo, that's scary. -- Yours, Kym Kovan mbcomms.net.au -- You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.