Changing the default date format for a database

Hi everyone,
I'm a british developer and so my SQL Server is on a british computer. The
problem I'm having is that dates are being stored in the British format and
I desperately need them to be stored in the American format. The applications
I'm making are all for the American market and having the dates stored in
the db as british dates is causing all sorts of problems. The two main problems
is that my application sends american formatted dates to the application
(which the db freaks out at) and the database tries to send back british
dates (which my application freaks out at).
Can anyone tell me, is there a way to have the DB store the dates in the
American format? I was hoping that this could be set on a per database basis
because I do have a couple of databases that are for British clients.
If anyone can help I would be very very grateful...!
Thanks
Simon
Simon,
Contrary to what you may believe, SQL does not store dates with any
formatting.
This is taken from books online:
Values with the datetime data type are stored internally by Microsoft SQL
Server as two 4-byte integers.
The formatting you see is done by the client application.
When you insert a date into SQL, use a date that SQL can understand.
The norm is yyyymmdd, so today would be '20060215'.
When presenting dates, I would suggest formatting the date client side.
You can personalize formatting to the client's taste.
I support English and French clients here, so I format dates depending on
the client "local".
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.c om...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
> problem I'm having is that dates are being stored in the British format
> and I desperately need them to be stored in the American format. The
> applications I'm making are all for the American market and having the
> dates stored in the db as british dates is causing all sorts of problems.
> The two main problems is that my application sends american formatted
> dates to the application (which the db freaks out at) and the database
> tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the
> American format? I was hoping that this could be set on a per database
> basis because I do have a couple of databases that are for British
> clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>
|||Hi Simon
If the dates are stored using the datetime datatype, they are not stored in
either the British or American format. They are stored in an internal,
unambiguous format that you never see, and are presented in whatever format
the client application requests.
Can you be specific about why you think they are being stored in a British
format?
You might want to take a look at the following topics in Books Online, and
then come back with more questions:
datetime datatype
Convert (use of a third parameter forces dates to be DISPLAYED in a chosen
format, it does not change how they are stored)
SET DATFORMAT (you can determine how you want strings to be interpreted as
dates, but again, it does not change how they are stored)
SET LANGUAGE (will set a default DATEFORMAT value as well as certain other
language options)
You might also search the SQL Server Magazine website for some articles I
wrote several years ago on dealing with datetime data in SQL Server.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.c om...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The
> problem I'm having is that dates are being stored in the British format
> and I desperately need them to be stored in the American format. The
> applications I'm making are all for the American market and having the
> dates stored in the db as british dates is causing all sorts of problems.
> The two main problems is that my application sends american formatted
> dates to the application (which the db freaks out at) and the database
> tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the
> American format? I was hoping that this could be set on a per database
> basis because I do have a couple of databases that are for British
> clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>
>
|||A longer elaboration, in addition to the other replies:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b1eb258c800620757197b@.news.microsoft.c om...
> Hi everyone,
> I'm a british developer and so my SQL Server is on a british computer. The problem I'm having is
> that dates are being stored in the British format and I desperately need them to be stored in the
> American format. The applications I'm making are all for the American market and having the dates
> stored in the db as british dates is causing all sorts of problems. The two main problems is that
> my application sends american formatted dates to the application (which the db freaks out at) and
> the database tries to send back british dates (which my application freaks out at).
> Can anyone tell me, is there a way to have the DB store the dates in the American format? I was
> hoping that this could be set on a per database basis because I do have a couple of databases that
> are for British clients.
> If anyone can help I would be very very grateful...!
> Thanks
> Simon
>
|||Hi Simon,
I think the following commands work.You can get a variety of US date
'style's by changging the 3rd parameter.
SELECT CONVERT(char(11), getdate(),0)
SELECT CONVERT(char(10), getdate(),10)
SELECT CONVERT(char(10), getdate(),1)
SELECT CONVERT(char(10), getdate(),2)
BYE
|||Hi Simon,
I think the following commands work.You can get a variety of US date
'style's by changging the 3rd parameter.
SELECT CONVERT(char(11), getdate(),0)
SELECT CONVERT(char(10), getdate(),10)
SELECT CONVERT(char(10), getdate(),1)
SELECT CONVERT(char(10), getdate(),2)
BYE
|||Hi everyone,
I'm looking through the articles you suggested. Very interesting stuff.
Thank you all very much for your time - you've all been a big big help!
Thanks again
Simon