[thelist] SQL Server DateTime Format

rudy r937 at interlog.com
Tue Jun 18 11:59:07 CDT 2002


>The current date held in the database is formatted: 6/18/02 9:20:22 AM

hi josh

that's actually a good way of saying it

it is formatted that way only when displayed using the default date
formatting style of your database, whatever that may have been set to --
dates are actually stored as integers

> I would like it to display to the user as: 6/18/02 0920

you'll probably have to do a bit of string manipulation

first, convert the date to a string using one of the style parameters of
the CONVERT function

you could look it up in Books OnLine (BOL) which (a) comes with sql/server,
or (b) is available for download, something like 26megs or whatever, useful
if one has a highspeed connection and doesn't have sql/server, or (c) is
available online on the microsoft site...

i wanted to give you the url for (b) and (c) but i didn't bookmark them and
after ten unsuccessful minutes on microsoft's site, i am reminded once
again why i hate that friggin site

anyhow, i have a text file which i saved many years ago, it should still be
valid and it's enough to get you started

fixed font recommended --


   for output, datetime values are shown in the default
   display format

     "Mon dd yyyy hh:mmAM" (or PM)

   for example, "Feb 26 1999 2:27PM"

   to get other date formats on output, use the
   CONVERT function

     convert(datatype[(length)],expression,style)

   the style parameter determines the format

   valid style parameter values:

   without    with
   century   century
    (yy)     (yyyy)        standard     output format
   -------  ----------  --------------  ---------------------------
     -     0 or 100 (*)    default      mon dd yyyy hh:miAM (or PM)
     1        101            USA        mm/dd/yy
     2        102            ANSI       yy.mm.dd
     3        103       British/French  dd/mm/yy
     4        104           German      dd.mm.yy
     5        105          Italian      dd-mm-yy
     6        106             -         dd mon yy
     7        107             -         mon dd, yy
     8        108             -         hh:mm:ss
     -     9 or 109 (*)    default      mon dd yyyy
                         milliseconds   hh:mi:ss:mmmAM (or PM)
    10        110            USA        mm-dd-yy
    11        111           Japan       yy/mm/dd
    12        112            ISO        yymmdd
     -    13 or 113 (*) Europe default  dd mon yyyy
                         milliseconds   hh:mm:ss:mmm(24h)
    14        114             -         hh:mi:ss:mmm(24h)

   (*) the default values (style 0 or 100, 9 or 109, and
       13 or 113) always return the century (yyyy)

   you can truncate unwanted date parts when converting
   from datetime or smalldatetime values by using an
   appropriate char or varchar datatype length

> Also, when the user inserts the time from a form
> will SQL Server accept the following format: 6/18/02 0920

most probably

sql/server has one of the smartest date format recognition algorithms i've
seen

rudy




More information about the thelist mailing list