i've explored a few more date to string format conversions. nothing really new here, but here are some observations.
select convert(varchar, getdate(), 102), convert(varchar, getdate(), 2)
select convert(varchar, getdate(), 120), convert(varchar, getdate(), 12)
select convert(varchar, getdate(), 101), convert(varchar, getdate(), 1)
select convert(varchar, getdate(), 100), convert(varchar, getdate(), 0)
output:
------------------------------ ------------------------------
2004.06.07 04.06.07
------------------------------ ------------------------------
2004-06-07 14:38:42 040607
------------------------------ ------------------------------
06/07/2004 06/07/04
------------------------------ ------------------------------
Jun 7 2004 2:38PM Jun 7 2004 2:38PM
ok... so format #2 returns yy.mm.dd and and #102 returns yyyy.mm.dd
this means you can use either format #102 or #120 like this... and this method may still be the most succinct way to format a date, returning the first of the month...
declare @mydate datetime
set @mydate = convert(char(7), getdate(), 120) + '-01'
select @mydate
set @mydate = convert(char(7), getdate(), 102) + '-01'
select @mydate
that should really be '.01' on format 102. but i left that in to make a point - it means you can format strings using mixed delimeters (even include spaces too) :
declare @mydate datetime
select @mydate = '2004 .06 / 08'
select @mydate
and sql server still composes the date as June 8th, 2004
here's the full list of conversion styles (from t-sql docs).
Without century (yy), With century (yyyy), Standard Input/Output**
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 + milliseconds mon dd yyyy 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 + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)
14, 114, - hh:mi:ss:mmm(24h)
20, or 120, (*) ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
21, or 121, (*) ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)
126, (***) ISO8601 yyyy-mm-dd Thh:mm:ss:mmm(no spaces)
130, * Kuwaiti dd mon yyyy hh:mi:ss:mmmAM
131, * Kuwaiti dd/mm/yy hh:mi:ss:mmmAM
* The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy).
** Input when converting to datetime; output when converting to character data.
*** Designed for XML use. For conversion from datetime or smalldatetime to character data, the output format is as described in the table. For conversion from float, money, or smallmoney to character data, the output is equivalent to style 2. For conversion from real to character data, the output is equivalent to style 1.