Re: Regional settings weird problem

The regional settings on your computer influence how the date is presented
in Enterprise Manager. So if the regional settings are British Enterprise
Manager will _display_ the dates in dd/mm/yyyy. Query Analyzer isn't
influenced by the regional settings, and will always display dates in
yyyy-mm-dd hh:mm:ss, which is known as the ODBC canonical dateformat.

However, how SQL Server interprets the dateformat of strings depends on the
settings for your login in SQL Server. In Enterprise Manager, look under
<Server>\Security\Logins and look at the Default Language for your username
(or BUILTIN\Administrators if you are a Windows administrator on your local
machine). In your case this will be English instead of British.

To avoid problems with dates as strings, use the formats that are always
interpreted the same by SQL Server independent of any settings:
yyyymmdd and
yyyy-mm-ddThh:mm:ss

--
Jacco Schalkwijk
SQL Server MVP


"Northgate" <Northgate@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:98A3F027-FC72-4F48-A42E-B9B9F6C7D67A@xxxxxxxxxxxxxxxx
> Ok, I think I understand but I'm still having problems on my W2KPC and SQL
> Server 2000.
>
> As far as I can tell everything on my PC is set to British English - PC
> regional settings, SQL Server Logins, registry settings,etc, etc. I've
> also
> changed the MSSQLSERVER service to run under my personal login.
>
> When I do a Look At Table -> return all rows on a table in Enterprise
> manager dates as displayed in British format, e.g., dd/mm/yyyy hh:mm:ss.
> When
> I use Query Analyser (also logged in with my personal login) dates are
> displayed as yy-mm-dd hh:mm:ss. Why ??
>
> It gets worse ...
>
> If I click the SQL button when viewing the table in Enterprise manager and
> use the SQL pane to do a select such as :
>
> SELECT *
> FROM tablename
> WHERE (dateField BETWEEN '06/04/2004' AND '07/04/2004')
>
> This is interpreted to be in mm/dd/yy format rather the British dd/mm/yy
> format that the data is displayed in, groan!!! Why??
>
> Any help greatly appreciated, otherwise I'm going to change all my date
> fields to varchars!


.

Loading