Help! SQLExpress2005 date formats

I installed SQLExpress2005 and restored some old MSSQL7 databases. This
seemed to work at first glance, but there is at least one problem. These
databases contained datetime values.
And we are in Germany where dates are written like this: 25.12.2005
(christmas, for example).
My ColdFusion application contains queries like this:
SELECT Datum, Kurztext, Feiertag, Brauchtumstag, Tagestyp, Freianteil,
Notiz
FROM tblBetriebskalender
WHEREdatum between '01.12.2005' and '31.12.2005'
ORDER BYDatum
this used to work without problems.
Now the same queries don't work because for a date such as '31.12.2005',
SQLServer thinks 12 is the day and 31 is the month.
What can I do? Is there a central switch somewhere that changes the data
formats? Unfortunately I don't know what it was set to in the old system.
(The database properties option tab shows that collation is now set to
SQL_Latin1_General_CP1_CI_AS, and the compatibility level is SQLServer 7.0.)
-Michael
hi Michael,
Michael Peters wrote:
> I installed SQLExpress2005 and restored some old MSSQL7 databases.
> This seemed to work at first glance, but there is at least one
> problem. These databases contained datetime values.
> And we are in Germany where dates are written like this: 25.12.2005
> (christmas, for example).
> My ColdFusion application contains queries like this:
> SELECT Datum, Kurztext, Feiertag, Brauchtumstag, Tagestyp, Freianteil,
> Notiz
> FROM tblBetriebskalender
> WHERE datum between '01.12.2005' and '31.12.2005'
> ORDER BY Datum
> this used to work without problems.
> Now the same queries don't work because for a date such as
> '31.12.2005', SQLServer thinks 12 is the day and 31 is the month.
> What can I do? Is there a central switch somewhere that changes the
> data formats? Unfortunately I don't know what it was set to in the
> old system.
> (The database properties option tab shows that collation is now set to
> SQL_Latin1_General_CP1_CI_AS, and the compatibility level is
> SQLServer 7.0.)
> -Michael
Dates should be handled in ISO format YYYY-MM-DD or YYYYMMDD in order to
avoid this kind of problem as the dateformat is language specific and each
login has na associated language default.. probably you had german setting
for your login on the old server and englis setting on the new one...
you can change the login's language
ALTER LOGIN login_name
DEFAULT_LANGUAGE = new_language
http://msdn2.microsoft.com/en-us/library/ms189828.aspx
or, at connection level, using the SET DATEFORMAT
http://msdn.microsoft.com/library/de...-set_052s.asp,
but I strongly urge you to modify your language management to standard
ISO/ODBC format..
an unvaluable article by Notre Dame SQL Server Kalen Delaney about "dates"
is available at
http://www.windowsitpro.com/SQLServe...147/9147.html, but
only for subscriber...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||thanks very much Andrea.
-Michael