MSSQL Import Source Query GETDATE Function
Posted on 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."