We help IT Professionals succeed at work.

Query where date and time are in separate fields

240 Views
Last Modified: 2013-12-25
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.
Comment
Watch Question

Top Expert 2009

Commented:
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?

Author

Commented:
The fields are on an AS400 stored as "Zoned" datatypes, which is basically like a float.
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

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.
Top Expert 2009

Commented:
>>.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?

CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT

Commented:
You may ask for data type conversions here at EE in a zone especially derived for AS400 (https://www.experts-exchange.com/Programming/System/AS_-_400/)

AS400 experts should know better what everything is available for you.

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]..."

Author

Commented:
Worked great!!!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.