Home » SQL & PL/SQL » SQL & PL/SQL » Need to get max update date time last 7 days (11)
Need to get max update date time last 7 days [message #612396] Thu, 17 April 2014 11:00 Go to next message
cplusplus1
Messages: 40
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 #612397 is a reply to message #612396] Thu, 17 April 2014 11:36 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/
Re: Need to get max update date time last 7 days [message #612402 is a reply to message #612396] Thu, 17 April 2014 12:48 Go to previous messageGo to next message
joy_division
Messages: 4520
Registered: February 2005
Location: East Coast USA
Senior Member
I don't know sql server, but what is wrong with
sysdate - 7
and of course your select needs to match your group by in oracle.

[Updated on: Thu, 17 April 2014 12:49]

Report message to a moderator

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 Go to previous message
Littlefoot
Messages: 19648
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.
Previous Topic: Missing right paren... subquery trouble
Next Topic: getting finacial week number
Goto Forum:
  


Current Time: Tue Sep 30 19:54:44 CDT 2014

Total time taken to generate the page: 0.09493 seconds