sql server new users:
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


[quoted text, click to view]

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

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".

[quoted text, click to view]

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/


[quoted text, click to view]