Per formattare una data in una query SQL dobbiamo usare la funzione CONVERT:
CONVERT(varchar,@theDate,104)
Sintassi CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Direttamente da MsdN riporto i possibili valori per "style":
Date and Time Styles
When expression is a date or time data type, style can be one of the values shown in the following table. Other values are processed as 0. SQL Server supports the date format in Arabic style by using the Kuwaiti algorithm.
| Without century (yy) (1) | With century (yyyy) | Standard | Input/Output (3) |
|
-
|
0 or 100 (1, 2)
|
Default
|
mon dd yyyy hh:miAM (or PM)
|
|
1
|
101
|
U.S.
|
mm/dd/yyyy
|
|
2
|
102
|
ANSI
|
yy.mm.dd
|
|
3
|
103
|
British/French
|
dd/mm/yyyy
|
|
4
|
104
|
German
|
dd.mm.yy
|
|
5
|
105
|
Italian
|
dd-mm-yy
|
|
6
|
106 (1)
|
-
|
dd mon yy
|
|
7
|
107 (1)
|
-
|
Mon dd, yy
|
|
8
|
108
|
-
|
hh:mi:ss
|
|
-
|
9 or 109 (1, 2)
|
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
yyyymmdd
|
|
-
|
13 or 113 (1, 2)
|
Europe default + milliseconds
|
dd mon yyyy hh:mi:ss:mmm(24h)
|
|
14
|
114
|
-
|
hh:mi:ss:mmm(24h)
|
|
-
|
20 or 120 (2)
|
ODBC canonical
|
yyyy-mm-dd hh:mi:ss(24h)
|
|
-
|
21 or 121 (2)
|
ODBC canonical (with milliseconds)
|
yyyy-mm-dd hh:mi:ss.mmm(24h)
|
|
-
|
126 (4)
|
ISO8601
|
yyyy-mm-ddThh:mi:ss.mmm (no spaces)
|
|
-
|
127(6, 7)
|
ISO8601 with time zone Z.
|
yyyy-mm-ddThh:mi:ss.mmmZ
(no spaces)
|
|
-
|
130 (1, 2)
|
Hijri (5)
|
dd mon yyyy hh:mi:ss:mmmAM
|
|
-
|
131 (2)
|
Hijri (5)
|
dd/mm/yy hh:mi:ss:mmmAM
|
1 These style values return nondeterministic results. Includes all (yy) (without century) styles and a subset of (yyyy) (with century) styles.
2 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).
3 Input when you convert to datetime; output when you convert to character data.
4 Designed for XML use. For conversion from datetime or smalldatetime to character data, the output format is as described in the previous table.
5 Hijri is a calendar system with several variations. SQL Server uses the Kuwaiti algorithm.
oppure si puo integrare questo codice CLR
[Microsoft.SqlServer.Server.
SqlFunction]public static SqlString FormatDateTime(SqlDateTime value, string format)
{
if (value.IsNull )
{
return new SqlString();
}
else{
return (SqlString)((DateTime)value).ToString(format);
}
}
E qui c'e' una overview sul CLR integration: http://msdn.microsoft.com/en-us/library/ms345136(SQL.90).aspx