Solved

MSSQL Import Source Query GETDATE Function

Posted on 2013-01-25
2
531 Views
Last Modified: 2013-01-25
Is there a way to utilize the GETDATE function in a source query for SQL Import/Export Wizard?

For example, I want to import data from a Pervasive table but limit the data to the last five years.  The SOURCE table has the field TransDateYYYYMMDD that I would like to use for this filter.  This query works fine:

SELECT * FROM <SOURCE> WHERE TRANSDATEYYYYMMDD>=20010101

but I would rather write something that uses a rolling date, like this

SELECT * FROM <SOURCE> WHERE TRANSDATEYYYYMMDD>=(year(getdate())-5)*10000+101

or something similar so that I would only import data for the last five years or so.

When I try to use the GETDATE function in the source query, MSSQL doesn't like it and says it's an "invalid user-defined or scalar function."
0
Comment
Question by:trbbhm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 6

Accepted Solution

by:
liija earned 500 total points
ID: 38820416
If you are importing from pervasive-database, isn't that query going to Pervasive db then?
I mean your error is not SQL Server error, it's Pervasive error.

I'm not familiar with Pervasive SQL but you should use Pervasive's own function, similar to SQL Server's getdate().
0
 

Author Closing Comment

by:trbbhm
ID: 38820431
I certainly was not thinking along those lines.  Thank you for clearing my head!!
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

729 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