Convert a long date to a short date using convert in SQL Server

My code:

Convert (date, mydatetime)

Bytes --------------------------------------------------------------------------------------------------------------------------

Generally, the time format stored in the database isYyyy-mm-DDHH: mm: SSIf you want to convertYyyy-mm-ddShort date format. You can use the convert function.

The following is the declaration of the convert function in sqlserver help:
Use convert:

CONVERT (data_type[(length)],expression[,style])

Parameters

Expression

Is any valid Microsoft SQL Server expression. It is generally the field name.
Data_type

Data Types provided by the target system, includingBigintAndSQL _variant. User-Defined data types cannot be used.
Length

Nchar,Nvarchar,Char,Varchar,BinaryOrVarbinaryOptional parameter of the data type.

Style

Date FormatDatetimeOrSmalldatetimeConvert data to character data (Nchar,Nvarchar,Char,Varchar,NcharOrNvarcharData Type); or string format styleFloat,Real,MoneyOrSmallmoneyConvert data to character data (Nchar,Nvarchar,Char,Varchar,NcharOrNvarcharData Type ).

SQL Server supports the data format in the Arabic style using the Kuwait algorithm.

In the table, the two columns on the left representDatetimeOrSmalldatetimeConvert to the style value of the character data. Add 100 to the style value to obtain the four-digit year (yyyy) of the century ).

Without Century digital (yy)	Digital Century (YYYY)	Standard	Input/Output **
-	0 or 100 (*)	Default Value	Mon dd yyyy hh: miam (or pm)
1	101	USA	Mm/DD/YYYY
2	102	ANSI	YY. Mm. dd
3	103	UK/France	Dd/mm/yy
4	104	Germany	Dd. mm. yy
5	105	Italy	DD-mm-yy
6	106	-	Dd mon YY
7	107	-	Mon DD, YY
8	108	-	Hh: mm: SS
-	9 or 109 (*)	Default Value + millisecond	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 (*)	European default value + millisecond	Dd mon yyyy hh: mm: SS: Mmm (24 h)
14	114	-	Hh: MI: SS: Mmm (24 h)
-	20 or 120 (*)	ODBC specifications	Yyyy-mm-dd hh: mm: ss [. fff]
-	21 or 121 (*)	ODBC specifications (in milliseconds)	Yyyy-mm-dd hh: mm: ss [. fff]
-	126 (***)	Iso8601	Yyyy-mm-dd thh: mm: SS: Mmm (excluding spaces)
-	130 *	Kuwait	Dd mon yyyy hh: MI: SS: mmmam
-	131 *	Kuwait	Dd/mm/yy hh: MI: SS: mmmam

* The default value (style0 or 100, 9 or 109, 13 or 113, 20 or 120, 21 or 121) always returns century digits (yyyy ).
** When convertingDatetimeInput; output when converted to character data.
* ** It is specially used for XML. ForDatetimeOrSmalldatetimeToCharacterData conversion. The output format is shown in the table. ForFloat,MoneyOrSmallmoneyToCharacterData conversion. The output is equivalent to style2. ForRealToCharacterData conversion. The output is equivalent to style1.

ImportantBy default, SQL Server interprets two-digit years based on the end year 2049. That is, the year 49 with two digits is interpreted as 2049, and the year 50 with two digits is interpreted as 1950. Many client applications (such as client applications based on OLE automation objects) Use 2030 as the end year. SQL Server provides a configuration option ("") to change the end year used by SQL Server and process the date in a consistent manner. However, the safest way is to specify a four-digit year.

WhenSmalldatetimeWhen converting to character data, the style that contains seconds or milliseconds will display zero at these locations. WhenDatetimeOrSmalldatetimeYou can use the appropriateCharOrVarcharThe length of the Data Type to truncate the date part that is not required.
========================================================== ======================================
If you only need to obtain the time in the yyyy-mm-dd format, you can use convert (nvarchar (10), field, 120)
120 is the format code, and nvarchar (10) is the first 10 characters.
Example:
Select convert (nvarchar (10), publish_time, 120) from table_name

 

From: http://asdic.iteye.com/blog/354118