Sql|Orace date type sample Sql

 1 .Time interval

select messagename , avg( aday)*24 *3600 as seconds from (      

SELECT MESSAGENAME , ( to_date(to_char (LASTUPDATEDDATE, 'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS' )

 - to_date(to_char (CREATEDDATE, 'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS' )

) as aday FROM WSG_TXN_MASTER

WHERE STATUSFLAG ='Y'

and transactionid >060711000000000 and transactionid<060727999999999

AND MESSAGENAME in (

'ActivateServiceRequest',

'PortInInitiateRequest',

'PortInServiceOrderRequest')

)

group by messagename

 

2. convert date string to date type and then can use between/>/< to compare

 

select * from wsg_lnp_txn_detail

where statusflag ='SNA_Status_OK'

and losingcarrierid =donorcarrierid

and cutoverdatetime between

to_date( '2007-07-12 12:30:00','YYYY-MM-DD HH24:MI:SS' ) and

to_date( '2007-07-12 12:50:00','YYYY-MM-DD HH24:MI:SS' )

 

3. jo_request_time is varchar(23 ), directly compare

 

SELECT jo_message_id ,jo_request_time, ip,jo_process ,jo_service, jo_timediff,jo_returncode

FROM ec_journal WHERE jo_type = 'R'

AND jo_request_time >= '2006-07-25 11:00:00,000'

AND jo_returncode = 'RC_SENT'

 

4.insert new filed data type is date

insert into wsg_txn_events values('060711000000493' ,

 to_date( '2007/07/12 13:00:00', 'yyyy/mm/dd hh24:mi:ss'),

 'dummy' ,'SNA_Status_OK', '','Y' )