Re: [cfaussie] Re: Odd date behaviour - CF9/SQLServer2005

[m...@ampersand.net.au]

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? On 8/06/2010 1:30 PM, m...@ampersand.net.au wrote: 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, "YYYY-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-yyyy - 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 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.