where date='2009-11-14' where date=2009-11-14 加引号和不加引号啥区别啊？加引号查询不会报错,不加引号会报错由2009-11-14 转成DATETIME类型出错
declare @t table(date datetime) insert @t select '2009-11-13' union all select '2009-6-08' union all select '1905-6-09' select * from @t where date=2009-11-13我05的 不会报错啊？ --结果 date ----------------------- 1905-06-09 00:00:00.000(1 行受影响)
where date='2009-11-14' where date=2009-11-14 加与不加单引号没有区别的。
declare @t table(date datetime) insert @t select '2009-11-13' union all select '2009-11-13' union all select '2009-11-13'
select * from @t where date<=2009-11-13 select * from @t where date>=2009-11-13 (3 行受影响) date -----------------------(0 行受影响)date ----------------------- 2009-11-13 00:00:00.000 2009-11-13 00:00:00.000 2009-11-13 00:00:00.000(3 行受影响) --两个语句不同结果今天偶然遇到这个问题 ，搞不懂为虾米？！
declare @t table(date datetime) insert @t select '2009-11-13' union all select '2009-11-13' union all select '1905-05-09' union all select '1905-06-09'
where date=2009-11-14
加引号和不加引号啥区别啊？加引号查询不会报错,不加引号会报错由2009-11-14 转成DATETIME类型出错
declare @t table(date datetime)
insert @t
select '2009-11-13' union all
select '2009-6-08' union all
select '1905-6-09'
select * from @t where date=2009-11-13我05的 不会报错啊？
--结果
date
-----------------------
1905-06-09 00:00:00.000(1 行受影响)
where date=2009-11-14 加与不加单引号没有区别的。
declare @t table(date datetime)
insert @t
select '2009-11-13' union all
select '2009-11-13' union all
select '2009-11-13'
select * from @t where date<=2009-11-13
select * from @t where date>=2009-11-13
(3 行受影响)
date
-----------------------(0 行受影响)date
-----------------------
2009-11-13 00:00:00.000
2009-11-13 00:00:00.000
2009-11-13 00:00:00.000(3 行受影响)
--两个语句不同结果今天偶然遇到这个问题 ，搞不懂为虾米？！
------------------------------------------------------------
|--Table Insert(OBJECT:(@t), SET:(@t.[date]=[Union1006]))
|--Top(ROWCOUNT est 0)
|--Concatenation
|--Constant Scan
|--Constant Scan
|--Constant Scan
|--Constant Scan（所影响的行数为 7 行）StmtText
------------------------------------------ select * from @t where date<=2009-11-13（所影响的行数为 1 行）StmtText
----------------------------------------------------------------------
|--Table Scan(OBJECT:(@t), WHERE:(@t.[date]<='06 9 1905 12:00AM'))（所影响的行数为 1 行）StmtText
--------------------------------------------- select * from @t where date>=2009-11-13（所影响的行数为 1 行）StmtText
----------------------------------------------------------------------
|--Table Scan(OBJECT:(@t), WHERE:(@t.[date]>='06 9 1905 12:00AM'))（所影响的行数为 1 行）看下这个就清楚了
如果你不加引号的话 当成减号计算了
比如
2009-11-14=1984
而你转换成datetime以后
等价于
select cast(2009-11-14 as datetime)select dateadd(dd,1984,'1900-1-1')
/*
------------------------------------------------------
1905-06-08 00:00:00.000（所影响的行数为 1 行）
------------------------------------------------------
1905-06-08 00:00:00.000（所影响的行数为 1 行）*/
insert @t
select '2009-11-13' union all
select '2009-11-13' union all
select '1905-05-09' union all
select '1905-06-09'
select * from @t where date<=2008-11-13
select * from @t where date>=2009-11-13（所影响的行数为 4 行）Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ----------------------------------------- --------------------------------------------------------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ ------------ -------- ------------------------------ -------- ------------------------
4 1 insert @t
select '2009-11-13' union all
select '2009-11-13' union all
select '1905-05-09' union all
select '1905-06-09' 15 1 0 NULL NULL NULL NULL 4.0 NULL NULL NULL 1.6766185E-2 NULL NULL INSERT 0 NULL
4 1 |--Table Insert(OBJECT:(@t), SET:(@t.[date]=[Union1006])) 15 2 1 Table Insert Insert OBJECT:(@t), SET:(@t.[date]=[Union1006]) NULL 4.0 1.6756756E-2 0.000004 15 1.6766185E-2 NULL NULL PLAN_ROW 0 1.0
4 1 |--Top(ROWCOUNT est 0) 15 3 2 Top Top NULL NULL 4.0 0.0 0.0000004 15 5.4279999E-6 [Union1006] NULL PLAN_ROW 0 1.0
4 1 |--Concatenation 15 4 3 Concatenation Concatenation NULL [Union1006] = ([Expr1000], [Expr1001], [Expr1003], [Expr1005]) 4.0 0.0 0.0000004 15 5.028E-6 [Union1006] NULL PLAN_ROW 0 1.0
1 1 |--Constant Scan 15 5 4 Constant Scan Constant Scan NULL NULL 1.0 0.0 1.157E-6 15 1.157E-6 [Expr1000] NULL PLAN_ROW 0 1.0
1 1 |--Constant Scan 15 7 4 Constant Scan Constant Scan NULL NULL 1.0 0.0 1.157E-6 15 1.157E-6 [Expr1001] NULL PLAN_ROW 0 1.0
1 1 |--Constant Scan 15 9 4 Constant Scan Constant Scan NULL NULL 1.0 0.0 1.157E-6 15 1.157E-6 [Expr1003] NULL PLAN_ROW 0 1.0
1 1 |--Constant Scan 15 11 4 Constant Scan Constant Scan NULL NULL 1.0 0.0 1.157E-6 15 1.157E-6 [Expr1005] NULL PLAN_ROW 0 1.0（所影响的行数为 8 行）date
------------------------------------------------------
1905-05-09 00:00:00.000（所影响的行数为 1 行）Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
----------- ----------- ---------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ----------------------------------------------------- ------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ ---------- -------- ------------------------------ -------- ------------------------
1 1 select * from @t where date<=2008-11-13 16 1 0 NULL NULL NULL NULL 1.0 NULL NULL NULL 3.7658378E-2 NULL NULL SELECT 0 NULL
1 1 |--Table Scan(OBJECT:(@t), WHERE:(@t.[date]<='06 8 1905 12:00AM')) 16 3 1 Table Scan Table Scan OBJECT:(@t), WHERE:(@t.[date]<='06 8 1905 12:00AM') @t.[date] 1.0 3.7578501E-2 7.9600002E-5 15 3.7658099E-2 @t.[date] NULL PLAN_ROW 0 1.0（所影响的行数为 2 行）date
------------------------------------------------------
2009-11-13 00:00:00.000
2009-11-13 00:00:00.000
1905-06-09 00:00:00.000（所影响的行数为 3 行）Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
----------- ----------- ---------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ----------------------------------------------------- ------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ ---------- -------- ------------------------------ -------- ------------------------
3 1 select * from @t where date>=2009-11-13 17 1 0 NULL NULL NULL NULL 1.0 NULL NULL NULL 3.7658378E-2 NULL NULL SELECT 0 NULL
3 1 |--Table Scan(OBJECT:(@t), WHERE:(@t.[date]>='06 9 1905 12:00AM')) 17 3 1 Table Scan Table Scan OBJECT:(@t), WHERE:(@t.[date]>='06 9 1905 12:00AM') @t.[date] 1.0 3.7578501E-2 7.9600002E-5 15 3.7658099E-2 @t.[date] NULL PLAN_ROW 0 1.0（所影响的行数为 2 行）这又不同了
没加引号当成了减号计算就行了