[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