OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Date Format

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Date Format [message #7298]

Tue, 03 June 2003 10:16

Heidi
Messages: 37
Registered: February 2000

Member

Hi again,
How can I convert my TIMESTAMP to a date which when copied and pasted into excel is recognized as a date ("09-MAY-03" instead of "Fri May 09 17:17:21 PDT 2003")?

select s.facilitycode,
ev.designatedOwner,
ev.eventtype,
ev.equipmentnumber,
es.vendorestimateNumber EstimateNumber,
ev.timestamp transactionDate,
ev.timestamp systemdate,
e.equipmenttype,
er.estimatestatus conditionCode
from
facilitysegment s,
equipment e,
repairestimate es,
estimaterevision er,
equipmentevent ev
WHERE ev.timestamp >= TO_DATE ('01-MAY-2003' , 'DD-MON-YYYY')
AND ev.timestamp < TO_DATE ('01-JUN-2003' , 'DD-MON-YYYY')
and ev.designatedOwner = 'SNT'
/* and s.facilitycode= 'ARBUEICBA' */
/* and ee.equipmentnumber = 'TRIU458507' */
and ev.eventtype = 'estimate'
and ev.facilitysegmentid = s.facilitysegmentid
and e.equipmentnumber= s.equipmentnumber
and es.facilitysegmentid = s.facilitysegmentid
and ev.transactionNUmber = es.transactionNumber
and es.originalrevisionid = er.estimaterevisionid
order by designatedOwner, eventtype, ev.equipmentnumber, es.vendorestimateNumber, facilitycode

Report message to a moderator

Re: Date Format [message #7302 is a reply to message #7298]

Tue, 03 June 2003 13:13

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

Use the

TO_CHAR

function to format your dates however you want them:

select   s.facilitycode, 
         ev.designatedOwner, 
         ev.eventtype, 
         ev.equipmentnumber, 
         es.vendorestimateNumber EstimateNumber, 
         <b>TO_CHAR (ev.timestamp, 'DD-MON-YY')</b> transactionDate, 
         <b>TO_CHAR (ev.timestamp, 'DD-MON-YY')</b> systemdate, 
         e.equipmenttype, 
         er.estimatestatus conditionCode
from     facilitysegment s, 
         equipment e, 
         repairestimate es,
         estimaterevision er, 
         equipmentevent ev
WHERE    ev.timestamp >= TO_DATE ('01-MAY-2003' , 'DD-MON-YYYY')
AND      ev.timestamp < TO_DATE ('01-JUN-2003' , 'DD-MON-YYYY')
and      ev.designatedOwner = 'SNT'
/* and s.facilitycode= 'ARBUEICBA' */
/* and ee.equipmentnumber = 'TRIU458507' */
and      ev.eventtype = 'estimate' 
and      ev.facilitysegmentid = s.facilitysegmentid
and      e.equipmentnumber= s.equipmentnumber
and      es.facilitysegmentid = s.facilitysegmentid
and      ev.transactionNUmber = es.transactionNumber
and      es.originalrevisionid = er.estimaterevisionid
order by ev.designatedOwner, 
         ev.eventtype, 
         ev.equipmentnumber, 
         es.vendorestimateNumber, 
         s.facilitycode

Report message to a moderator

Previous Topic:	PUBBLD.SQL required while connecting
Next Topic:	spool actual scripts

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 22:52:12 CDT 2024