asked on

CAST() on Time from AS400

When I cast the time from the AS400 the result is 07.19.36

How can I get it without the periods?
So I can compare the order creation time which come across as text...

SELECT *
FROM OPENQUERY (MyData, '
SELECT cast((current_time - 1 HOURS) AS varchar(8)) AS f1,  cast(ohtime AS varchar(8)) AS f2 
FROM MyTable
WHERE fld1 = 12
	AND fldDate = CURRENT_DATE


')
WHERE f1 < f2

Open in new window

knightEknight

SELECT REPLACE( cast((current_time - 1 HOURS) AS varchar(8)), '.', '' ) AS f1, REPLACE( cast(ohtime AS varchar(8)), '.', '' ) AS f2

jaymz69

ASKER

now how to get the leading zeros out ?

the results now look like:
073451

I need it to look like :
73451

knightEknight

SELECT convert(int,REPLACE( cast((current_time - 1 HOURS) AS varchar(8)), '.', '' )) AS f1,
convert(int,REPLACE( cast(ohtime AS varchar(8)), '.', '' )) AS f2

jaymz69

ASKER

my fs is not in time format, comes out as string

jaymz69

ASKER

Did not like that

OLE DB provider "IBMDA400" for linked server "MyData" returned message "SQL0206: Column INT not in specified tables.
Cause . . . . . : INT is not a column of table *N in *N. If the table is *N, INT is not a column of any table or view that can be referenced. Recovery . . . : Do one of the following and try the request again: -- Ensure that the column and table names are specified correctly in the statement. -- If this is a SELECT statement, ensure that all the required tables were named in the FROM clause. -- If the column was intended to be a correlated reference, qualify the column with the correct table designator.".
Msg 7321, Level 16, State 2, Line 4
An error occurred while preparing the query "
SELECT ohord#, CONVERT(int,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

knightEknight

Does this not work for you?

select 1
where convert(int,'073451') = convert(int,'73451')

ASKER CERTIFIED SOLUTION

knightEknight

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

knightEknight

If the following SQL works on AS400, I think the above query will work for you as well:

select 1
where cast( '073451' as integer ) = cast( '73451' as integer )

jaymz69

ASKER

That was it

Thanks