Need to get max update date time last 7 days [message #612396] |
Thu, 17 April 2014 11:00 |
|
cplusplus1
Messages: 58 Registered: October 2012 Location: usa
|
Member |
|
|
I want to get last 7 days Max DW_Update_Date (date and time)
select 'I_encountrs' as JOB_NAME ,
dw_update_date from I_Encntrs
where dw_update_date >= sysdate() -7
group by trunc(dw_update_date)
In sql server i use something like this: where dw_update_date >= CAST(getdate() AS DATE) - 7
Not sure how to in pl sql.
Thank you very much for helpful info.
|
|
|
|
|
Re: Need to get max update date time last 7 days [message #612403 is a reply to message #612397] |
Thu, 17 April 2014 12:52 |
|
Littlefoot
Messages: 21808 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
What problem did you meet? Did you get any error when executing your own code? If so, which one was it? It is easier for us if we don't have to guess too much.
Basically, you got it right - subtract 7 from SYSDATE and that would be it. However, as SYSDATE contains both date and time, you might want to TRUNCate it. The same - truncation - goes for DW_UPDATE_DATE. It means that your query might look like this:
select 'I_encountrs' as JOB_NAME,
trunc(dw_update_date) truncated_dw_update_date
from I_Encntrs
where trunc(dw_update_date) >= trunc(sysdate) - 7
group by trunc(dw_update_date)
If it still doesn't do the job, maybe you could provide test case and explain what output you expect from provided input.
|
|
|