sql2000如何把字符串转为时间然后进行比较??

XTMENG 2009-08-04 06:10:58

表里有个字段是用字符型表示时间,如何转换为时间类型,然后比较

比如 ta(t1,t2)

t1 t2
'2009-6-2 11:41:00' '2009-6-20 21:41:00'

... ...

想把t1字段的 11:41:00提取出来,不要日期,然后把在早上8点到21点钟之间的记录筛选出来?

如何实现????

...全文

2734 15 打赏收藏转发到动态举报

写回复

用AI写文章

15 条回复

切换为时间正序

请发表友善的回复…

发表回复

zxm1983 2010-11-25

打赏
举报

select * from
(select * from t1
union all
select * from t2
union all
select * from t3)
where title like "%物%" and content like "%物%" and fabu like "%王%"
and
(datediff("d",date(edittime),2008-1-1)>0 and datediff("d",date(edittime),2009-12-13)<0)
order by edittime desc
谁能给看一下俺的sql语句问题出在哪儿吗？谢谢。

htl258_Tony 2009-08-05

打赏
举报

[Quote=引用 12 楼 lihan6415151528 的回复:]
SQL codeselect*from tawherecast(convert(varchar,t1,108)asdatetime)between'08:00'and'21:00'
[/Quote]
T1是字符型,没有再转换是不得行的.

lihan6415151528 2009-08-05

打赏
举报

select * from ta where cast(convert(varchar,t1,108) as datetime) between '08:00' and '21:00'

hui_hui_2007 2009-08-05

打赏
举报



declare @t table (t1 varchar(30),t2 varchar(30))

insert @t

select '2009-6-2 11:41:00','2009-6-20 21:41:00' union all

select '2001-3-4 09:22:12','2009-4-15 20:11:11' union all

select '2001-3-4 07:22:12','2009-4-15 17:11:11'



select *

from @t

where convert(char(10),cast(t1 as datetime),114) >= '08:00:00:0' and convert(char(10),cast(t1 as datetime),114) < '21:00:00:0'



（所影响的行数为 3 行）



/*

t1                             t2                             

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

2009-6-2 11:41:00              2009-6-20 21:41:00

2001-3-4 09:22:12              2009-4-15 20:11:11



（所影响的行数为 2 行）

*/

hui_hui_2007 2009-08-05

打赏
举报



declare @str varchar(30)

set @str='2009-6-2 11:41:00'

select cast(@str as datetime)

select convert(char(10),cast(@str as datetime),120)

/*

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

2009-06-02 11:41:00.000



（所影响的行数为 1 行）



           

---------- 

2009-06-02



（所影响的行数为 1 行）

*/

cxmcxm 2009-08-04

打赏
举报

如果字符串只有时间,随便用一固定日期串+时间串再用convert转为datetime类型进行查询
如果字符串含有日期,先将日期截掉,再加上一固定日期,再转为datetime进行查询

黄_瓜 2009-08-04

打赏
举报

[Quote=引用 6 楼 htl258 的回复:]
SQL codeIfnotobject_id('[ta]')isnullDroptable[ta]GoCreatetable[ta]([t1]nvarchar(18),[t2]nvarchar(19))Insert taSelect'2009-6-2 11:41:00','2009-6-20 21:41:00'unionallSelect'2009-6-3 11:41:00','2009-6-21¡
[/Quote]
应该不用转换那么多次吧，系统会自动判断的

select * from ta 

where

convert(varchar(10) ,cast(t1 as datetime),108)

between '08:00' and '21:00'

这样就行

soft_wsx 2009-08-04

打赏
举报

这么多，用CAST和CONVERT转换就可以了

htl258_Tony 2009-08-04

打赏
举报

If not object_id('[ta]') is null

	Drop table [ta]

Go

Create table [ta]([t1] nvarchar(18),[t2] nvarchar(19))

Insert ta

Select '2009-6-2 11:41:00','2009-6-20 21:41:00' union all

Select '2009-6-3 11:41:00','2009-6-21 21:41:00' union all

Select '2009-6-4 09:41:00','2009-6-22 21:41:00' union all

Select '2009-6-5 10:41:00','2009-6-23 21:41:00' union all

Select '2009-6-6 22:41:00','2009-6-21 21:41:00'

Go

--Select * from ta



-->SQL查询如下:

select * 

from ta 

where cast(convert(varchar,cast(t1 as datetime),108) as datetime) between '08:00' and '21:00'



/*

t1                 t2

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

2009-6-2 11:41:00  2009-6-20 21:41:00

2009-6-3 11:41:00  2009-6-21 21:41:00

2009-6-4 09:41:00  2009-6-22 21:41:00

2009-6-5 10:41:00  2009-6-23 21:41:00



(4 行受影响)

*/

sdhdy 2009-08-04

打赏
举报

select * from tb where convert(varchar(5),cast(t1 as datetime),108) >= '08:00' 

and convert(varchar(5),cast(t2 as datetime),108)<='21:00'

xiequan2 2009-08-04

打赏
举报

[Quote=引用楼主 xtmeng 的回复:]
表里有个字段是用字符型表示时间,如何转换为时间类型,然后比较

比如 ta(t1,t2)

t1 t2
'2009-6-2 11:41:00' '2009-6-20 21:41:00'

... ...

想把t1字段的 11:41:00提取出来,不要日期,然后把在早上8点到21点钟之间的记录筛选出来?

如何实现????
[/Quote]



select * from tb where  datepart(Hour, convert(varchar(20),cast(t1 as datetime),108)) between 8 and  21

htl258_Tony 2009-08-04

打赏
举报

select * from ta where cast(convert(varchar,t1,108) as datetime) between '08:00' and '21:00'

sdhdy 2009-08-04

打赏
举报

--*******************************************************************************

--                            日期转换参数大全

--********************************************************************************

select CONVERT(varchar,getdate(),120)

--2009-03-15 15:10:02



select CONVERT(varchar(10),getdate(),120)

--2009-03-15



select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')

--20090315151201



select CONVERT(varchar(12) , getdate(), 111)

--2009/03/15



select CONVERT(varchar(12) , getdate(), 112)

--20090315



select CONVERT(varchar(12) , getdate(), 102)

--2009.03.15



select CONVERT(varchar(12) , getdate(), 108)

--15:13:26



其它我不常用的日期格式转换方法:



select CONVERT(varchar(12) , getdate(), 101 )

--03/15/2009



select CONVERT(varchar(12) , getdate(), 103 )

--15/03/2009



select CONVERT(varchar(12) , getdate(), 104 )

--15.03.2009



select CONVERT(varchar(12) , getdate(), 105 )

--15-03-2009



select CONVERT(varchar(12) , getdate(), 106 )

--15 03 2009



select CONVERT(varchar(12) , getdate(), 107 )

-- 15, 2009



select CONVERT(varchar(12) , getdate(), 109 )

--03 15 2009  



select CONVERT(varchar(12) , getdate(), 110 )

--03-15-2009



select CONVERT(varchar(11) , getdate(), 113 )

--15 03 2009 



select CONVERT(varchar(12) , getdate(), 114)

--15:15:55:717