[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Date format in milli seconds

Posted on 2004-11-04
9
Medium Priority
?
17,632 Views
Last Modified: 2011-08-18
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
0
Comment
Question by:d_lalit
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 2

Expert Comment

by:dramacqueen
ID: 12502057
Hi d_lalit,
> to_date('2003111212110000','YYYYMMDDHHMISSSS')
try to_char('2003111212110000','YYYYMMDDHHMISSSS')
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12502102
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
 

Author Comment

by:d_lalit
ID: 12502123
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 2

Expert Comment

by:dramacqueen
ID: 12502175
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
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12502226
d_lalit, have you read my answer ?
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 150 total points
ID: 12502251
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
 

Author Comment

by:d_lalit
ID: 12502314
Hello Henka,

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

Thanks
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12502355
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
 
LVL 22

Expert Comment

by:earth man2
ID: 12506884
UPDATE MSGDB SET CUSTOM25=to_timestamp('2003111212110000','YYYYMMDDHHMISSFF') WHERE MSGDB_ID = 29852;
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question