Automation: Oracle sql-query data based on even hours

Friday, November 13, 2009

Oracle sql-query data based on even hours

A simple oracle query data based on even hours. here is a simple example.

Table name: Tab1

IDATE
RINDEX
A0101
A0102
A0103
A0104
A0105
A0106
A0107
A0108

Sql-query:

SELECT TO_CHAR(IDATE,'dd-mm-yy HH24:MI'),

A0101,

A0102,

A0103,

A0104,

A0105,

A0106,

A0107,

A0108

FROM Tab1 WHERE (MOD(To_Number(TO_CHAR(IDATE,'HH24')),2))=0 AND IDATE BETWEEN to_date(inDte1,'dd-mm-yyyy HH24:MI:SS') AND to_date(inDte2,'dd-mm-yyyy HH24:MI:SS') ORDER BY RINDEX;

Here (MOD(To_Number(TO_CHAR(IDATE,'HH24')),2))=0 is doing that selection from available data. First get the hour's value as string then convert it into a Number and apply MOD function.

2 comments:

Anonymous said...: Hi,
How can I query data by month?

// Chain; November 15, 2009 at 10:23 PM
Adhir Dutta said...: By Date:
SELECT * FROM Table_Name WHERE date_columnname=to_date('04/04/2010','mm/dd/yyyy')
//It will show data for 04/04/2011 only

By Month:
SELECT * FROM Table_Name WHERE to_char(date_columnname,'MM')=04 AND to_char(date_columnname,'YYYY')=2011
//It will show all data for 04/2011 only

//Dutta; April 4, 2011 at 12:54 AM