×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

getdate() in parameter

getdate() in parameter

getdate() in parameter

(OP)
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.

RE: getdate() in parameter

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.

RE: getdate() in parameter

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

----signature below----
Majority rule don't work in mental institutions

My Crummy Web Page

RE: getdate() in parameter

(OP)
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.

RE: getdate() in parameter

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?

----signature below----
Majority rule don't work in mental institutions

My Crummy Web Page

RE: getdate() in parameter

(OP)
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.

RE: getdate() in parameter

Yeah, I thought so.

You are going to need to use Oracle syntax.

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

CODE

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

----signature below----
Majority rule don't work in mental institutions

My Crummy Web Page

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close