getdate() in parameter

[grnzbra]

I am writing a DTS package to import records from ORACLE which have a date between 14 days from today's date and 28 days from today's date. In the query analyzer, I have no problem setting this up, but it doesn't translage into a DTS package.

How can the SQL statement be set up to allow this kind of parameter to be entered.

 

[RiverGuy]

1. When you say you would like a parameter to be entered, what exactly does that mean? DTS isn't intended to be an end-user GUI that prompts a user to enter data.

2. Are your date ranges always going to be the same, or are you going to have ad-hoc ranges from time to time? If your date ranges are always going to be the same (although a rolling window of time as you have stated), then your best bet is to used equivalent functions in Oracle to SQL Server's GETDATE()/DATEADD, and then code those into your query statically.

3. Are you saying you are using Microsoft SQL Server Query Analyzer to run this query against an Oracle database? If so, I suspect you are using a linked server. It would probably be better to use an Oracle driver in your DTS package and hit the Oracle database directly.

 

[AlexCuse]

I don't think you can use getdate() in Oracle. You might try using CURRENT_TIMESTAMP which is ANSI compliant. But I'm not sure if Oracle would offer that or not.

If you're trying to pass a parameter from your package, you need to use VBScript not T-SQL syntax in setting its' value.

OTTOMH, something like DateAdd("d", -14, Date())

If this does not get it done, please post more info on what you are doing.

Hope this helps,

ALex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[grnzbra]

I am trying to get there records for the dates between 14 and 28 days in the future. It means the difference between pulling a few thousand records and pulling in excess of 4 million records across the network.

 

[AlexCuse]

Ok. HOW are you trying to get them? Is the date criteria hard-coded in your source query, or is it coming from the package?

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[grnzbra]

In the workflow, I am using a query in which I am trying to say

WHERE OC_DT BETWEEN DATEADD(d,14,GETDATE()) AND DATEADD(d,28,GETDATE())

I have tried all sorts of variations on this and none work.

 

[AlexCuse]

Yeah, I thought so.

You are going to need to use Oracle syntax.

I know oracle has a dateadd function. Try something like this:

between DATEADD('dd', 14, CURRENT_TIMESTAMP) and DATEADD('dd', 28, CURRENT_TIMESTAMP)

I'm not sure if Oracle supports CURRENT_TIMESTAMP, but it it's part of the ansi standard so hopefully it does (SQL Server does too, and its' the same thing as getdate()).

I would get in the habit of using CURRENT_TIMESTAMP if you work across systems, because it will work more often than it won't (and I don't know of anything but SQL Server where getdate() works).

HOpe this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page