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.
MIHIR KAR#Oracle_DB #UNIX beginnerAsked:
Who is Participating?
 
awking00Commented:
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.
0
 
Pawan KumarDatabase ExpertCommented:
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';
0
 
awking00Commented:
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')
;
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
awking00Commented:
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.
0
 
Pawan KumarDatabase ExpertCommented:
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' ));
0
 
MIHIR KAR#Oracle_DB  #UNIX beginnerAuthor Commented:
Thanks Pawan ..
your query looks good but it is not working on Oracle DB .
0
 
Pawan KumarDatabase ExpertCommented:
Error please?
0
 
Pawan KumarDatabase ExpertCommented:
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

0
 
slightwv (䄆 Netminder) 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
0
 
MIHIR KAR#Oracle_DB  #UNIX beginnerAuthor 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 .
0
 
Pawan KumarDatabase ExpertCommented:
@MIHIR

Could you please provide you table schema and some sample data.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.