[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VARCHAR(8) relust to the LEFT

Posted on 2011-05-12
6
Medium Priority
?
244 Views
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 *
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

0
Comment
Question by:jaymz69
  • 3
  • 2
6 Comments
 
LVL 6

Expert Comment

by:LCSandman8301
ID: 35747834
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
 
LVL 60

Expert Comment

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

Author Comment

by:jaymz69
ID: 35747880
DB2
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 60

Expert Comment

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

Author Comment

by:jaymz69
ID: 35748355
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
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 35750021
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

872 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