Dates are always stored the same way in SQL Server, regardless of where the server is located on the planet, and regardless of the language spoken by the people using it. :)
The
datetime datatype is stored internally as two 4 byte integers: the first indicating the number of days which have elapsed since Jan 1, 1900. This is a signed number, and dates before Jan 1, 1753 are not allowed. The second integer stores the time of day as the number of milliseconds since midnight on that date.
The
smalldatetime is similar, but using only 2 byte integers. The first representing the days since Jan 1, 1900, and the second the minutes since midnight.
So, what you are dealing with is a formatting issue, either when retrieving data and presenting it, or inputting it into the database, or specifying it in a query or in the definition of a database object (like a CONSTRAINT).
You can choose the format of the result when converting a datetime to a character string; see BOL for the CONVERT function; the
style parameter of that function allows you to specify just about any format you can think of for the format of the resultant string.
You can choose the format for display in your host language if you wish to delay conversion until the data is returned to your app. See the FORMAT function in VBScript.
You can specify the format of data for
input directly into SQL Server. See BOL for the SET DATEFORMAT command. Note that this affects how dates input into SQL Server are interpreted and has no effect on how dates are output.
See also BOL for "Writing International Transact-SQL Statements" for some tips on how to specify dates and time unambiguously regardless of the locale.
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
SQL Server date column and ASP
