Ok, so I made a mistake in my code...
Here is the correction:
Select *
>From someTable
Where myDate >= '05-01-2010'
And myDate < dateAdd(m, 1, '05-01-2010');
That code gets everything greater than or equal to 2010-05-01
00:00:00.000 and less than 2010-06-01 00:00:00.000
Steve
-----Original Message-----
From: DURETTE, STEVEN J (ATTASIAIT)
Sent: Wednesday, May 12, 2010 9:35 AM
To: cf-talk
Subject: RE: SQLServer2005 and European dates
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:afpwebwo...@gmail.com]
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:333596
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
