SQL0201 - GETDATE in *LIBL type *N not found

How could I automate this date criteria...

currently hard coded:
SELECT     IADVDTA.HPPATMFL.*
FROM         IADVDTA.HPPATMFL
WHERE     ("PPGRP#" BETWEEN '20' AND '30') AND (PPMTCD IN ('A',
'C', 'D')) AND (PPMTDT >= 20110420)

What I want is to pull only previous date:
SELECT     IADVDTA.HPPATMFL.*
FROM         IADVDTA.HPPATMFL
WHERE     ("PPGRP#" BETWEEN '20' AND '30') AND (PPMTCD IN ('A',
'C', 'D')) AND (PPMTDT >= CONVERT(VARCHAR,
                      DATEADD(d, - 1, CONVERT(varchar, GETDATE(), 112)), 112))

... but I am getting an error message
 
epicazoAsked:
Who is Participating?
 
Daniel WilsonCommented:
Your error is coming from yoru connection to an AS/400 iSeries -- not a connection to the MS SQL Server.

The AS/400 iSeries does use different date functions!

http://www.sqlthing.com/as400_datetime_scalars.htm

Have a look at CURRENT_TIMESTAMP.
0
 
knightEknightCommented:
AND (PPMTDT >= CONVERT(int, GETDATE())-1 )
0
 
knightEknightCommented:
this assumes that PPMTDT is a datetime and not an int.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
knightEknightCommented:
so, if PPMTDT is an INT field, with values in this format: 20110420, then the query changes like this:

AND convert(datetime,convert(varchar,PPMTDT)) >= CONVERT(int,GETDATE())-1
0
 
knightEknightCommented:
... but if you want the previous date ONLY then change >= to =
0
 
epicazoAuthor Commented:
Still erroring out...

SELECT     IADVDTA.HPPATMFL.*
FROM         IADVDTA.HPPATMFL
WHERE     ("PPGRP#" BETWEEN '20' AND '30') AND (convert(datetime,convert(varchar,PPMTDT)) >= CONVERT(int,GETDATE())-1)

 VARCHAR ERROR
0
 
epicazoAuthor Commented:
SORRY... This is the actual error..
 actuall error
0
 
knightEknightCommented:
hmm, that doesn't make any sense from your SQL in post #35440794.  Try converting it to char(8) instead of varchar.  I don't really expect this to fix the problem, but it may shed some more light on the error.
0
 
knightEknightCommented:
so, I assume then that your PPMTDT is actually an INT and not a DATETIME ?
0
 
epicazoAuthor Commented:
This syntax works...
SELECT IADVDTA.HPPATMFL.*
FROM IADVDTA.HPPATMFL
WHERE ("PPGRP#" BETWEEN '20' AND '30') AND (PPMTCD IN ('A',
'C', 'D')) AND (PPMTDT >= 20110420)

But I am trying to automate DTS

0
 
epicazoAuthor Commented:
Remember I am fetching data from an AS400 table into my SQL2000
0
 
knightEknightCommented:
Oh, so this query is running against AS400?
0
 
knightEknightCommented:
yes, CURRENT_TIMESTAMP instead of getdate(), which is SQL Server
0
 
epicazoAuthor Commented:
My SQL2000 DTS has an ODBC connection to AS400.  Sorry if I didn't explain myself, I thought the error message [IBM][iSeries Access ODBC.... was adequate.  
0
 
epicazoAuthor Commented:
knigh... the following didn't work

SELECT     IADVDTA.HPPATMFL.*
FROM         IADVDTA.HPPATMFL
WHERE     ("PPGRP#" BETWEEN '20' AND '30') AND (convert(datetime,convert(varchar,PPMTDT)) >= CONVERT(int,CURRENT_TIMESTAMP)-1)
0
 
knightEknightCommented:
No, I expect it wouldn't, since that is all SQL Server syntax.  :)
See the link posted above by DanielWilson.  It should help you figure out how to derive the previous day, perhaps based on CURRENT_TIMESTAMP.
0
 
knightEknightCommented:
All of my posts using CONVERT should be scrapped -- that is SQL Server.
0
 
knightEknightCommented:
CURDATE might be closer to what you want.  I'm just guessing here:

SELECT     IADVDTA.HPPATMFL.*
FROM         IADVDTA.HPPATMFL
WHERE     ("PPGRP#" BETWEEN '20' AND '30') AND (PPMTDT >= CURDATE -1)
0
 
epicazoAuthor Commented:
I am currently researching... Knight the code you provided doesn't like CURDATE -- ERROR:

...Column CURDATE not in specified tables
0
 
knightEknightCommented:
it may be CURDATE() instead of CURDATE - I don't really know how functions work in AS400.
0
 
epicazoAuthor Commented:
I am still testing... the only function that works is

char(date(NOW()), ISO)

but it give me a YYYY-MM-DD format
0
 
epicazoAuthor Commented:
Thank you all...  

I figured it out.  It's kind of messy but it works. The following will give me Previous Date in YYYYMMDD format (ex 20110420)......

substr (char (Char((CURRENT_DATE-1 day),ISO)),1,4)) ||''||(substr (char (Char((CURRENT_DATE-1 day),ISO)),6,2)) ||''||(substr (char (Char((CURRENT_DATE-1 day),ISO)),9,2)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.