Hi, I have a database table with a date column containing dates formatted as YYYYMMDD. I need to select all records from the table with a date ranging from the current date to 5 years previous of the current date. Any assistance appreciated.
Solved
Select records from the previous 5 years based on the current date
- Participant
Best answer by david_r
What type of database is this? Most databases can do this in a simple where-clause, e.g. Oracle:
WHERE my_timestamp BETWEEN add_months(trunc(sysdate), -12*5) AND sysdateOr SQL Server:
WHERE my_timestamp BETWEEN DateAdd(yy, -5, GetDate()) AND GetDate()Or PostgreSQL:
WHERE my_timestamp BETWEEN NOW() - INTERVAL '5 years' AND NOW()You can then use this where-clause in either a regular reader, a FeatureReader or a SQLExecutor. This will be a lot faster than reading the entire table contents into FME and filter there.
+32- Influencer
Are you reading all the records in and then want to restrict to only keep the last five years, or do you just want to read the last five years in from the database table? If the latter, you probably need to specify what format your data is in.
- Best Answer
What type of database is this? Most databases can do this in a simple where-clause, e.g. Oracle:
WHERE my_timestamp BETWEEN add_months(trunc(sysdate), -12*5) AND sysdateOr SQL Server:
WHERE my_timestamp BETWEEN DateAdd(yy, -5, GetDate()) AND GetDate()Or PostgreSQL:
WHERE my_timestamp BETWEEN NOW() - INTERVAL '5 years' AND NOW()You can then use this where-clause in either a regular reader, a FeatureReader or a SQLExecutor. This will be a lot faster than reading the entire table contents into FME and filter there.
- Participant
Hi david_r & ebygomm, thanks for the replies. it's an SQL DB and I want to output all records in one stream (which I'm OK with) and output the date filtered records to another.
If you want a pure FME solution, you can use the DateTimeCalculator to create a timestamp five years back:
You can then use e.g. a Tester to check if your timestamp is between five_years_ago and @DateTimeNow()
+32- Influencer
You can also do this directly in a tester, the right statement returns a date five years ago from now and you can test for greater than this
The statement on the right gets today's date (@DateTimeNow), formats it to drop the time (@DateTimeFormat) then subtracts 5 years from that value (@DateTimeAdd)
Reply
Rich Text Editor, editor1
Editor toolbars
Press ALT 0 for help
Helpful Members This Week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.