sql2005 若字段定义的类型为datetime,插入为''(空),那么会默认值为1900-01-01 00:00:00.000
2011年06月08日 星期三 15:42
sql2005 若字段定义的类型为datetime,插入为''(空),那么会默认值为1900-01-01 00:00:00.000
(运行:select cast('' as datetime) 产生:1900-01-01 00:00:00.000)
解决:
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
select
cast
(
''
as
datetime
)
, cast ( nullif ( '' , '' ) as datetime )
, isnull ( cast ( nullif ( '' , '' ) as datetime ), getdate ())
/*
----------------------- ----------------------- -----------------------
1900-01-01 00:00:00.000 NULL 2009-02-25 17:18:15.140
(1 行受影响)
*/
, cast ( nullif ( '' , '' ) as datetime )
, isnull ( cast ( nullif ( '' , '' ) as datetime ), getdate ())
/*
----------------------- ----------------------- -----------------------
1900-01-01 00:00:00.000 NULL 2009-02-25 17:18:15.140
(1 行受影响)
*/
更清楚的:
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
declare
@date
varchar
(
20
)
set @date = ''
select cast ( nullif ( @date , '' ) as datetime )
set @date = ' 20090225 '
select cast ( nullif ( @date , '' ) as datetime )
/*
-----------------------
NULL
(1 行受影响)
-----------------------
2009-02-25 00:00:00.000
(1 行受影响)
*/
set @date = ''
select cast ( nullif ( @date , '' ) as datetime )
set @date = ' 20090225 '
select cast ( nullif ( @date , '' ) as datetime )
/*
-----------------------
NULL
(1 行受影响)
-----------------------
2009-02-25 00:00:00.000
(1 行受影响)
*/
法二:也可以在.NET程序中处理:
a.
insert
into
表名 (Datetime字段名)
values
(控件提取的时间
==
string.Empty ? "
Null
" : "
'
" + 控件提取的时间+"
'
")
b.
insert
into
表名 (Datetime字段名)
values
(
cast
(
nullif
(
'页面控件提取的时间'
,
'
'
))
as
DateTime
)