Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL0201 - GETDATE in *LIBL type *N not found

Posted on 2011-04-21
22
Medium Priority
?
2,838 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
 
0
Comment
Question by:epicazo
  • 12
  • 9
22 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 35440546
AND (PPMTDT >= CONVERT(int, GETDATE())-1 )
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35440552
this assumes that PPMTDT is a datetime and not an int.
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35440650
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
Independent Software Vendors: 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!

 
LVL 33

Expert Comment

by:knightEknight
ID: 35440654
... but if you want the previous date ONLY then change >= to =
0
 

Author Comment

by:epicazo
ID: 35440794
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
 

Author Comment

by:epicazo
ID: 35440816
SORRY... This is the actual error..
 actuall error
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35440849
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
 
LVL 33

Expert Comment

by:knightEknight
ID: 35440856
so, I assume then that your PPMTDT is actually an INT and not a DATETIME ?
0
 

Author Comment

by:epicazo
ID: 35440946
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
 

Author Comment

by:epicazo
ID: 35440965
Remember I am fetching data from an AS400 table into my SQL2000
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35440969
Oh, so this query is running against AS400?
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 450 total points
ID: 35440973
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
 
LVL 33

Expert Comment

by:knightEknight
ID: 35440978
yes, CURRENT_TIMESTAMP instead of getdate(), which is SQL Server
0
 

Author Comment

by:epicazo
ID: 35440987
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
 

Author Comment

by:epicazo
ID: 35441003
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
 
LVL 33

Assisted Solution

by:knightEknight
knightEknight earned 300 total points
ID: 35441015
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
 
LVL 33

Expert Comment

by:knightEknight
ID: 35441030
All of my posts using CONVERT should be scrapped -- that is SQL Server.
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35441047
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
 

Author Comment

by:epicazo
ID: 35441562
I am currently researching... Knight the code you provided doesn't like CURDATE -- ERROR:

...Column CURDATE not in specified tables
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35441578
it may be CURDATE() instead of CURDATE - I don't really know how functions work in AS400.
0
 

Author Comment

by:epicazo
ID: 35441636
I am still testing... the only function that works is

char(date(NOW()), ISO)

but it give me a YYYY-MM-DD format
0
 

Author Closing Comment

by:epicazo
ID: 35442843
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

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

571 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