We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL0201 - GETDATE in *LIBL type *N not found

epicazo
epicazo asked
on
Medium Priority
5,048 Views
Last Modified: 2013-11-30
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
 
Comment
Watch Question

CERTIFIED EXPERT

Commented:
AND (PPMTDT >= CONVERT(int, GETDATE())-1 )
CERTIFIED EXPERT

Commented:
this assumes that PPMTDT is a datetime and not an int.
CERTIFIED EXPERT

Commented:
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
CERTIFIED EXPERT

Commented:
... but if you want the previous date ONLY then change >= to =

Author

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

Author

Commented:
SORRY... This is the actual error..
 actuall error
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT

Commented:
so, I assume then that your PPMTDT is actually an INT and not a DATETIME ?

Author

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

Author

Commented:
Remember I am fetching data from an AS400 table into my SQL2000
CERTIFIED EXPERT

Commented:
Oh, so this query is running against AS400?
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT

Commented:
yes, CURRENT_TIMESTAMP instead of getdate(), which is SQL Server

Author

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.  

Author

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)
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT

Commented:
All of my posts using CONVERT should be scrapped -- that is SQL Server.
CERTIFIED EXPERT

Commented:
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)

Author

Commented:
I am currently researching... Knight the code you provided doesn't like CURDATE -- ERROR:

...Column CURDATE not in specified tables
CERTIFIED EXPERT

Commented:
it may be CURDATE() instead of CURDATE - I don't really know how functions work in AS400.

Author

Commented:
I am still testing... the only function that works is

char(date(NOW()), ISO)

but it give me a YYYY-MM-DD format

Author

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)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.