SQL中的时间的问题

一。DateTime类型的去掉时间部分--转化为字符  来显示
CONVERT(varchar(10),
      dbo.tbl_EmpContract.ConEndDate, 111) AS ConEndDate, 这种  格式是 2006/01/02 导到Excel中 就是正常的显示格式 2006-1-2


二。 convert(varchar(10),单据日期,120)   格式 2006-1-2

三。 SET DATEFORMAT YMD  将默认的设成某一格式,如年月日

四。 startdate 2005/1/1 和 enddate  2006/1/1  不要日  来比较 只要 年月
            convert(char(7),startdate ,120)
        
          convert(datetime,year(startdate)+'-'+month(startdate)+'-01')
          convert(datetime,year(enddate)+'-'+month(enddate)+'-01')

          datepart(yy,startdate)=datepart(yy.enddate) and datepart(mm,startdate))=datepart(mm.enddate)

五。 查询 SKTime 日期 2006-1-1(包括0点到23-59-59) 至 2006-1-10(包括0点到23-59-59)  日的记录
1.
select * from tablename where convert(varchar(10),SKTime,120)>='2006-01-01' and convert(varchar(10),SKTime,120)<'2006-01-11'
2.
select * from Table where SKTime between '2006-1-1' and '2006-1-11'

以上查询:
SKTime 是datetime 或 smalldatetime 类型才行

3.
select * from Table where SKTime >='2006-1-1' and SKTime<'2006-1-11'

4.
select * from a
where sktime between '2006-1-1' and '2006-1-10'

5.
select * from Table where SKTime between '2006-01-01 00:00:00' and '2006-01-10 23:59:59.997'

六 对日期进行模糊查询
select * from abc where id like 'a%'
如果是日期型进行模糊查询，该怎么写，比如我要对2005年10月份的所有数据进行 查询怎么写?
1.
select * from 表名 where datediff(month,日期字段,'2005-10-1')=0

2.
select * from abc where datepart(year,shijianziduan)=2005 and datepart(month,shijianziduan)=10

3.
select * from abc where date between '2005-10-1' and '2005-10-31'

4.
可以先转换成字符型,再用%呀

参考：
CAST and CONVERT (T-SQL)
Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.

Syntax
Using CAST:

CAST(expression AS data_type)

Using CONVERT:

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

Arguments
expression
Is any valid Microsoft&reg; SQL Server&#8482; expression. For more information, see Expressions.
data_type
Is the target system-supplied data type. User-defined data types cannot be used. For more information about available data types, see Data Types.
length
Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types.
style
Is the style of date format you want when converting datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or the string format when converting float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).
In the table, the two columns on the left represent the style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).

 

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)
*    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.
 

--------------------------------------------------------------------------------

Important By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That is, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. Many client applications, such as those based on OLE Automation objects, use a cutoff year of 2030. SQL Server provides a configuration option (two digit year cutoff) that changes the cutoff year used by SQL Server and allows the consistent treatment of dates. The safest course, however, is to specify four-digit years.