数据表里面有DateTime字段, 日期和时间同时进行时间区间查询
有表
字段名称 字段类型
GUID VARCHAR(50)
str_DateTime DATETIME
想实现查询2005年5月1日-2006年1月1日
时间在18:00到19:00的所有GUID
select
*
from
TableName
where
convert ( char ( 10 ),str_DateTime, 120 ) between ' 2005-05-01 ' and ' 2006-01-01 '
and
convert ( char ( 5 ),str_DateTime, 114 ) between ' 18:00 ' and ' 19:00 '
在工作中也遇到了一个这样的问题
*
from
TableName
where
convert ( char ( 10 ),str_DateTime, 120 ) between ' 2005-05-01 ' and ' 2006-01-01 '
and
convert ( char ( 5 ),str_DateTime, 114 ) between ' 18:00 ' and ' 19:00 '
20060214T18000000转化为2006-02-14 18:00:00
declare
@var
varchar
(
50
)
set @var = ' 20060214T18000000 '
select left ( @var , 4 ) + ' - ' + substring ( @var , 5 , 2 ) + ' - ' + substring ( @var , 7 , 2 ) + ' '
+ substring ( @var , 10 , 2 ) + ' : ' + substring ( @var , 12 , 2 ) + ' : ' + substring ( @var , 14 , 2 )
把字段转化为DateTime 类型
set @var = ' 20060214T18000000 '
select left ( @var , 4 ) + ' - ' + substring ( @var , 5 , 2 ) + ' - ' + substring ( @var , 7 , 2 ) + ' '
+ substring ( @var , 10 , 2 ) + ' : ' + substring ( @var , 12 , 2 ) + ' : ' + substring ( @var , 14 , 2 )
CONVERT
(
datetime
,
LEFT
(dbo.SENTR_ExLog.IOTime,
4
)
+ ' - ' + SUBSTRING (dbo.SENTR_ExLog.IOTime, 5 , 2 )
+ ' - ' + SUBSTRING (dbo.SENTR_ExLog.IOTime, 7 , 2 )
+ ' ' + SUBSTRING (dbo.SENTR_ExLog.IOTime, 10 , 2 )
+ ' : ' + SUBSTRING (dbo.SENTR_ExLog.IOTime, 12 , 2 )
+ ' : ' + SUBSTRING (dbo.SENTR_ExLog.IOTime, 14 , 2 )) AS IOTime
屏蔽1900-1-1日的日期不显示
+ ' - ' + SUBSTRING (dbo.SENTR_ExLog.IOTime, 5 , 2 )
+ ' - ' + SUBSTRING (dbo.SENTR_ExLog.IOTime, 7 , 2 )
+ ' ' + SUBSTRING (dbo.SENTR_ExLog.IOTime, 10 , 2 )
+ ' : ' + SUBSTRING (dbo.SENTR_ExLog.IOTime, 12 , 2 )
+ ' : ' + SUBSTRING (dbo.SENTR_ExLog.IOTime, 14 , 2 )) AS IOTime
select
Guid,ProjectName,StartDate,
Case
EndDate
when
'
1900-1-1 0:00:00
'
then
''
else
Convert
(
char
(
10
),EndDate,
120
)
end
AS
EndDate,Memo,IsDelete
from pgProject
from pgProject
select Guid,RelicGuid,SerialNumber,Name,
AttachId=(case AttachId when 0 then '原始图' when 1 then '缩略图' when 2 then '浏览图' end)
from rsDrawing
使用的时候看一下,看帮助会很清晰的
如果不显示1900-1-1日的日期
(
case
MadeDate
when
'
1900/01/01
'
then
''
else
MadeDate
end
)
as
MadeDate