[thelist] Date and Time issue Worldwide

[rudy]

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