表中的一个日期字段datefield,类型为varchar,存储的数据有NULL,还有类似2008-01-01,以及01/01/2008这样格式的数据,现在想查询某一日期段的数据,SQL如下:
select cast(datefield as datetime) from table where
cast(datefield as datetime) > '2008-01-01' and datefield is not null但是现在查询的时候SQL SERVER问题提示:
服务器: 消息 241,级别 16,状态 1,行 1
从字符串转换为 datetime 时发生语法错误。怎么解决这个问题呢?
select cast(datefield as datetime) from table where
cast(datefield as datetime) > '2008-01-01' and datefield is not null但是现在查询的时候SQL SERVER问题提示:
服务器: 消息 241,级别 16,状态 1,行 1
从字符串转换为 datetime 时发生语法错误。怎么解决这个问题呢?
最好用convert(datetime ,datefield ,120)并且指定格式代码转换, select convert(datetime,datefield ,120) from table where convert(datetime,datefield ,120) > '2008-01-01' and datefield is not null
create table a
(
datefield varchar(20)
)
insert into a values('2008-01-01')
insert into a values('01/02/2008')select cast(datefield as datetime) from a where
cast(datefield as datetime) > '2008-01-01' and datefield is not null
-----------------------
2008-01-02 00:00:00.000(1 row(s) affected)
[datefile] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
)
06 3 2008 3:41PM
NULL
06 3 2008 3:42PM
06/03/2008
06/03/2008
06 3 2008 3:34PM
06/03/2008
06 3 2008 3:25PM
NULL
06/03/2008
06/03/2008
06/03/2008
06 3 2008 3:20PM
06 3 2008 3:20PM
06/03/2008
06 3 2008 3:12PM
NULL
06/03/2008
06/03/2008
06 3 2008 3:26PM
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
06/03/2008
06/03/2008
06/03/2008
06/03/2008
06/03/2008
NULL
NULL
NULL
06/03/2008
06/03/2008
06/03/2008
这是从库里导出来的部分数据
try
select datefield from table where
datediff(day,'2008-01-01',datefield)>0 and datefield is not null
--example
declare @t varchar(50)
set @t='2008-06-02'
select datediff(day,ltrim(@t),'2008-06-02')
可以的话修改列
--修改列
alter table t alter column groups varchar(20)
CREATE TABLE dbo.a (
datefile varchar(50) COLLATE Chinese_PRC_CI_AS NULL
)
insert into a
select
'06 3 2008 3:41PM' union all select
NULL union all select
'06 3 2008 3:42PM' union all select
'06/03/2008' union all select
'06/03/2008' union all select
'06 3 2008 3:34PM'select cast(replace(datefile,' ','-') as datetime) from a where
cast(replace(datefile,' ','-') as datetime) > '2008-01-01' and datefile is not null
结果
-----------------------
2008-06-03 15:41:00.000
2008-06-03 15:42:00.000
2008-06-03 00:00:00.000
2008-06-03 00:00:00.000
2008-06-03 15:34:00.000(5 row(s) affected)
你可以
使用
select distinct datefield from table
大致看一下数据,有没有很太过杂乱的数据!
这样才可以找到问题所在!
你可以
使用
select distinct datefield from table
大致看一下数据,有没有很太过杂乱的数据!
这样才可以找到问题所在!
数据太乱调整一下数据就ok!
select cast(replace(datefile,' ','-') as datetime) from a where
cast(replace(datefile,' ','-') as datetime) > '2008-01-01' and datefile is not null
我用你给的数据运行没问题啊
CREATE TABLE dbo.a (
datefile varchar(50) COLLATE Chinese_PRC_CI_AS NULL
)
insert into a
select
'06 3 2008 3:41PM' union all select
NULL union all select
'06 3 2008 3:42PM' union all select
'06/03/2008' union all select
'06/03/2008' union all select
'06 3 2008 3:34PM' select cast(replace(datefile,' ','-') as datetime) from a where
cast(replace(datefile,' ','-') as datetime) > '2008-01-01' and datefile is not null
结果
-----------------------
2008-06-03 15:41:00.000
2008-06-03 15:42:00.000
2008-06-03 00:00:00.000
2008-06-03 00:00:00.000
2008-06-03 15:34:00.000 (5 row(s) affected)
[datefield] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
)
go
insert into mytable
select '06 3 2008 3:41PM' union all
select NULL union all
select '06/03/2008 ' union all
select '2008-01-02'
go
select cast(datefield as datetime)
from mytable
where
cast(datefield as datetime) > '2008-01-01' and datefield is not null
go
我执行这个没有出错,这几种数据都有,除非例外的数据
select distinct datefield from table
请把你的数据贴出来!
from a
where cast(replace(datefile,' ','-') as datetime) > '2008-01-01'
and datefile is not null
and isdate(datefield)=0