• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

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.
0
_Wade_
Asked:
_Wade_
  • 4
  • 3
  • 2
1 Solution
 
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
_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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now