VARCHAR(8) relust to the LEFT

I want reults to be brought to the LEFt

that way when I comare the two fields I do not get a record with a space then it throws of my results.


SELECT *
FROM OPENQUERY (GSFL2K, '
SELECT  ohord#, REPLACE(cast((current_time - 1 HOURS) AS varchar(8)),''.'','''')  AS f1, 
	cast(ohtime AS varchar(8)) AS f2 
FROM oohead
WHERE ohloc = 12
	AND ohdate = CURRENT_DATE


')
WHERE f1 < f2

Open in new window

jaymz69Asked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
It may be easier to convert the string in OHTIME to an actual TIME value.  I am not a DB/2 officianado as I haven't had to program to it in 10-12 years, so my default would be to bring the data back "as-is" from the OpenQuery and do the conversion in SQL; however, concept should be the same for DB/2 syntax.  

This may be a helpful resource: http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.html
Search "char(current time)" without the quotes and it should take you to section on converting back and forth from TIME data type using CHAR() and TIME() functions.

As I said, my first choice would be to convert the text to TIME and then compare the time values.  That way, you avoid any oddities where alphanumeric sort puts times out of chronological order.
0
 
LCSandman8301Commented:
you can use ltrim(value) to remove spaces from the front of a characterstring and rtrim(value) to remove spaces from the end

so you can try ltrim(rtrim(value)) to get rid of all the blank spaces for your value.
0
 
Kevin CrossChief Technology OfficerCommented:
What database platform and version is GSFL2K?  Maybe let the values flow to SQL as date/time and do the comparisons (with conversions if necessary) using SQL functions.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
jaymz69Author Commented:
DB2
0
 
Kevin CrossChief Technology OfficerCommented:
Agree with above, btw, that you can use the LTrim/RTrim functions, but I wouldn't convert date/time fields to VARCHAR if ultimately your goal is to compare the two as date/time values.
0
 
jaymz69Author Commented:
the field for time created is in text in the db2

and the CURRENT TIME is in time format

I just want to somehow compare the two, either string or time but both are not in equal format
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.