MIHIR KAR

asked on 12/13/2017

Transaction_Select_Issue

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.

Oracle Database

Last Comment

Pawan Kumar

8/22/2022 - Mon

Pawan Kumar

12/13/2017

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';

awking00

12/13/2017

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')
;

awking00

12/13/2017

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.

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

Pawan Kumar

12/13/2017

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

12/13/2017

ASKER

Thanks Pawan ..
your query looks good but it is not working on Oracle DB .

SOLUTION

Pawan Kumar

12/13/2017

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

ASKER CERTIFIED SOLUTION

awking00

12/13/2017

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Pawan Kumar

12/13/2017

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

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

slightwv (䄆 Netminder)

12/13/2017

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

12/13/2017

ASKER

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 Kumar

12/13/2017

@MIHIR

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy