语法：

SELECT CONVERT (data_type(length)),Date, DateFormatCode)

Ex:

Select CONVERT(varchar,GETDATE(),1) as [MM/DD/YY]
Select CONVERT(varchar,GETDATE(),2) as [YY.MM.DD]

关于日期的所有日期格式代码：

DateFormatCode  Format
1       [MM/DD/YY]
2       [YY.MM.DD]
3       [DD/MM/YY]
4       [DD.MM.YY]
5       [DD-MM-YY]
6       [DD MMM YY]
7       [MMM DD,YY]
10      [MM-DD-YY]
11      [YY/MM/DD]
12      [YYMMDD]
23      [yyyy-mm-dd]
101     [MM/DD/YYYY]
102     [YYYY.MM.DD]
103     [DD/MM/YYYY]
104     [DD/MM/YYYY]
105     [DD/MM/YYYY]
106     [DD MMM YYYY]
107     [MMM DD,YYYY]
110     [MM-DD-YYYY]
111     [YYYY/MM/DD]
112     [YYYYMMDD]

select convert(getdate() as date)

select CONVERT(datetime,CONVERT(date, getdate()))

语法：

SELECT CONVERT (data_type(length)),Date, DateFormatCode)

Ex:

Select CONVERT(varchar,GETDATE(),1) as [MM/DD/YY]
Select CONVERT(varchar,GETDATE(),2) as [YY.MM.DD]

关于日期的所有日期格式代码：

DateFormatCode  Format
1       [MM/DD/YY]
2       [YY.MM.DD]
3       [DD/MM/YY]
4       [DD.MM.YY]
5       [DD-MM-YY]
6       [DD MMM YY]
7       [MMM DD,YY]
10      [MM-DD-YY]
11      [YY/MM/DD]
12      [YYMMDD]
23      [yyyy-mm-dd]
101     [MM/DD/YYYY]
102     [YYYY.MM.DD]
103     [DD/MM/YYYY]
104     [DD/MM/YYYY]
105     [DD/MM/YYYY]
106     [DD MMM YYYY]
107     [MMM DD,YYYY]
110     [MM-DD-YYYY]
111     [YYYY/MM/DD]
112     [YYYYMMDD]

日期：

SELECT CONVERT(date, GETDATE())
SELECT CAST(GETDATE() as date)

时间：

SELECT CONVERT(time , GETDATE() , 114)
SELECT CAST(GETDATE() as time)

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

例如

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

给了我

2008-09-22 00:00:00.000

赞成的意见：

不需要varchar<->datetime转换无需考虑区域设置

SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))