Thanks Steve, but the problem is we're in Australia. We use European format dates. All the reports and queries are already written as
select * from someTable where mydate between '01-05-2010' and '31-05-2010'; But they dont select any May records unless i re-write the query using cast( ) etc. That is simply impractical - the client is not going to pay me to rewrite hundreds of queries, and anyway I'm not going to be able to do that inside the time available. And anyway, we already have historical data stored the way we want, from the previous server where this site came from. We brought it over to this server and now we have the problem. Maybe what I have to do is figure out how to change the collation. Do you think that would get at the issue? 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, May 12, 2010 at 11:34 PM, DURETTE, STEVEN J (ATTASIAIT) < [email protected]> wrote: > > Mike, > > SQL Server doesn't actually save the date in either format. It saves it > as seconds (milliseconds maybe) from a certain date (I believe > 1/1/1900). > > The way it is displaying is based on the collation setting of the > Database. Mine are set to SQL_Latin1_General_CP1_CI_AS so I get dates > back like this: "2010-05-12 08:25:15.670" Notice its Year-Month-Day. > You noted that the format of Year-Day-Month was American format, that is > incorrect. It is European format. US Format is Year Month Day and most > people in the US write their dates as Month/Day/Year. > > If you want a date in a specific format, you can return it as a string > in the format you want. Select cast(year(getDate()) as varchar) + '-' + > right('0' + cast(month(getDate()) as varchar), 2) + '-' + right('0' + > cast(day(getDate()) as varchar), 2) as properlyFormatedDate > > Either way cfqueryparam should be handling the conversions correctly. > If you aren't picking up the 31st, then I would guess that there is > another issue. > > If you did something like select * from someTable where mydate between > '05-01-2010' and '05-31-2010'; and it didn't return the record for the > 31st that you were expecting then my guess is that there is time data > for that record. > > In general when I'm doing something like that I do this: > Select * > From someTable > Where myDate >= '05-01-2010' > And myDate < dateAdd(d, -1, dateAdd(m, 1, '05-01-2010')); > > That way you get everything from 05-01-2010 00:00:00.000 to 05-31-2010 > 23:59:59.999. > > Steve > > > -----Original Message----- > From: Mike Kear [mailto:[email protected]] > Sent: Wednesday, May 12, 2010 9:20 AM > To: cf-talk > Subject: SQLServer2005 and european dates > > > I'm having trouble figuring out why my database is storing dates in a > way i > dont want. I hope someone can help. > > If I run an insert statement, and the dateentered = <cfqueryparam > value="#dateentered#" cfsqltype="CF_SQL_DATE" /> i would have thought > that > it would store the value '2010-03-31 00:00:00' but it doesnt. When > i > run a select query on the table the value has been stored as '2010-31-03 > 00:00:00' (month and day in American format) > > This means my reports dont find results for the last day of March and > all > other days are screwed up too. I can't change the reports for a whole > lot > of reasons, not the least of which is there are bazillions of them. > I'd > rather force the database to store the date in the format i want. > > Does anyone know now I can force SQLServer2005 to behave like a good > Australian server not an American one and store the dates how we use > them? > > > I have also tried using dateentered=#createodbcdate(dateentered)# and > that > gives the same result. > > -- > 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 > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333595 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
