SQL time-based Query Solution

(1) SQL query by Time

I didn't pay too much attention to the time-based query in SQL statements. An SQL query error is returned.

Select D. *, D. rowid from dy_date d Where D. work_group_id = '05807e481fd0f1c0011ffde5ad457be4 'and D. duty_date <= to_date ('2017-03-17 ')
Order by D. duty_date, D. start_time;

 

Note that the function that converts a string to a date should have a date format parameter.

Therefore, the modified SQL statement should be as follows:

 

 

Select D. *, D. rowid from dy_date d Where D. work_group_id = '05807e481fd0f1c0011ffde5ad457be4 'and D. duty_date <= to_date ('2017-03-17 ', 'yyyy-mm-dd ')
Order by D. duty_date, D. start_time;

 

(2) SQL query by time period

For example, table 1:

60

100

150

The expected result is

-20
-75
-110
-150

This can be implemented in Oracle

Create Table T (cdate date, cnum INT );
/
Insert into t
Select to_date ('2017-6-27 ', 'yyyy-mm-dd hh24: Mi'), 20 from dual Union all
Select to_date ('1970-6-27 ', 'yyyy-mm-dd hh24: Mi'), 60 from dual Union all
Select to_date ('1970-6-27 ', 'yyyy-mm-dd hh24: Mi'), 15 from dual Union all
Select to_date ('1970-6-27 ', 'yyyy-mm-dd hh24: Mi'), 2007 from dual Union all
Select to_date ('1970-6-27 ', 'yyyy-mm-dd hh24: Mi'), 10 from dual Union all
Select to_date ('2017-6-27 ', 'yyyy-mm-dd hh24: Mi'), 2007 from dual;
/
Select to_char (cdate, 'yyyy-mm-dd') | ''| to_char (cdate, 'hh24') | ': 00-' | to_char (cdate, 'hh24') | ': 59', sum (cnum) from T group by to_char (cdate, 'yyyy-mm-dd'), to_char (cdate, 'hh24 ')
-- Execution result
-20
-75
-110
-150