load based upon date

Posted on 2011-09-19
Last Modified: 2013-11-16
I have two columns createdt and changedt both character 14 looks like (20101108140439) I want to do daily load based upon these two dates with date() function

So sumthing like this if createdt or changedt changed in last 2 days capture those last 2 days and insert data

Select * from aa
Where createdt or changedt <= date() -2 days

Other thing is we have global macro &loaddt if I want to use that
Instead of date() how can I do tht.
Question by:sam2929
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
  • 3
  • 3
LVL 14

Expert Comment

by:Aloysius Low
ID: 36565013
what you could do is to compare date() - 2 against substring of createdt and changedt in your where condition:
where input(substr(createdt, 1, 8), yymmdd8.) <= date() - 2 or input(substr(changedt, 1, 8), yymmdd8.) <= date() - 2

if you have a macro loaddt and that variable already stores a numeric date value, then you could simply replace date() with &loaddt, otherwise, you'll need to convert the value stored in &loaddt into numeric first, so it all depends on how the value in &loaddt is stored

Author Comment

ID: 36566542
i am running the below query which should pick last 2 days but its just picking all the days

select createddt,input(substr(createddt, 1, 8), yymmdd8.),date() from aa
where input(substr(createddt, 1, 8), yymmdd8.) <= date() - 2
LVL 14

Accepted Solution

Aloysius Low earned 500 total points
ID: 36566837
my mistake, it should be >= instead

otherwise, it might be safer to use in (date() - 1, date() - 2) to explicitly state the 2 dates that you are interested in...
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!


Author Comment

ID: 36566875
what if the date format is date9. what format should where caluse be then

Author Comment

ID: 36566882
what if the createddt format is date9. what format should where caluse be then
LVL 14

Expert Comment

by:Aloysius Low
ID: 36566889
anydtdte should work

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

615 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