Re: [cfaussie] Re: Odd date behaviour - CF9/SQLServer2005
On Tue, Jun 8, 2010 at 9:15 PM, Mike Kear afpwebwo...@gmail.com wrote: This is generated by the Rooibos code written by P Farrell Just saw this so ignore my other post (about naming the guilty code generator). Thanx. -- Sean A Corfield -- (904) 302-SEAN Railo Technologies, Inc. -- http://getrailo.com/ An Architect's View -- http://corfield.org/ If you're not annoying somebody, you're not really alive. -- Margaret Atwood -- 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
Sean, I'm not sure characterising it as guilty is very fair. This generator has worked just fine for me without any problems in about a hundred applications. This is the first time it's given me any issues. It's been so reliable I didnt suspect it might be anything to do with this issue for a very long time. I'm sure you'd be annoyed if i posted that something you've written is guilty before I've even asked you about the issue. Perhaps it might be a bug you're aware of, perhaps you might have a reason it's the way it is that's not compatible with what i want to use it for. Or any of a number of reasons. That's why i think its only fair to talk to the author first before making characterisations about his code. 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 Wed, Jun 9, 2010 at 5:44 PM, Sean Corfield seancorfi...@gmail.comwrote: On Tue, Jun 8, 2010 at 9:15 PM, Mike Kear afpwebwo...@gmail.com wrote: This is generated by the Rooibos code written by P Farrell Just saw this so ignore my other post (about naming the guilty code generator). Thanx. -- Sean A Corfield -- (904) 302-SEAN Railo Technologies, Inc. -- http://getrailo.com/ An Architect's View -- http://corfield.org/ If you're not annoying somebody, you're not really alive. -- Margaret Atwood -- 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] Re: Odd date behaviour - CF9/SQLServer2005
Yes quite so, Sean. I'm just saying that it's worked a treat for me for a long time, and I didn't want there to be any sense of criticism of this code generator, or the two people who wrote it. When I look at the code behind the generator, I see that there's logic in the routine that writes getters and setters for date fields so that sometimes it writes the conversion/dateformatting bit and sometimes it doesn't. I guess that's where this issue arose and didnt in previous uses of the generator. Whether the issue is a bug or my use of the generator remains to be seen. That's all i mean by 'lets not label it as guilty just yet'. 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 Thu, Jun 10, 2010 at 3:13 AM, Sean Corfield seancorfi...@gmail.comwrote: Wow, don't jump to conclusions! I used guilty simply to mean the component that caused the bug you ran into. It's just code, it doesn't have any emotion. -- -- 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
IMO, Set Langauge seems a bit... obtuse? SET LANGUAGE DMY seems more appropriate, especially if you are giong to enter it into every sproc. cheers, Mike Kear afpwebwo...@gmail.com 08/06/2010 2:27 pm 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. -- 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
Believe it or not, I'm still worrying away at this date issue.I dont understand ColdFusion's insistence that we have to use American dates. (This is CF9 Enterprise by the way) I create a date object, using the following: createdate(2010,10,06) which is supposed to be year, month, day in that order.Then when i output the date in the next line it shows that date as 06/Oct/2010 It doesn't seem to make any difference whether i set a locale or not. It's nothing to do with dateformat, the problem here is that I'm creating a date using year-month-day and it's understanding it as year-day-month. HUH?? Here is the actual code: cfset testdate = createdate(2010,10,06) / cfoutput ptest date is #testdate# : #dateformat(testdate, dd/mmm/)# /p /cfoutput The result i get is this: test date is {ts '2010-10-06 00:00:00'} : 06/Oct/2010 Can someone else please run this code on their CF9 and see if they get the same result? That way i can know if this is being caused by something in the environment here. 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 4:44 PM, Scott Thornton scott.thorn...@hnehealth.nsw.gov.au wrote: IMO, Set Langauge seems a bit... obtuse? SET LANGUAGE DMY seems more appropriate, especially if you are giong to enter it into every sproc. cheers, -- 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
Oh jeezz. Ever had those moments when you wish you'd just taken a breath first??? Forget that.If i do it properly without the code error, i get the right result. hides his head in shame!@ 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 Wed, Jun 9, 2010 at 10:29 AM, Mike Kear afpwebwo...@gmail.com wrote: Believe it or not, I'm still worrying away at this date issue.I dont understand ColdFusion's insistence that we have to use American dates. (This is CF9 Enterprise by the way) I create a date object, using the following: createdate(2010,10,06) which is supposed to be year, month, day in that order.Then when i output the date in the next line it shows that date as 06/Oct/2010 It doesn't seem to make any difference whether i set a locale or not. It's nothing to do with dateformat, the problem here is that I'm creating a date using year-month-day and it's understanding it as year-day-month. HUH?? Here is the actual code: cfset testdate = createdate(2010,10,06) / cfoutput ptest date is #testdate# : #dateformat(testdate, dd/mmm/)# /p /cfoutput The result i get is this: test date is {ts '2010-10-06 00:00:00'} : 06/Oct/2010 Can someone else please run this code on their CF9 and see if they get the same result? That way i can know if this is being caused by something in the environment here. 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 4:44 PM, Scott Thornton scott.thorn...@hnehealth.nsw.gov.au wrote: IMO, Set Langauge seems a bit... obtuse? SET LANGUAGE DMY seems more appropriate, especially if you are giong to enter it into every sproc. cheers, -- -- 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
Its working createDate(year, month, day) 2010,10,06 is 6th of 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: Wednesday, 9 June 2010 10:30 AM To: cfaussie@googlegroups.com Subject: Re: [cfaussie] Re: Odd date behaviour - CF9/SQLServer2005 Believe it or not, I'm still worrying away at this date issue.I dont understand ColdFusion's insistence that we have to use American dates. (This is CF9 Enterprise by the way) I create a date object, using the following: createdate(2010,10,06) which is supposed to be year, month, day in that order.Then when i output the date in the next line it shows that date as 06/Oct/2010 It doesn't seem to make any difference whether i set a locale or not. It's nothing to do with dateformat, the problem here is that I'm creating a date using year-month-day and it's understanding it as year-day-month. HUH?? Here is the actual code: cfset testdate = createdate(2010,10,06) / cfoutput ptest date is #testdate# : #dateformat(testdate, dd/mmm/)# /p /cfoutput The result i get is this: test date is {ts '2010-10-06 00:00:00'} : 06/Oct/2010 Can someone else please run this code on their CF9 and see if they get the same result? That way i can know if this is being caused by something in the environment here. 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 4:44 PM, Scott Thornton scott.thorn...@hnehealth.nsw.gov.au wrote: IMO, Set Langauge seems a bit... obtuse? SET LANGUAGE DMY seems more appropriate, especially if you are giong to enter it into every sproc. cheers, -- 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] Re: Odd date behaviour - CF9/SQLServer2005
But thats correct isnt it? October is the 10th month _ From: Mike Kear [mailto:afpwebwo...@gmail.com] Sent: Wednesday, 9 June 2010 10:30 AM To: cfaussie@googlegroups.com Subject: Re: [cfaussie] Re: Odd date behaviour - CF9/SQLServer2005 Believe it or not, I'm still worrying away at this date issue.I dont understand ColdFusion's insistence that we have to use American dates. (This is CF9 Enterprise by the way) I create a date object, using the following: createdate(2010,10,06) which is supposed to be year, month, day in that order.Then when i output the date in the next line it shows that date as 06/Oct/2010 It doesn't seem to make any difference whether i set a locale or not. It's nothing to do with dateformat, the problem here is that I'm creating a date using year-month-day and it's understanding it as year-day-month. HUH?? Here is the actual code: cfset testdate = createdate(2010,10,06) / cfoutput ptest date is #testdate# : #dateformat(testdate, dd/mmm/)# /p /cfoutput The result i get is this: test date is {ts '2010-10-06 00:00:00'} : 06/Oct/2010 Can someone else please run this code on their CF9 and see if they get the same result? That way i can know if this is being caused by something in the environment here. 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 4:44 PM, Scott Thornton scott.thorn...@hnehealth.nsw.gov.au wrote: IMO, Set Langauge seems a bit... obtuse? SET LANGUAGE DMY seems more appropriate, especially if you are giong to enter it into every sproc. cheers, -- 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] Re: Odd date behaviour - CF9/SQLServer2005
His problem was that when he takes that date and inserts it into SQL it goes in as 2010-06-10, but if the month was past the 12th then it goes in incorrectly, i.e. if he does 2010-12-30 he gets December 30 (correct), if he goes 2010-04-07 he gets July 4 (wrong). Personally, I still reckon it's SQL Server doing something odd. Can you try another SQL login/username and see what happens then? or do you only have one login? On 9/06/2010 11:00 AM, Steve Onnis wrote: But thats correct isnt it? October is the 10th month From: Mike Kear [mailto:afpwebwo...@gmail.com] Sent: Wednesday, 9 June 2010 10:30 AM To: cfaussie@googlegroups.com Subject: Re: [cfaussie] Re: Odd date behaviour - CF9/SQLServer2005 Believe it or not, I'm still worrying away at this date issue. I dont understand ColdFusion's insistence that we have to use American dates. (This is CF9 Enterprise by the way) I create a date object, using the following: createdate("2010","10","06") which is supposed to be year, month, day in that order. Then when i output the date in the next line it shows that date as06/Oct/2010 It doesn't seem to make any difference whether i set a locale or not. It's nothing to do with dateformat, the problem here is that I'm creating a date using year-month-day and it's understanding it as year-day-month. HUH?? Here is the actual code: cfsettestdate = createdate("2010","10","06") / cfoutput ptest date is #testdate# : #dateformat(testdate, "dd/mmm/")# /p /cfoutput The result i get is this: test date is {ts '2010-10-06 00:00:00'} : 06/Oct/2010 Can someone else please run this code on their CF9 and see if they get the same result? That way i can know if this is being caused by something in the environment here. 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 4:44 PM, Scott Thornton scott.thorn...@hnehealth.nsw.gov.au wrote: IMO, Set Langauge seems a bit... obtuse? SET LANGUAGE DMY seems more appropriate, especially if you are giong to enter it into every sproc. cheers, -- 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 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
Well i had to suck that back again and wished i could have completely obliterated my previous email. I had a mistake in the code i posted on my example earlier.But i have just found the exact scenario happening. Inside my CFCs, i think American dates are always assumed. I am setting a date in my bean. Even if i specifically set it using the code i quoted in my last email ( i.e. PosBean.setTransDate( createdate(2010,06,10) ) for 10th June) if i immediately output the date I get a different result to what i get if i have it in a regular .cfm file. cfdump var=#lsdateformat(posbean.getTransDate(), 'dd/mmm/')# / cfdump var=#dateformat(posbean.getTransDate(), 'dd/mmm/')# / This code in a .cfm file outputs two showing 10 June. If i output from a .cfc it shows the first as 10 June, and the second as 6 October. So unless i'm mistaken, I get a different result in a .cfm page than i get if i output from a cfc. The trouble is, it's the .cfc that is causing the problem because its the cfc that creates the insert statement to the database. This is proving to be a particularly elusive problem. But since we use dates to select records for a whole host of reports, it's essential that i sort it out. 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 Wed, Jun 9, 2010 at 10:33 AM, Dale Fraser d...@fraser.id.au wrote: Its working createDate(year, month, day) 2010,10,06 is 6th of October. Regards Dale Fraser http://dale.fraser.id.au http://cfmldocs.com http://learncf.com http://flexcf.com *From:* cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] *On Behalf Of *Mike Kear *Sent:* Wednesday, 9 June 2010 10:30 AM *To:* cfaussie@googlegroups.com *Subject:* Re: [cfaussie] Re: Odd date behaviour - CF9/SQLServer2005 Believe it or not, I'm still worrying away at this date issue.I dont understand ColdFusion's insistence that we have to use American dates. (This is CF9 Enterprise by the way) I create a date object, using the following: createdate(2010,10,06) which is supposed to be year, month, day in that order.Then when i output the date in the next line it shows that date as 06/Oct/2010 It doesn't seem to make any difference whether i set a locale or not. It's nothing to do with dateformat, the problem here is that I'm creating a date using year-month-day and it's understanding it as year-day-month. HUH?? Here is the actual code: cfset testdate = createdate(2010,10,06) / cfoutput ptest date is #testdate# : #dateformat(testdate, dd/mmm/)# /p /cfoutput The result i get is this: *test date is {ts '2010-10-06 00:00:00'} : 06/Oct/2010* Can someone else please run this code on their CF9 and see if they get the same result? That way i can know if this is being caused by something in the environment here. 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 4:44 PM, Scott Thornton scott.thorn...@hnehealth.nsw.gov.au wrote: IMO, Set Langauge seems a bit... obtuse? SET LANGUAGE DMY seems more appropriate, especially if you are giong to enter it into every sproc. cheers, -- 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.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] Re: Odd date behaviour - CF9/SQLServer2005
do you have a return type set on the function of the cfc ? _ From: Mike Kear [mailto:afpwebwo...@gmail.com] Sent: Wednesday, 9 June 2010 11:13 AM To: cfaussie@googlegroups.com Subject: Re: [cfaussie] Re: Odd date behaviour - CF9/SQLServer2005 Well i had to suck that back again and wished i could have completely obliterated my previous email. I had a mistake in the code i posted on my example earlier.But i have just found the exact scenario happening. Inside my CFCs, i think American dates are always assumed. I am setting a date in my bean. Even if i specifically set it using the code i quoted in my last email ( i.e. PosBean.setTransDate( createdate(2010,06,10) ) for 10th June) if i immediately output the date I get a different result to what i get if i have it in a regular .cfm file. cfdump var=#lsdateformat(posbean.getTransDate(), 'dd/mmm/')# / cfdump var=#dateformat(posbean.getTransDate(), 'dd/mmm/')# / This code in a .cfm file outputs two showing 10 June. If i output from a .cfc it shows the first as 10 June, and the second as 6 October. So unless i'm mistaken, I get a different result in a .cfm page than i get if i output from a cfc. The trouble is, it's the .cfc that is causing the problem because its the cfc that creates the insert statement to the database. This is proving to be a particularly elusive problem. But since we use dates to select records for a whole host of reports, it's essential that i sort it out. 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 Wed, Jun 9, 2010 at 10:33 AM, Dale Fraser d...@fraser.id.au wrote: Its working createDate(year, month, day) 2010,10,06 is 6th of 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: Wednesday, 9 June 2010 10:30 AM To: cfaussie@googlegroups.com Subject: Re: [cfaussie] Re: Odd date behaviour - CF9/SQLServer2005 Believe it or not, I'm still worrying away at this date issue.I dont understand ColdFusion's insistence that we have to use American dates. (This is CF9 Enterprise by the way) I create a date object, using the following: createdate(2010,10,06) which is supposed to be year, month, day in that order.Then when i output the date in the next line it shows that date as 06/Oct/2010 It doesn't seem to make any difference whether i set a locale or not. It's nothing to do with dateformat, the problem here is that I'm creating a date using year-month-day and it's understanding it as year-day-month. HUH?? Here is the actual code: cfset testdate = createdate(2010,10,06) / cfoutput ptest date is #testdate# : #dateformat(testdate, dd/mmm/)# /p /cfoutput The result i get is this: test date is {ts '2010-10-06 00:00:00'} : 06/Oct/2010 Can someone else please run this code on their CF9 and see if they get the same result? That way i can know if this is being caused by something in the environment here. 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 4:44 PM, Scott Thornton scott.thorn...@hnehealth.nsw.gov.au wrote: IMO, Set Langauge seems a bit... obtuse? SET LANGUAGE DMY seems more appropriate, especially if you are giong to enter it into every sproc. cheers, -- 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 mailto:cfaussie%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 mailto:cfaussie%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
Re: [cfaussie] Re: Odd date behaviour - CF9/SQLServer2005
Is the code in the CFC doing anything obviously wrong (like formatting the date)? On 9/06/2010 11:38 AM, Gavin Baumanis wrote: Hi Mike I am NOT seeing your issue. Here is the code I am using; (I even changed the format slightly between the CFC and the CFM just to ensure it was dumping correctly.) dateformatter.cfm I am in the CFMbr / hr / cfset thedate = createdate("2010","06","10") cfdump var="#lsdateformat(thedate, 'dd/mmm/')#" /br / cfdump var="#dateformat(thedate, 'dd/mmm/')#" / br / hr / cfset myobj = createobject("component","/scribble/dateformatter") I am in the CFCbr / cfscript myobj.fn_datedumper(); /cfscript dateformatter.cfc cfcomponent output="false" cffunction name="fn_datedumper" cfset thedate = createdate("2010","06","10") cfdump var="#lsdateformat(thedate, 'dd/mm/')#" /br / cfdump var="#dateformat(thedate, 'dd/mm/')#" / br / /cffunction /cfcomponent and here is the output; I am in the CFM 10/Jun/2010 10/Jun/2010 I am in the CFC 10/06/2010 10/06/2010 As a best guess - I would be looking at the code used to insert / update date columns in the database. As I said previously in this thread, we dateformat(thedate, "-mm- dd") in all hand written SQL that inserts/updates to ensure it is persisted correctly. it was the ONLY way e could find to ensure valid dates everytime - and we spent a substantial amunt of time on trying to work it out, prior to going down that path. There may well be an "easier" option - and we ust donlt know about it... but at least we can (now) guarantee that all dates are correct in our database. Gavin. On Jun 9, 11:13am, Mike Kear afpwebwo...@gmail.com wrote: Well i had to suck that back again and wished i could have completely obliterated my previous email. I had a mistake in the code i posted on my example earlier. But i have just found the exact scenario happening. Inside my CFCs, i think American dates are always assumed. I am setting a date in my bean. Even if i specifically set it using the code i quoted in my last email ( i.e. PosBean.setTransDate( createdate("2010","06","10") ) for 10th June) if i immediately output the date I get a different result to what i get if i have it in a regular .cfm file. cfdump var="#lsdateformat(posbean.getTransDate(), 'dd/mmm/')#" / cfdump var="#dateformat(posbean.getTransDate(), 'dd/mmm/')#" / This code in a .cfm file outputs two showing 10 June. If i output from a .cfc it shows the first as 10 June, and the second as 6 October. So unless i'm mistaken, I get a different result in a .cfm page than i get if i output from a cfc. The trouble is, it's the .cfc that is causing the problem because its the cfc that creates the insert statement to the database. This is proving to be a particularly elusive problem. But since we use dates to select records for a whole host of reports, it's essential that i sort it out. 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 Wed, Jun 9, 2010 at 10:33 AM, Dale Fraser d...@fraser.id.au wrote: Its working createDate(year, month, day) 2010,10,06 is 6th of October. Regards Dale Fraser http://dale.fraser.id.au http://cfmldocs.com http://learncf.com http://flexcf.com *From:* cfaussie@googlegroups.com [mailto:cfaussie@googlegroups.com] *On Behalf Of *Mike Kear *Sent:* Wednesday, 9 June 2010 10:30 AM *To:* cfaussie@googlegroups.com *Subject:* Re: [cfaussie] Re: Odd date behaviour - CF9/SQLServer2005 Believe it or not, I'm still worrying away at this date issue. I dont understand ColdFusion's insistence that we have to use American dates. (This is CF9 Enterprise by the way) I create a date object, using the following: createdate("2010","10","06") which is supposed to be year, month, day in that order. Then when i output the date in the next line it shows that date as 06/Oct/2010 It doesn't seem to make any difference whether i set a locale or not. It's nothing to do with dateformat, the problem here is that I'm creating a date using year-month-day and it's understanding it as year-day-month. HUH?? Here is the actual code:
Re: [cfaussie] Re: Odd date behaviour - CF9/SQLServer2005
I have found i can specifically set a date using numerical values rather than variables, to make absolutely certain i know what the value of the date being inserted is. If i set a variable which is a date object using the createdate() function, it reads the date properly. If I use that same function to set a setter in a bean, it doesnt. Here's the specifics: cfset testdate = createdate(2010,06,10) pDay: #day(testdate)#br / Month:#month(testdate)#br / Year: #year(testdate)#/p This code shows year='2010', month='6', day='10' But if i have a bean with a date value in it, and set the date value like this: cfset PosBean.setTransDate( createdate(2010,06,10) ) / pDay: #day( posbean.getTransDate() )#br / Month:#month( posbean.getTransDate() )#br / Year: #year( posbean.getTransDate() )#/p This code shows year='2010', month=10', day='6' If i use the bean value in the insert statement, it inserts the date with month=10 and date=6. If i use the #testdate# value in the insert statement, it inserts the date with month=6 and date=10. The conclusion i am coming to is that a cfc will behave differently to a cfm file. No?? Anyway, since the value i'm trying to insert into the database is a value from the bean (along with all the other values of the bean) I'm always going to get the wrong value into the database aren't i? -- 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] Re: Odd date behaviour - CF9/SQLServer2005
What do the methods setTransDate() and getTransDate() look like? Is there a declared property behind them? If so, what does that look like? On Tue, Jun 8, 2010 at 9:02 PM, Mike Kear afpwebwo...@gmail.com wrote: I have found i can specifically set a date using numerical values rather than variables, to make absolutely certain i know what the value of the date being inserted is. If i set a variable which is a date object using the createdate() function, it reads the date properly. If I use that same function to set a setter in a bean, it doesnt. Here's the specifics: cfset testdate = createdate(2010,06,10) pDay: #day(testdate)#br / Month:#month(testdate)#br / Year: #year(testdate)#/p This code shows year='2010', month='6', day='10' But if i have a bean with a date value in it, and set the date value like this: cfset PosBean.setTransDate( createdate(2010,06,10) ) / pDay: #day( posbean.getTransDate() )#br / Month:#month( posbean.getTransDate() )#br / Year: #year( posbean.getTransDate() )#/p This code shows year='2010', month=10', day='6' If i use the bean value in the insert statement, it inserts the date with month=10 and date=6. If i use the #testdate# value in the insert statement, it inserts the date with month=6 and date=10. The conclusion i am coming to is that a cfc will behave differently to a cfm file. No?? Anyway, since the value i'm trying to insert into the database is a value from the bean (along with all the other values of the bean) I'm always going to get the wrong value into the database aren't i? -- 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
This is the code for the getter and setter involved: cffunction name=setTransDate access=public returntype=void output=false cfargument name=TransDate type=string required=true / cfif isDate(arguments.TransDate) cfset arguments.TransDate = dateformat(arguments.TransDate,DD/MM/) / /cfif cfset variables.instance.TransDate = arguments.TransDate / /cffunction cffunction name=getTransDate access=public returntype=string output=false cfreturn variables.instance.TransDate / /cffunction This is generated by the Rooibos code written by P Farrell On Wed, Jun 9, 2010 at 2:07 PM, Sean Corfield seancorfi...@gmail.comwrote: What do the methods setTransDate() and getTransDate() look like? Is there a declared property behind them? If so, what does that look like? 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] Re: Odd date behaviour - CF9/SQLServer2005
On 9/06/2010 14:02, Mike Kear wrote: If i set a variable which is a date object using the createdate() function, it reads the date properly. If I use that same function to set a setter in a bean, it doesnt. Here's the specifics: cfset testdate = createdate(2010,06,10) pDay: #day(testdate)#br / Month:#month(testdate)#br / Year: #year(testdate)#/p This code shows year='2010', month='6', day='10' But if i have a bean with a date value in it, and set the date value like this: cfset PosBean.setTransDate( createdate(2010,06,10) ) / pDay: #day( posbean.getTransDate() )#br / Month:#month( posbean.getTransDate() )#br / Year: #year( posbean.getTransDate() )#/p This code shows year='2010', month=10', day='6' So the next bit to check is the typing of the attributes going onto the CFC/bean and return type, etc... -- 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.
RE: [cfaussie] Re: Odd date behaviour - CF9/SQLServer2005
Return type String could be the problem 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: Wednesday, 9 June 2010 2:15 PM To: cfaussie@googlegroups.com Subject: Re: [cfaussie] Re: Odd date behaviour - CF9/SQLServer2005 This is the code for the getter and setter involved: cffunction name=setTransDate access=public returntype=void output=false cfargument name=TransDate type=string required=true / cfif isDate(arguments.TransDate) cfset arguments.TransDate = dateformat(arguments.TransDate,DD/MM/) / /cfif cfset variables.instance.TransDate = arguments.TransDate / /cffunction cffunction name=getTransDate access=public returntype=string output=false cfreturn variables.instance.TransDate / /cffunction This is generated by the Rooibos code written by P Farrell On Wed, Jun 9, 2010 at 2:07 PM, Sean Corfield seancorfi...@gmail.com wrote: What do the methods setTransDate() and getTransDate() look like? Is there a declared property behind them? If so, what does that look like? 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] Re: Odd date behaviour - CF9/SQLServer2005
Plus your doing a dateformat on it, its not needed, keep it as a date object in the setter 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: Wednesday, 9 June 2010 2:15 PM To: cfaussie@googlegroups.com Subject: Re: [cfaussie] Re: Odd date behaviour - CF9/SQLServer2005 This is the code for the getter and setter involved: cffunction name=setTransDate access=public returntype=void output=false cfargument name=TransDate type=string required=true / cfif isDate(arguments.TransDate) cfset arguments.TransDate = dateformat(arguments.TransDate,DD/MM/) / /cfif cfset variables.instance.TransDate = arguments.TransDate / /cffunction cffunction name=getTransDate access=public returntype=string output=false cfreturn variables.instance.TransDate / /cffunction This is generated by the Rooibos code written by P Farrell On Wed, Jun 9, 2010 at 2:07 PM, Sean Corfield seancorfi...@gmail.com wrote: What do the methods setTransDate() and getTransDate() look like? Is there a declared property behind them? If so, what does that look like? 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] Re: Odd date behaviour - CF9/SQLServer2005
I would have written it this way cffunction name=setTransDate access=public returntype=void output=false cfargument name=TransDate type=date required=true / cfset variables.instance.TransDate = arguments.TransDate / /cffunction cffunction name=getTransDate access=public returntype=date output=false cfreturn variables.instance.TransDate / /cffunction That way you get the same date object as you passed in and let the cfargument validate the value being passed in. Simple and straight forward Steve _ From: Mike Kear [mailto:afpwebwo...@gmail.com] Sent: Wednesday, 9 June 2010 2:15 PM To: cfaussie@googlegroups.com Subject: Re: [cfaussie] Re: Odd date behaviour - CF9/SQLServer2005 This is the code for the getter and setter involved: cffunction name=setTransDate access=public returntype=void output=false cfargument name=TransDate type=string required=true / cfif isDate(arguments.TransDate) cfset arguments.TransDate = dateformat(arguments.TransDate,DD/MM/) / /cfif cfset variables.instance.TransDate = arguments.TransDate / /cffunction cffunction name=getTransDate access=public returntype=string output=false cfreturn variables.instance.TransDate / /cffunction This is generated by the Rooibos code written by P Farrell On Wed, Jun 9, 2010 at 2:07 PM, Sean Corfield seancorfi...@gmail.com wrote: What do the methods setTransDate() and getTransDate() look like? Is there a declared property behind them? If so, what does that look like? 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] Re: Odd date behaviour - CF9/SQLServer2005
If I remove the date formatting stuff from the setter, it seems to work ok. But I've been using the Rooibos bean generator without problems for ages. I have never had this problem with dates before.So i'm not sure what's so different about this app. Anyway, if i do this with the setter: cffunction name=setTransDate access=public returntype=void output=false cfargument name=TransDate type=date required=true / cfset variables.instance.TransDate = arguments.TransDate / /cffunction then it seems to return a date object that the CFQUERYPARAM seems to like and inserts the right way round. I guess that's the workaround but i'm a bit nervous about deploying an application where it behaves differently to other applications that work quite successfully. 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 Wed, Jun 9, 2010 at 2:15 PM, Kym Kovan dev-li...@mbcomms.net.au wrote: On 9/06/2010 14:02, Mike Kear wrote: If i set a variable which is a date object using the createdate() function, it reads the date properly. If I use that same function to set a setter in a bean, it doesnt. Here's the specifics: cfset testdate = createdate(2010,06,10) pDay: #day(testdate)#br / Month:#month(testdate)#br / Year: #year(testdate)#/p This code shows year='2010', month='6', day='10' But if i have a bean with a date value in it, and set the date value like this: cfset PosBean.setTransDate( createdate(2010,06,10) ) / pDay: #day( posbean.getTransDate() )#br / Month:#month( posbean.getTransDate() )#br / Year: #year( posbean.getTransDate() )#/p This code shows year='2010', month=10', day='6' So the next bit to check is the typing of the attributes going onto the CFC/bean and return type, etc... -- 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.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] Re: Odd date behaviour - CF9/SQLServer2005
Mike, This generated code looks like it is causing you problems. As the bean converts the date to a string for storage, when it retrieves it back CF does a String Date conversion which is something i've always seen problems with. A quick hack would be to alter the cfreturn from cfreturn variables.instance.TransDate / to something like cfreturn CreateDate(ListLast(variables.instance.TransDate, /), ListGetAt(variables.instance.TransDate, 2, /), ListFirst(variables.instance.TransDate, /)) To ensure there is no ambiguity. If that works ok, then you could look for a neater solution. Phil On 9/06/10 2:15 PM, Mike Kear wrote: This is the code for the getter and setter involved: cffunction name=setTransDate access=public returntype=void output=false cfargument name=TransDate type=string required=true / cfif isDate(arguments.TransDate) cfset arguments.TransDate = dateformat(arguments.TransDate,DD/MM/) / /cfif cfset variables.instance.TransDate = arguments.TransDate / /cffunction cffunction name=getTransDate access=public returntype=string output=false cfreturn variables.instance.TransDate / /cffunction This is generated by the Rooibos code written by P Farrell On Wed, Jun 9, 2010 at 2:07 PM, Sean Corfield seancorfi...@gmail.com mailto:seancorfi...@gmail.com wrote: What do the methods setTransDate() and getTransDate() look like? Is there a declared property behind them? If so, what does that look like? Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET http://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] Re: Odd date behaviour - CF9/SQLServer2005
the thing is the function is messing around with the date object when it doesnt need to be. _ From: Mike Kear [mailto:afpwebwo...@gmail.com] Sent: Wednesday, 9 June 2010 2:26 PM To: cfaussie@googlegroups.com Subject: Re: [cfaussie] Re: Odd date behaviour - CF9/SQLServer2005 If I remove the date formatting stuff from the setter, it seems to work ok. But I've been using the Rooibos bean generator without problems for ages. I have never had this problem with dates before.So i'm not sure what's so different about this app. Anyway, if i do this with the setter: cffunction name=setTransDate access=public returntype=void output=false cfargument name=TransDate type=date required=true / cfset variables.instance.TransDate = arguments.TransDate / /cffunction then it seems to return a date object that the CFQUERYPARAM seems to like and inserts the right way round. I guess that's the workaround but i'm a bit nervous about deploying an application where it behaves differently to other applications that work quite successfully. 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 Wed, Jun 9, 2010 at 2:15 PM, Kym Kovan dev-li...@mbcomms.net.au wrote: On 9/06/2010 14:02, Mike Kear wrote: If i set a variable which is a date object using the createdate() function, it reads the date properly. If I use that same function to set a setter in a bean, it doesnt. Here's the specifics: cfset testdate = createdate(2010,06,10) pDay: #day(testdate)#br / Month:#month(testdate)#br / Year: #year(testdate)#/p This code shows year='2010', month='6', day='10' But if i have a bean with a date value in it, and set the date value like this: cfset PosBean.setTransDate( createdate(2010,06,10) ) / pDay: #day( posbean.getTransDate() )#br / Month:#month( posbean.getTransDate() )#br / Year: #year( posbean.getTransDate() )#/p This code shows year='2010', month=10', day='6' So the next bit to check is the typing of the attributes going onto the CFC/bean and return type, etc... -- 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 mailto:cfaussie%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. -- 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
Somewhere on that server setting the CF locale properly will fix the problem. If its an existing legacy app as you mentioned, something in the app might be playing with the locale. At least its your code and not SQL so you can fix it. 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: Wednesday, 9 June 2010 2:26 PM To: cfaussie@googlegroups.com Subject: Re: [cfaussie] Re: Odd date behaviour - CF9/SQLServer2005 If I remove the date formatting stuff from the setter, it seems to work ok. But I've been using the Rooibos bean generator without problems for ages. I have never had this problem with dates before.So i'm not sure what's so different about this app. Anyway, if i do this with the setter: cffunction name=setTransDate access=public returntype=void output=false cfargument name=TransDate type=date required=true / cfset variables.instance.TransDate = arguments.TransDate / /cffunction then it seems to return a date object that the CFQUERYPARAM seems to like and inserts the right way round. I guess that's the workaround but i'm a bit nervous about deploying an application where it behaves differently to other applications that work quite successfully. 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 Wed, Jun 9, 2010 at 2:15 PM, Kym Kovan dev-li...@mbcomms.net.au wrote: On 9/06/2010 14:02, Mike Kear wrote: If i set a variable which is a date object using the createdate() function, it reads the date properly. If I use that same function to set a setter in a bean, it doesnt. Here's the specifics: cfset testdate = createdate(2010,06,10) pDay: #day(testdate)#br / Month:#month(testdate)#br / Year: #year(testdate)#/p This code shows year='2010', month='6', day='10' But if i have a bean with a date value in it, and set the date value like this: cfset PosBean.setTransDate( createdate(2010,06,10) ) / pDay: #day( posbean.getTransDate() )#br / Month:#month( posbean.getTransDate() )#br / Year: #year( posbean.getTransDate() )#/p This code shows year='2010', month=10', day='6' So the next bit to check is the typing of the attributes going onto the CFC/bean and return type, etc... -- 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 mailto:cfaussie%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. -- 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.
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.