Transaction_Select_Issue

MIHIR KAR
MIHIR KAR used Ask the Experts™
on
Hi Expert ,

i have a transaction table  which one column called " MIDTRANSSTARTTIME datatype is timestamp " i want transaction record between  01-12-17 04:44:25.696000000' - '01-12-17 04:44:34.966000000' which is around 9 min in my case .

I'm using below query but unable to get the proper record please help .
Query::
SQL> select MIDTRANSAUDITID, TOKENID from TBLTMIDTRANSAUDIT WHERE
MIDTRANSSTARTTIME BETWEEN ('01-12-17 04:44:25.696000000') and ('01-12-17 04:44:34.966000000');

Your suggestion will be acceptable.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please try this ..

select MIDTRANSAUDITID, TOKENID from TBLTMIDTRANSAUDIT WHERE
MIDTRANSSTARTTIME >= '01-12-17 04:44:25.696' and MIDTRANSSTARTTIME  < '01-12-17 04:44:34.967';
awking00Information Technology Specialist

Commented:
I think you need to explicitly create between times using to_timestamp.
MIDTRANSAUDITID, TOKENID from TBLTMIDTRANSAUDIT
WHERE MIDTRANSAUDITID >= TO_TIMESTAMP('01-12-17 04:44:25.696000000','MM-DD-RR HH:MI:SS.FF9')
  AND MIDTRANSAUDITID < TO_TIMESTAMP('01-12-17 04:44:34.966000000','MM-DD-RR HH:MI:SS.FF9')
;
awking00Information Technology Specialist

Commented:
Note - I assumed that '01-12-17 ...' stood for January 12th and not December 1st. If not, use DD-MM-RR instead of MM-DD-RR.
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
I think it is DD-MM-RR.. also since we are using < we need to add 1 second to the enddate.

Please try this -

SELECT MIDTRANSAUDITID, TOKENID from TBLTMIDTRANSAUDIT WHERE
MIDTRANSSTARTTIME >= TO_TIMESTAMP('01-12-17 04:44:25.696000000','DD-MM-RR HH:MI:SS.FF9')  
      and MIDTRANSSTARTTIME  < ( TO_TIMESTAMP('01-12-17 04:44:34.966000000','DD-MM-RR HH:MI:SS.FF9') + numToDSInterval( 1, 'second' ));
MIHIR KAR#Hadoop #Oracle_DB  #UNIX beginner

Author

Commented:
Thanks Pawan ..
your query looks good but it is not working on Oracle DB .
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
Error please?
Information Technology Specialist
Commented:
There really is no need to add a second (or even a fraction thereof), just use the between method -
SELECT MIDTRANSAUDITID, TOKENID from TBLTMIDTRANSAUDIT WHERE
 MIDTRANSSTARTTIME BETWEEN TO_TIMESTAMP('01-12-17 04:44:25.696000000','DD-MM-RR HH:MI:SS.FF9')  
       AND  TO_TIMESTAMP('01-12-17 04:44:34.966000000','DD-MM-RR HH:MI:SS.FF9')

It's up to MIHAR KAR to determine whether timestamp format is MM-DD-RR or DD-MM-RR.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Sample trial for you.

Table creation and data generation.

--

CREATE TABLE TBLTMIDTRANSAUDIT 
(
	  MIDTRANSAUDITID INT
	, TOKENID INT 
	, MIDTRANSSTARTTIME VARCHAR2(30)
)\\

INSERT INTO TBLTMIDTRANSAUDIT VALUES (1,21,'01-12-17 04:44:25.696000000')\\
INSERT INTO TBLTMIDTRANSAUDIT VALUES (2,12,'01-12-17 04:44:26.696000000')\\
INSERT INTO TBLTMIDTRANSAUDIT VALUES (3,32,'01-12-17 04:44:27.696000000')\\
INSERT INTO TBLTMIDTRANSAUDIT VALUES (4,25,'01-12-17 04:44:29.696000000')\\
INSERT INTO TBLTMIDTRANSAUDIT VALUES (5,27,'01-12-17 04:44:30.696000000')\\
INSERT INTO TBLTMIDTRANSAUDIT VALUES (6,26,'01-12-17 04:44:35.696000000')\\
INSERT INTO TBLTMIDTRANSAUDIT VALUES (7,29,'01-12-17 04:44:37.696000000')\\
INSERT INTO TBLTMIDTRANSAUDIT VALUES (8,21,'01-12-17 04:44:34.966000000')\\

--

Open in new window


SOLUTION
--

SELECT MIDTRANSAUDITID, TOKENID from TBLTMIDTRANSAUDIT WHERE 
 TO_TIMESTAMP(MIDTRANSSTARTTIME,'DD-MM-RR HH:MI:SS.FF9') BETWEEN TO_TIMESTAMP('01-12-17 04:44:25.696000000','DD-MM-RR HH:MI:SS.FF9')  
       AND  TO_TIMESTAMP('01-12-17 04:44:34.966000000','DD-MM-RR HH:MI:SS.FF9')\\


--

Open in new window


OUTPUT

--

 	MIDTRANSAUDITID	TOKENID
1	1	21
2	2	12
3	3	32
4	4	25
5	5	27
6	8	21

--

Open in new window

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Pawan,
Please read the question "MIDTRANSSTARTTIME datatype is timestamp"

So this is unnecessary:
TO_TIMESTAMP(MIDTRANSSTARTTIME,'DD-MM-RR HH:MI:SS.FF9') BETWEEN
MIHIR KAR#Hadoop #Oracle_DB  #UNIX beginner

Author

Commented:
Correct @slightwv ,  here datatype is timestamp so need transaction info between as mentioned time which is around 9 min .

but when i used above query it is not getting expected result .
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
@MIHIR

Could you please provide you table schema and some sample data.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial