?
Solved

CAST() on Time from AS400

Posted on 2011-05-12
9
Medium Priority
?
1,464 Views
Last Modified: 2012-06-22
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

0
Comment
Question by:jaymz69
  • 5
  • 4
9 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 35747412
SELECT REPLACE( cast((current_time - 1 HOURS) AS varchar(8)), '.', '' ) AS f1,  REPLACE( cast(ohtime AS varchar(8)), '.', '' ) AS f2
0
 

Author Comment

by:jaymz69
ID: 35747472
now how to get the leading zeros out ?

the results now look like:
  073451  

I need it to look like :
  73451
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35747498
SELECT convert(int,REPLACE( cast((current_time - 1 HOURS) AS varchar(8)), '.', '' )) AS f1,  
           convert(int,REPLACE( cast(ohtime AS varchar(8)), '.', '' )) AS f2
0
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.

 

Author Comment

by:jaymz69
ID: 35747523
my fs is not in time format, comes out as string
0
 

Author Comment

by:jaymz69
ID: 35747541
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
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35747549
Does this not work for you?

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

Accepted Solution

by:
knightEknight earned 2000 total points
ID: 35747945
I see now that the SQL is actually being executed on AS400, not SQL Server.  Here is the same query using AS400 syntax:

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

However, I can't verify whether doing this will actually work on AS400 as it does in SQL Server, because some systems treat numbers with leading zeros as Octal numbers.  (SQL Server does not.)
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35748188
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 )
0
 

Author Closing Comment

by:jaymz69
ID: 35748389
That was it

Thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 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