using time in a criteria - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

using time in a criteria

Author

Topic

chippyles
Yak Posting Veteran

68 Posts

Posted - 2006-08-09 : 14:44:59

I have a datetime field named tRSSQL_TRANS. i am trying to build a criteria that allows me to only return data that is after 7 am that day. I know how to return data for a certain day, but when it comes to time I am stumped.

Example data in tRSSQL_TRANS...
08/01/2006 11:00:00 AM
08/03/2006 10:15:00 AM
08/07/2006 01:30:00 PM
08/08/2006 11:45:00 PM

If I type 07:00:00 AM for the criteria the sql code comes in as CONVERT(DATETIME, '1899-12-30 07:00:00', 102))

I have tried to manipulate the date in the sql code, but it is not working for me. And this can not be as simple as all times over 7 am. It has to be all times passed 7 am for that day. How can I do this??

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-08-09 : 15:27:05

hmmm.... Theres gotta be a cleaner way, but this will get you started:

declare @table table (Entry datetime)
insert into @table
	select '08/08/2006 11:45:00 PM' union
	select '08/09/2006 06:59:00 AM' union
	select '08/09/2006 07:01:00 AM'	union
	select '08/09/2006 07:00:00 AM'

declare @CheckDate	datetime,
	@CheckTime	varchar(4)

select	@CheckDate = '08/09/2006',
	@CheckTime = '07:00'

select	Entry		
from	@table
where	CAST(DATEDIFF(d,0,Entry) AS DATETIME) = CAST(DATEDIFF(d,0,@CheckDate) AS DATETIME) and 
	Entry - CAST(DATEDIFF(d,0,Entry) AS DATETIME) >= @CheckTime

Ill keep thinking about it

Nathan Skerl

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-09 : 15:38:22

this shows the principle. modiy it to your needs.


declare @t table (dt datetime)
insert into @t
select GetDate() union all
select dateadd(hh, -1, GetDate()) union all
select dateadd(hh, -2, GetDate()) union all
select dateadd(hh, -3, GetDate()) union all
select dateadd(hh, -4, GetDate()) union all
select dateadd(hh, -5, GetDate())

select * from @t
declare @after int
set @after = 18

SELECT *
from @t
where dt > dateadd(hh, @after, DATEADD(d, DATEDIFF(d, 0, dt), 0))

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources