[thelist] Date and Time issue Worldwide
rudy
r937 at interlog.com
Tue Apr 16 10:46:01 CDT 2002
> ... Enterprise Manager with SQL Server 7 automatically
> puts in American format: MM/DD/YYYY. ex:
> 5/8/02 -->> 5/8/2002
> 26/2/79 -->> 2/26/1979 (*my b-day* I know, I'm young)
hi rob
i think we're getting somewhere
what does "puts in" mean?
in sql/server, dates are entered in some kind of date format, and dates are
retrieved in some kind of date format, but they are *not* stored in any
particular human-recognizable format
in sql/server, dates are stored as integers
for datetime datatypes, there are two 4-byte integers: one for the number
of days before or after the base date of january 1, 1900, and one for the
number of milliseconds after midnight (dates prior to the base date are
stored as negative values)
for smalldatetime datatypes, there are two 2-byte integers: one for the
number of days after january 1, 1900, and one for the number of minutes
past midnight
getting dates out in the format of your choice is not really a problem,
because you can use the "style" parameter of the CONVERT function -- if you
want english format, i suggest style 103
this leaves inserting dates, which is where i think you're having the
difficulty
is there any reason why you cannot enter dates in ISO format as suggested?
sql/server has no problem at all with dates entered that way
here is some documentation that i stole from BOL several releases ago (it
should still be valid) --
for input, sql/server recognizes three formats --
alphabetic, numeric, and unseparated string
all three formats are strings so they have to be
contained within single quotation marks (')
1) alphabetic format
the month can be the full name or an abbreviation;
commas are optional and case is ignored
examples of acceptable alphabetic formats:
Apr[il] [15][,] 1996
Apr[il] 15[,] [19]96
Apr[il] 1996 [15]
Apr[il] [19]96 15
[15] Apr[il][,] 1996
15 Apr[il][,][19]96
15 [19]96 apr[il]
[15] 1996 apr[il]
1996 APR[IL] [15]
[19]96 APR[IL] 15
1996 [15] APR[IL]
if you specify only two digits for the year, values
less than 50 are interpreted as 20yy, and values
greater than or equal to 50 are interpreted as 19yy
you must type the century when the day is omitted or
when you need a century other than the default
if the day is missing, the first day of the month is
supplied
2) numeric format
specify month, day, and year in a string with
slashes (/), hyphens (-), or periods (.) as separators
this string must appear in the following form:
<num> <sep> <num> <sep> <num> [<time_spec>] [<time_spec>]
examples of acceptable numeric formats:
[0]4/15/[19]96 (mdy)
[0]4-15-[19]96 (mdy)
[0]4.15.[19]96 (mdy)
[04]/[19]96/15 (myd)
15/[0]4/[19]96 (dmy)
15/[19]96/[0]4 (dym)
[19]96/15/[0]4 (ydm)
[19]96/[04]/15 (ymd)
when the language is set to us_english, the default
order for the date is mdy
you can change the date order with the SET DATEFORMAT
statement, which determines how date values are
interpreted
if the order doesn't match the setting, the values
are not interpreted as dates (because they are
out of range), or the values are misinterpreted
for example, 12/10/08 can be interpreted as one of six
dates, depending on the DATEFORMAT setting
3) unseparated string format
specify four, six, or eight digits, an empty string,
or a time value without a date value
the DATEFORMAT session setting does not apply to
all-numeric date entries (numeric entries without
separators)
six- or eight-digit strings are always interpreted
as ymd -- the month and day must always be
two digits.
this is the acceptable unseparated string format:
[19]960415
a string of only four digits is interpreted as the
year, and the month and date are set to january 1
when specifying only four digits, you must include
the century
rudy
More information about the thelist
mailing list