Date format in milli seconds

Hello,

I need to insert miliiseconds in a date field in oracle 9i . I use the following query

UPDATE MSGDB SET CUSTOM25=to_date('2003111212110000','YYYYMMDDHHMISSSS') WHERE MSGDB_ID = 29852;

I get the error ORA-01810: format code appears twice

when I change the query to
UPDATE MSGDB SET CUSTOM25=to_date('2003111212110000','YYYYMMDDHHMISSMS') WHERE MSGDB_ID = 29852;

I get an error : ORA-01821: date format not recognized

Please can anyone help as I need this urgently....

Thanks
d_lalitAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dramacqueenCommented:
Hi d_lalit,
> to_date('2003111212110000','YYYYMMDDHHMISSSS')
try to_char('2003111212110000','YYYYMMDDHHMISSSS')
0
Helena Markováprogrammer-analystCommented:
Here is an answer from Tom Kyte (http://asktom.oracle.com/pls/ask/f?p=4950:8:17375114488168714598::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:96012348060,):
...
Followup:

hmmm, did the people understand what sssss is?  that is "seconds past midnight"

they probably thought "hey, we are able to magically get hundredths of seconds"  (wrong, not happening) ...
0
d_lalitAuthor Commented:
Hello,

I tried using to_char
UPDATE MSGDB SET CUSTOM25=to_char('2003111212110000','YYYYMMDDHHMISSSS') WHERE MSGDB_ID = 29852;

i get the following error :- ORA-01481: invalid number format model
Also I cannot use to_char while updating a date field.

i checked the sites and found one more format SSSSS. when i used that I got the following error :
UPDATE MSGDB SET CUSTOM25=to_date('20031112121100000','YYYYMMDDHHMISSSSS') WHERE MSGDB_ID = 29852;

i get the follwoing error : ORA-01836: hour conflicts with seconds in day

Thanks
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.

dramacqueenCommented:
oops sorry, I don't think that will work.  Oracle only stores down to the level of seconds in the DATE format.  I think you are going to have to record milliseconds in some other fashion.  I'll give it some thought but you might want to increase the points to attract another guru...
0
Helena Markováprogrammer-analystCommented:
d_lalit, have you read my answer ?
0
lwadwellCommented:
Hi d_lalit,

two things ... the normal DATE datatype in Oracle does not permit milliseconds, for that you need a TIMESTAMP datatype.

TO_DATE() id for DATE datatypes, whilst TO_TIMESTAMP() is for TIMESTAMP and the fraction seconds format code is 'FF' I believe.

tryout...

SELECT TO_CHAR(SYSDATE, 'DD-Mon-YYYY HH24:MI:SS')
     , LOCALTIMESTAMP(2)
     , TO_TIMESTAMP(TO_CHAR(SYSDATE, 'DD-Mon-YYYY HH24:MI:SS')||'.55', 'DD-Mon-YYYY HH24:MI:SS.FF')
FROM dual
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
d_lalitAuthor Commented:
Hello Henka,

GOne thru the link but could not find the related info...please can you paste it in here....

Thanks
0
Helena Markováprogrammer-analystCommented:
Here you are:
...
Hi Tom,
There is a  table with dates stored as varchar2. They are derived using the conversion as follows in a PLSQL procedure:
to_char(<some_date>, 'YYYY-MM-DD HH24:MI:SSSSS').

Now, I want to convert this VARCHAR2 value as a date.

drop table t;

create table t ( ts varchar2(30) )
/

insert into t values ( '2004-07-30 07:07:25972' )
/

insert into t values ('2004-07-30 17:07:62891' )
/

select ts from t;

TS
----------------------
2004-07-30 07:07:25972
2004-07-30 17:07:62891

How do I convert the SSSSS component above into a date format.

When I try this, I get an error:
select ts, to_date(ts, 'yyyy-mm-dd hh24:mi:sssss') dt
from t
/

select ts, to_date(ts, 'yyyy-mm-dd hh24:mi:sssss') dt
                   *
ERROR at line 1:
ORA-01837: minutes of hour conflicts with seconds in day


encompass@tkd16> l
  1* select * from nls_session_parameters
encompass@tkd16> /

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-YY
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

Regards,
Venkat


Followup:

hmmm, did the people understand what sssss is?  that is "seconds past midnight"

they probably thought "hey, we are able to magically get hundredths of seconds"
(wrong, not happening)

you'll have to trunc(that sssss part/60)

or substring out the hh:mi part.

ops$tkyte@ORA9IR2> select       to_date( substr( ts, 1, 10 ) || substr( ts, 18
), 'yyyy-mm-ddsssss'     )
  2    from t;
 
TO_DATE(SUBSTR(TS,1,
--------------------
30-jul-2004 07:12:52
30-jul-2004 17:28:11

sigh, sigh, sigh, the old "store a date in a string" - what a bad bad horrible idea.  ranks up there in the top 5 things I *hate* (right next to number in a string)
...
0
earth man2Commented:
UPDATE MSGDB SET CUSTOM25=to_timestamp('2003111212110000','YYYYMMDDHHMISSFF') WHERE MSGDB_ID = 29852;
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

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.