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

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

What if you use  #dateformat(ECRTTotal.getdateentered(),"yyyymmdd")# (just out of interest really). Somewhere in either SQL Server or even in the OS itself you could try setting your location to the US and see if that helps? On 8/06/2010 9:30 AM, Blair McKenzie wrote: 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 yyyy-mm-dd is the default but yyyy-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 yyyy-dd-mm  but if the day is after the 12th of the month, it's stored as yyyy-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 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.