Date Format

Can anyone tell me how to get SQL Server to store the date as dd/MM/yyyy,
rather than the default MM/dd/yyyy.
Thanks in anticipation,
Paul BeckettPaul,
The date is stored in neither format, but as a binary number. What you are
seeing is the way the date is interpreted to and from a character string.
This is affected by the language and locale that you run with. See SET
LANGUAGE.
Also, note Aaron Bertrand's message just a little earlier in this group,
which I quote in part: " I use the ISO standard, unambiguous, and SQL
Server safe YYYYMMDD format."
If you want SQL Server to output a string format that is different from your
machine settings, examine the CONVERT command, which has several formatting
options for dates.
Russell Fields
"Paul Beckett" <paul_beckett@.lineone.net> wrote in message
news:c15tsk$ikf$1@.titan.btinternet.com...
> Can anyone tell me how to get SQL Server to store the date as dd/MM/yyyy,
> rather than the default MM/dd/yyyy.
> Thanks in anticipation,
> Paul Beckett
>|||SQLServer doesn't store formatting information with a DATETIME or
SMALLDATETIME column. The date is actually represented internally as two
integers but it is the job of the client application to pass dates to the
server in a valid format and then to format the output for display.
When passing dates to a DATETIME/SMALLDATETIME column in SQLServer 2000, use
one of the following string formats:
'20031231' -- Just the date
'2003-12-31T17:59:00' -- Date/hours/minutes/seconds
'2003-12-31T17:59:00.000' -- Date/hours/minutes/seconds/milliseconds
these are guaranteed to work regardless of regional date format settings on
the server. Although it's possible to use SET DATEFORMAT to allow other
formats it's safer to stick to one of the standard alternatives shown above.
Format the date for display at the client side. Or use CONVERT(VARCHAR, ...,
103) to turn your dates into a VARCHAR if you must do it at the server.
--
David Portas
SQL Server MVP
--