VARCHAR(8) relust to the LEFT

Posted on 2011-05-12
Last Modified: 2012-05-11
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  ohord#, REPLACE(cast((current_time - 1 HOURS) AS varchar(8)),''.'','''')  AS f1, 
	cast(ohtime AS varchar(8)) AS f2 
FROM oohead
WHERE ohloc = 12

WHERE f1 < f2

Open in new window

Question by:jaymz69
    LVL 6

    Expert Comment

    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.
    LVL 59

    Expert Comment

    by:Kevin Cross
    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.

    Author Comment

    LVL 59

    Expert Comment

    by:Kevin Cross
    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.

    Author Comment

    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
    LVL 59

    Accepted Solution

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

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now