| Author |
 Topic  |
|
|
jedi_knight
Starting Member
USA
6 Posts |
 Posted - 05/29/2012 : 18:06:40
|
I'm trying include date logic that follows this idea: NXT_PMT_DUE_DAT <= today's date. In SAS, you can use TODAY() in that line of code.
Does SQL have an equivalent date function that I can implement? When I use CURDATE(), I get the following error in the message log:
OLE DB provider "MSDASQL" for linked server "ACLS_PROD" returned message "[IBM][CLI Driver][DB2] SQL0206N "CURDATE" is not valid in the context where it is used. SQLSTATE=42703
".
Msg 7350, Level 16, State 2, Line 19
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "ACLS_PROD".
Any idea how I can resolve this? |
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 05/29/2012 : 18:23:43
|
| You can use GETDATE() or CURRENT_TIMESTAMP. |
 |
|
|
jedi_knight
Starting Member
USA
6 Posts |
Posted - 05/29/2012 : 18:26:03
|
Thanks for the advice but I'm not sure how to use it. Here's what the code looks like:
SELECT *
FROM OPENQUERY(ACLS_PROD, 'SELECT A182.BK_NUM as Bank,
A182.LN_NUM as Loan_Number,
A182.ACT_STA as Ln_Sta,
A182.NXT_PMT_DUE_DAT as Due_Date,
A182.CUR_LN_BAL as Balance,
A182.INS_REF_NUM as Debt_Can_Ref
FROM PZFRL.ALT0182 A182
WHERE A182.ACT_STA IN(''0'', ''1'', ''2'')
AND (A182.INS_REF_NUM IN(''SDC'', ''SDD'', ''SDU'', ''SLF'', ''SUF'', ''DSF'',
''JDC'', ''JDD'', ''JDU'', ''JLF'', ''JDF'')
AND A182.NXT_PMT_DUE_DAT <= GETDATE())
ORDER BY A182.BK_NUM,
A182.LN_NUM
')
Do you know why it's jacked up?
quote:
Originally posted by robvolk
You can use GETDATE() or CURRENT_TIMESTAMP.
|
|
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 05/29/2012 : 18:42:26
|
| Well, what does "jacked up" mean? Do you get an error? No data? Wrong data? Is ACLS_PROD a SQL Server, or another RDBMS product? |
|
|
|
jedi_knight
Starting Member
USA
6 Posts |
Posted - 05/29/2012 : 18:50:21
|
Sorry. I guess I should've been more specific. Here's a copy of the error in the message log:
OLE DB provider "MSDASQL" for linked server "ACLS_PROD" returned message "[IBM][CLI Driver][DB2] SQL0440N No authorized routine named "GETDATE" of type "" having compatible arguments was found. SQLSTATE=42884
".
Msg 7350, Level 16, State 2, Line 19
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "ACLS_PROD".
quote:
Originally posted by robvolk
Well, what does "jacked up" mean? Do you get an error? No data? Wrong data? Is ACLS_PROD a SQL Server, or another RDBMS product?
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 05/29/2012 : 18:59:40
|
Try CURRENT_TIMESTAMP instead as that is the standard function name.
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
jedi_knight
Starting Member
USA
6 Posts |
Posted - 05/29/2012 : 22:18:25
|
That didn't work either.
Hmmm, anyone else have a suggestion?
quote:
Originally posted by SwePeso
Try CURRENT_TIMESTAMP instead as that is the standard function name.
N 56°04'39.26"
E 12°55'05.63"
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 05/29/2012 : 22:21:54
|
quote:
Originally posted by jedi_knight
That didn't work either.
Hmmm, anyone else have a suggestion?
quote:
Originally posted by SwePeso
Try CURRENT_TIMESTAMP instead as that is the standard function name.
N 56°04'39.26"
E 12°55'05.63"
what do you mean by it didnt work? did you get some error?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 05/29/2012 : 22:33:59
|
try
CURRENT DATE
KH
Time is always against us
|
|
|
|
jedi_knight
Starting Member
USA
6 Posts |
Posted - 05/29/2012 : 23:00:07
|
Yes. Same error.
quote:
Originally posted by visakh16
quote:
Originally posted by jedi_knight
That didn't work either.
Hmmm, anyone else have a suggestion?
quote:
Originally posted by SwePeso
Try CURRENT_TIMESTAMP instead as that is the standard function name.
N 56°04'39.26"
E 12°55'05.63"
what do you mean by it didnt work? did you get some error?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 05/29/2012 : 23:30:43
|
Maybe you should post your query in a DB2 forums like http://www.dbforums.com/db2/ as the error is not related to SQL Server but DB2. And there aren't many DB2 expert around here.
KH
Time is always against us
|
|
|
|
jedi_knight
Starting Member
USA
6 Posts |
Posted - 05/30/2012 : 09:45:48
|
I debugged the code and found that CURRENT DATE worked. Thank you for your help. The code is working.
quote:
Originally posted by khtan
try
CURRENT DATE
KH
Time is always against us
|
|
|
| |
Topic |
|
Using Date Logic
