|
View previous topic :: View next topic
|
| Author |
Message |
senthamizh
New User
Joined: 27 Apr 2009
Posts: 23
Location: mumbai
|
|
|
|
Hi All,
My requirement is to fetch records from table x whose last update time stamp is between yesterday 00.00.00.000000 and yesterday 23.59.59.999999.
for example if suppose i am running the query in batch spufi today say current date is 2015-05-24. then i have to get records from the table which are got updated between '2015-05-24.00.00.00.000000' and '2015-05-24.23.59.59.999999'.
is the above query is possible. I am planing to execute the query in batch and schedule it thru a job which will run daily and generate report.
I have the last update column in the table. |
|
| Back to top |
|
 |
mjadwani2785
New User
Joined: 28 Apr 2009
Posts: 89
Location: Noida , Dublin
|
|
|
|
Hi ,
Try this in where clause of your SQL
where DATE( timestamp_col - 1 DAY) = CURRENT DATE - 1 DAY
Hope this helps !!
Regards,
MJ |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| Quote: |
| My requirement is to fetch records from table x whose last update time stamp is between yesterday 00.00.00.000000 and yesterday 23.59.59.999999. |
| Quote: |
| for example if suppose i am running the query in batch spufi today say current date is 2015-05-24. then i have to get records from the table which are got updated between '2015-05-24.00.00.00.000000' and '2015-05-24.23.59.59.999999'. |
they are contradictory.
Declare something like this and use it in where condition.
| Code: |
01 date-range
05 date-to
10 current-date PIC X(10).
10 high-timestmp PIC X(16) VALUE '.23.59.59.999999'.
05 date-from
10 current-date PIC X(10).
10 low-timestmp PIC X(16) VALUE '.00.00.00.000000'. |
|
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10872
Location: italy
|
|
|
|
| Quote: |
| My requirement is to fetch records from table x whose last update time stamp is between yesterday 00.00.00.000000 and yesterday 23.59.59.999999. |
it looks like you are overshooting ...
wouldn' t it be simpler just to ask for the rows with a last update date of yesterday ??? |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
| mjadwani2785 wrote: |
Hi ,
Try this in where clause of your SQL
where DATE( timestamp_col - 1 DAY) = CURRENT DATE - 1 DAY
Hope this helps !!
Regards,
MJ |
"- 1 DAY" is a nice option, but once is enough:
| Code: |
| WHERE DATE(timestamp_update) = CURRENT DATE - 1 DAY |
|
|
| Back to top |
|
|
|
|
