Oracle how to write a SQL statement

Oracle how to query a according to weeks filtering result set: such as all data query in 2013 August second weeks,
Have a table table insert_time is stored in the insertion time, ask SQL how to write another HQL how to write??

Started by Hyman at November 20, 2016 - 6:56 AM

This is very simple.
1, The parameters of your incoming, should be a date, the date according to the data obtained it for a week, like today is 9.22, you can get 9.22-9.28 date
2, Then the first step to obtain the date range into your query, select * from table where insert_time between 9.22 and 9.28 like this can not be.?

Posted by Polly at December 03, 2016 - 7:47 AM

select * from table where insert_time between to_char(insert_time,'yyyyMMdd')='20130922' and to_char(insert_time,'yyyyMMdd')='20130928'

Posted by Karida at December 07, 2016 - 8:15 AM

There is a function to_char Oracle (field,'w')

Posted by Cedric at December 18, 2016 - 8:39 AM

Months and weeks can have parameters in query mode

with t1 as
(
     select date'2013-08-02' c1 from dual union all
     select date'2013-08-05' c1 from dual union all
     select date'2013-08-08' c1 from dual union all
     select date'2013-08-11' c1 from dual union all
     select date'2013-08-22' c1 from dual union all
     select date'2013-08-26' c1 from dual 
)

select *
from t1
where to_char(c1,'w')=2
      and c1 >= to_date('2013-08','yyyy-mm') 
      and c1 <add_months(to_date('2013-08','yyyy-mm'),1)

       c1
-----------------------
1	2013/8/8
2	2013/8/11

Posted by Cedric at December 21, 2016 - 9:12 AM

Look at the 1 floor, also want to reply, see 3 floor, wouldn't have anything to say. God will answer.!

Posted by Selma at January 03, 2017 - 10:07 AM

select *
  from table t
 where to_char(t.insert_time, 'mm') = 8
   and to_char(t.insert_time, 'w') = 2;

Posted by Edison at January 11, 2017 - 10:44 AM