Query where date and time are in different fields

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

General SQL Server Forums

New to SQL Server Programming

Query where date and time are in different fields

Author

Topic

Wade
Starting Member

4 Posts

Posted - 2009-07-07 : 20:13:31

I'm new to SQL programming and so this is probably going to be an easy one from someone.

Basically, each record in my table has a date and time but the date and time are in different columns. I want to select all records after a single point in time. Here's how I'm trying it but with no luck:

SELECT *
FROM myTable t1
join
(
select * from myTable
where date > 20090707
) t2
on t1.id=t2.id
where t1.time > 160100

Can anyone tell me how to do this?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-08 : 01:47:01

select *
from myTable
where date > ...
and time > ...

No need for a join in given example.

Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

Wade
Starting Member

4 Posts

Posted - 2009-07-08 : 02:08:05

Thanks for you response.

I believe the example you give wouldn't work. If I did the following:
select *
from myTable
where date > 20090101
and time > 222222

...this would exclude a record with a date of 20090102 but that a time of 111111. I want ALL records with a date/time greater than 20090101/222222. This should include records with a time < 222222 as long as the date was greater than or equal to 20090102.

quote:
Originally posted by webfred

select *
from myTable
where date > ...
and time > ...

No need for a join in given example.

Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-08 : 02:13:34

what is the data type of your date & time column ?

KH
[spoiler]Time is always against us[/spoiler]

Wade
Starting Member

4 Posts

Posted - 2009-07-09 : 11:15:09

It is "Zoned" (it's as400). This is basically like a floating point type. So they can be added, subtracted, etc...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-09 : 11:39:25

quote:
Originally posted by Wade

Thanks for you response.

I believe the example you give wouldn't work. If I did the following:
select *
from myTable
where date > 20090101
and time > 222222

...this would exclude a record with a date of 20090102 but that a time of 111111. I want ALL records with a date/time greater than 20090101/222222. This should include records with a time < 222222 as long as the date was greater than or equal to 20090102.

EDIT: Ahh, I see the issue. I guess you'd have to combine the Date and Time together,not sure how, but maybe Webfred's solution will work.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-09 : 12:07:55

I think Wade is correct...

Try something like this - it should work but I don't know with as400:

declare @yak table (id int, datecol int, timecol int)
insert @yak
select 1, 20090707,111111 union all
select 2, 20090707,222222 union all
select 3, 20090709,111111 union all
select 4, 20090710,222222

--select * from @yak

select * from @yak where convert(varchar(8),datecol)+convert(varchar(6),timecol) > '20090707222222'

No, you're never too old to Yak'n'Roll if you're too young to die.

Wade
Starting Member

4 Posts

Posted - 2009-07-09 : 12:42:18

I can't believe I made it this difficult. :) Here's the solution:

SELECT *
FROM TableName
where (Date = myDate and
Time >= myTime) or
Date > myDate

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-10 : 03:04:59

My dictionary: the scales fell from my eyes

I hope this is a known saying