Muhammad Irfan

"The purpose of life is a life of purpose." Robert Byrne


Leave a comment

Specify Date Type Format in WHERE clause: Query Date Compare « Select Query « Oracle PL / SQL

SELECT MTT.CREATION_DATE FROM MTL_MATERIAL_TRANSACTIONS_TEMP MTT

Static Date Time Sample Query

SELECT TO_DATE(’17-SEP-2015 12:02:46 AM’,’DD-MON-RRRR HH:MI:SS AM’) FROM DUAL

Static Date Time Sample Query With Dynamic Input

SELECT TO_DATE(:P_DATE,’DD-MON-RRRR HH:MI:SS AM’) FROM DUAL

Static Date Time Sample Query With Dynamic Input Or Default Static Date Time Using NVL

SELECT NVL(TO_DATE(:P_DATE,’DD-MON-RRRR HH:MI:SS AM’),TO_DATE(’17-SEP-2015 12:02:46 AM’,’DD-MON-RRRR HH:MI:SS AM’)) FROM DUAL

Static Date Time Sample Query With Dynamic Input Or Default Current System Date Time Using NVL

SELECT NVL(TO_DATE(:P_DATE,’DD-MON-RRRR HH:MI:SS AM’),TO_DATE(TO_CHAR(SYSDATE,’DD-MON-RRRR HH:MI:SS AM’),’DD-MON-RRRR HH:MI:SS AM’)) FROM DUAL

Dynamic Date Time Input Sample Query With Or Default Current System Date And Static Time

time SELECT NVL(TO_DATE(:P_DATE,’DD-MON-RRRR’),TO_DATE(TO_CHAR(SYSDATE,’DD-MON-RRRR’),’DD-MON-RRRR’))||’ ‘||NVL(TO_DATE(:P_TIME,’HH:MI:SS AM’),TO_DATE(TO_CHAR(’08:30:00 AM’,’DD-MON-RRRR’),’HH:MI:SS AM’)) FROM DUAL

Perfect Query with Date Time with Dynamic Concatenation of Date and Time

SELECT TO_CHAR(MTT.CREATION_DATE,’DY DD-MON-RRRR HH:MI:SS AM’)

FROM MTL_MATERIAL_TRANSACTIONS_TEMP MTT

WHERE MTT.CREATION_DATE >=

(SELECT TO_DATE(NVL(:P_FROM_DATE,TO_CHAR(SYSDATE,’DD-MON-RRRR’))||’ ‘||NVL(:P_FROM_TIME,’08:30:00 AM’),’DD-MON-RRRR HH:MI:SS AM’) FROM DUAL)

AND MTT.CREATION_DATE <=

(SELECT TO_DATE(NVL(:P_TO_DATE,TO_CHAR(SYSDATE,’DD-MON-RRRR’))||’ ‘||NVL(:P_TO_TIME,’09:30:00 AM’),’DD-MON-RRRR HH:MI:SS AM’) FROM DUAL)

=======================================================================

Date And Time Selection Queries By Parts

SELECT NVL(:P_DATE,TO_CHAR(SYSDATE,’DD-MON-RRRR’)) FROM DUAL

SELECT NVL(:P_TIME,’08:30:00 AM’) FROM DUAL

Dynamic Date And Time Test Query

SELECT TO_DATE(NVL(:P_DATE,TO_CHAR(SYSDATE,’DD-MON-RRRR’))||’ ‘||NVL(:P_TIME,’08:30:00 AM’),’DD-MON-RRRR HH:MI:SS AM’) FROM DUAL

Perfect Query with Date Time with Static Concatenation of Date and Time

SELECT TO_CHAR(MTT.CREATION_DATE,’DY DD-MON-RRRR HH:MI:SS AM’)

FROM MTL_MATERIAL_TRANSACTIONS_TEMP MTT

WHERE MTT.CREATION_DATE >=

(SELECT NVL(TO_DATE(:P_FROM_DATE,’DD-MON-RRRR HH:MI:SS AM’),TO_DATE(TO_CHAR(SYSDATE,’DD-MON-RRRR HH:MI:SS AM’),’DD-MON-RRRR HH:MI:SS AM’)) FROM DUAL)

AND MTT.CREATION_DATE <=

(SELECT NVL(TO_DATE(:P_TO_DATE,’DD-MON-RRRR HH:MI:SS AM’),TO_DATE(TO_CHAR(SYSDATE,’DD-MON-RRRR HH:MI:SS AM’),’DD-MON-RRRR HH:MI:SS AM’)) FROM DUAL)

Advertisements