Using Date Logic - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using Date Logic

Author

Topic

jedi_knight
Starting Member

6 Posts

Posted - 2012-05-29 : 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

15732 Posts

Posted - 2012-05-29 : 18:23:43

You can use GETDATE() or CURRENT_TIMESTAMP.

jedi_knight
Starting Member

6 Posts

Posted - 2012-05-29 : 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

15732 Posts

Posted - 2012-05-29 : 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

6 Posts

Posted - 2012-05-29 : 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

30421 Posts

Posted - 2012-05-29 : 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

6 Posts

Posted - 2012-05-29 : 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

52326 Posts

Posted - 2012-05-29 : 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)

17689 Posts

Posted - 2012-05-29 : 22:33:59

try

CURRENT DATE

KH
[spoiler]Time is always against us[/spoiler]

jedi_knight
Starting Member

6 Posts

Posted - 2012-05-29 : 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)

17689 Posts

Posted - 2012-05-29 : 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
[spoiler]Time is always against us[/spoiler]

jedi_knight
Starting Member

6 Posts

Posted - 2012-05-30 : 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
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources