• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1286
  • Last Modified:

How to compare AS/400 numeric(8,0) data to SQL*Server's current date

I am writing a DTS on sql*server 2000, to retrieve all records from an AS400 table where the end date is less than or equal to the current date.  

The table on the AS/400 is joord, the field is joendd defined as numeric(8,0), and the data is in the format YYYYMMDD.

Is the following the way to do this?

Select * from joord where joendd <= convert(numeric(8,0), getdate(), 112)







0
dvpiper
Asked:
dvpiper
  • 6
  • 5
  • 2
1 Solution
 
sudheeshthegreatCommented:
no, that won't give you the desired result. you can convert it to varchar
convert(varchar, getdate(), 112)
but while comparing you would probably have to convert joendd as varchar as well.

the best approach would be to convert the joendd into datetime and then compare with current date.
Select * from joord where convert(datetime,joendd ,112) <= getdate()
0
 
rafranciscoCommented:
You can try this:

Select * from joord where joendd <= cast(convert(varchar(10), getdate(), 112) as numeric(8,0))

convert(numeric(8,0), getdate(), 112) gives you the number of days from 1900-01-01 and not the desired date in th YYYYMMDD format.

Hope this helps.
0
 
sudheeshthegreatCommented:
yes, this will work as well. but, here you are making two type conversions whereas if you convert the joendd field to datetime, you have to convert it just once for every record in the table.

i guess an ever better approach would be to store the numeric format of current date in a local variable and then compare it with the field directly. with that, we save on the type conversions for each row in the table. thanks, rafrancisco. :-)

declare @today numeric (8,0)
set @today = cast(convert(varchar(10), getdate(), 112) as numeric(8,0))
Select * from joord where joendd <= @today
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dvpiperAuthor Commented:
I get back Client Access Token errors on all 3 examples.  The 3rd example returns the least amount of errors and is:

ADO error:  [IBM][Client Access Express ODBC Driver(32-bit)[DB2/400] SQL0104 - Token numeric was not valid Valid Tokens: Statement.

Any suggestions?
0
 
rafranciscoCommented:
Try this one:

Select * from joord where joendd <= cast(convert(varchar(10), getdate(), 112) as decimal(8,0))

I simply changed numeric to decimal, and I am assuming that AS 400 knows this token.

Hope this helps.
0
 
dvpiperAuthor Commented:
When  I leave the getdate functions inside the sql statement, instead of declaring a variable, a different Client Access Token en Error is returned: SQL0204 - getdate in *LIBL type *N not found

0
 
rafranciscoCommented:
Since the statement is being executed in AS400, you should be using the equivalent of getdate() in AS400.  I am not familiar with AS400 so I am not sure what this would be.

Same case with the cast and convert, you should find its equivalent in AS400.
0
 
rafranciscoCommented:
Try this one:

Select * from joord where joendd <= current_date

I believe current_date is the equivalent of getdate() in AS400.
0
 
dvpiperAuthor Commented:
You are absolutely correct.  The currernt_date does not error out, but it will not evaluate without a cast or convert equivalent on the AS/400.
0
 
rafranciscoCommented:
Here's another try:

Select * from joord where DATE(joendd) <= current_date
0
 
dvpiperAuthor Commented:
Almost.  What comes back is a SQL0802 error, data conversion or data mapping error, but at least it complies!
0
 
rafranciscoCommented:
One more time:

Select * from joord where joendd <= (year(current_date) * 10000) + (month(current_date) * 100) + (day(current_date))
0
 
dvpiperAuthor Commented:
Thank you so much for working through this problem with me!!!   I would have never figured this out on my own!!!
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now