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 yyyy-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.com>wrote: > 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-yyyy") 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.
