Query where date and time are in separate fields

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 (the fields are on an AS400 stored as "Zoned" datatypes, which is basically like a float).

It won't work to do:
select *
from myTable
where date >= 20090101
and time >= 222222
...because 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.

My intuition is that I need to do a query that returns all dates >= myDate, then, in the result set, Select * if (date=myDate AND time=myTime) OR (date>myDate).

I have no idea how to represent this in SQL Syntax.
_Wade_Asked:
Who is Participating?
 
pcelbaCommented:
Several possible ways exist.

select *
from myTable
where (date = 20090101 and time >= 222222) or date > 20090101

Or you could convert date and time to one text string and compare it as a string.

select *
from myTable
where STR(date) + STR(time) >= "20090101222222"

I am not sure about the exact STR function availability, syntax and its output. It depends on your SQL engine. You have to test it for times less than 120000 because the missing digit would cause another problems.
0
 
mrjoltcolaCommented:
What form is your time in? I'm using what you provided.


select * from myTable where date >=  convert(datetime, '20090101', 112)
   and time >= 222222
;


However, can you clarify the types of those columns and what is stored exactly?
0
 
_Wade_Author Commented:
The fields are on an AS400 stored as "Zoned" datatypes, which is basically like a float.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
_Wade_Author Commented:
One more thing...

My goal is to put the sql query into an SSIS package while will use ODBC to execute the command against the AS400 DB. But, I'm practicing the command natively on the as400 command line.
0
 
mrjoltcolaCommented:
>>.because 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.

This does not make sense.

If you want that, then ignore the time column altogether.



>>I want ALL records with a date/time greater than 20090101/222222. This should include records with a time < 222222

That does not compute. 20090101/111111 is not greater than 20090101/222222, but you say you want to include them. I think maybe your logic was misworded here?

0
 
pcelbaCommented:
The first query mentioned in my answer ID:24814801 should work for you independently on the way of data retrieving.

The STR() function usage you have to check from AS400 command line.
0
 
pcelbaCommented:
You may ask for data type conversions here at EE in a zone especially derived for AS400 (http://www.experts-exchange.com/Programming/System/AS_-_400/)

AS400 experts should know better what everything is available for you.
0
 
_Wade_Author Commented:
mrjoltcola:

>>I want ALL records with a date/time greater than 20090101/222222. This should include records with a time < 222222

That does not compute. 20090101/111111 is not greater than 20090101/222222, but you say you want to include them. I think maybe your logic was misworded here?

Maybe few more words would have clarfied:
"This should include records with a time < 222222 [as long as the date for those records is greater than 20090101]..."

0
 
_Wade_Author Commented:
Worked great!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.