Changing SQL Server Date format
omar koudsi
When I first installed the SQL server on my server, the default date
format was mm/dd/yy. Now I need to change that to dd/mm/yy in
regional settings, but its still not reflected in the DB's in SQL (its
still mm/dd/yy).
Is it possible to change it in SQL without a complete reinstallation
of the server?
BP Margolin
SQL Server does not have a default date format. SQL Server stores dates as
numeric offsets from a base date. The formatting of dates is controlled by
multiple factors, among them the default language specified for SQL Server
and regional settings on the client machines.
Bottom line, no application should depend upon any SQL Server "default date
format" because none exists. If you need dates to be returned in a
consistent format, then look at the CONVERT system function, and explicitly
format them before returning them to the client application. Even better,
just return dates as datetime data types, and format them explicitly in the
client application. For scalability reasons, it is better to have clients do
formatting work than SQL Server.
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"omar koudsi" <om...@jeeran.com> wrote in message
news:27eadc6c.02071...@posting.google.com...
omar koudsi
DB to dd/mm/yy and try to insert a date() into a column of a datetime
format , i get :
Syntax error converting character string to smalldatetime data type
because SQL thinks that dd/mm/yy is a string and not a date, because its
set to use mm/dd/yy.
I know I could use the convert system function, but it would be a very
big hassle if I had to change all of my sql statements in my
not-so-small app.
I have changed the default language of SQL server to "Arabic" , which
should use the dd/mm/yy format. But I still get the same error in my
application.
My SQL server is somewhere set that the proper date format is mm/dd/yy
and if you enter anything else it thinks its a string. I want that to
change, I want the standard to be dd/mm/yy , and I have a feeling its
something should be done on the server itself or in my connection
string, not in my SQL statements.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
BP Margolin
You have just outlined exactly why applications should not depend upon date
formats. SQL Server will always interpret dates entered as YYYYMMDD
correctly regardless of any other settings.
> I know I could use the convert system function, but it would be a very
> big hassle if I had to change all of my sql statements in my
> not-so-small app.
I don't mean to sound unsympathetic, but just a few years ago billions of
dollars were spent on fixing the Y2K mess because people opted for "quick
and easy" solutions rather than "correct" solutions. Sure you can play
around with settings until you find the "magic" ones, but then when you
install the next version of SQL Server, you are likely to have the same
problem. Better, IMHO, to fix it once and have it stay fixed.
>I want the standard to be dd/mm/yy , and I have a feeling its
> something should be done on the server itself or in my connection
> string, not in my SQL statements.
I respectfully disagree.
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"omar koudsi" <om...@jeeran.com> wrote in message
news:3d3861ef$0$58364$7586...@news.frii.net...
BP Margolin
You have just outlined exactly why applications should not depend upon date
formats. SQL Server will always interpret dates entered as YYYYMMDD
correctly regardless of any other settings.
> I know I could use the convert system function, but it would be a very
> big hassle if I had to change all of my sql statements in my
> not-so-small app.
I don't mean to sound unsympathetic, but just a few years ago billions of
dollars were spent on fixing the Y2K mess because people opted for "quick
and easy" solutions rather than "correct" solutions. Sure you can play
around with settings until you find the "magic" ones, but then when you
install the next version of SQL Server, you are likely to have the same
problem. Better, IMHO, to fix it once and have it stay fixed.
>I want the standard to be dd/mm/yy , and I have a feeling its
> something should be done on the server itself or in my connection
> string, not in my SQL statements.
I respectfully disagree.
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"omar koudsi" <om...@jeeran.com> wrote in message
news:3d3861ef$0$58364$7586...@news.frii.net...
omar koudsi
I really appreciate you taking the time to reply, so I guess ill take
your word for it and accept the fact that the standard for SQL is
YYYYMMDD, and it simply can not change.
regards,
Erland Sommarskog
> Well, here is the problem. After I change the regional settings in my
> DB to dd/mm/yy and try to insert a date() into a column of a datetime
> format , i get :
The regional settings for Windows do *not* affect SQL Server.
What affects SQL Server are language setting (SET LANGUAGE or language
per user) and SET DATEFORMAT.
> My SQL server is somewhere set that the proper date format is mm/dd/yy
> and if you enter anything else it thinks its a string. I want that to
> change, I want the standard to be dd/mm/yy , and I have a feeling its
> something should be done on the server itself or in my connection
> string, not in my SQL statements.
You are right in the last phrase: not in your SQL statements, but you are
wrong in the previous: it should *not* be done on the server, it should
be done on the client.
A properly written client never passes dates as strings to SQL Server, it
passes dates as parameters in prepared queries or RPC calls to stored
procedure. This way, SQL Server gets the date in its internal format,
and one user can use Arabic format, and another Swedish format.
If you occasionally need to specify a date in a stored procedure, use
YYYYMMDD, as BP said. You should use this format, because this format
will always work, and not be dependent on certain settings.
--
Erland Sommarskog, SQL Server MVP
som...@algonet.se
Books Online (updated!) for SQL 2000 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
BP Margolin
> so I guess ill take your word for it
Never take anyone's word for anything :-) There is a lot of nonsense that
gets posted to newsgroups ... and, unfortunately, I've posted my fair share
of nonsense also. So while I believe I provided sound advice, unless you
agree with it, don't accept it blindly.
> the standard for SQL is YYYYMMDD
To be precise, the standard for Microsoft SQL Server is the YYYYMMDD format.
The ANSI SQL standard, as I understand it, is actually dates in YYYY-MM-DD
format, which in most, but not all, cases Microsoft SQL Server will also
understand correctly. So there appears to be a bit of a disconnect between
Microsoft SQL Server and the ANSI SQL standard on the "best" format for
dates, but then again SQL Server, while frequently following the ANSI SQL
standard, does sometimes go off on its own path :-)
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"omar koudsi" <om...@jeeran.com> wrote in message
news:3d3871df$0$58368$7586...@news.frii.net...