Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

Milliseconds Not Capture

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Milliseconds Not Capture
# 1  
Old 10-27-2017
Milliseconds Not Capture
while I load the value using sqlldr the millisecond values not stored in oracle table.

Value:
Code:
'26-OCT-17 08.59.50.916000000 AM'

CTL field:

Code:
SRC_SYS_CRT_TS  Position(23:48) "decode(:SRC_SYS_CRT_TS,null,sysdate-1,to_timestamp(:SRC_SYS_CRT_TS,'yyyy-mm-dd.hh24.mi.ss.FF'))",

Expected output :
Code:
2017-10-26 08.59.50.916000000 AM

Current Output:
Code:
2017-10-26 08.59.50.000000000 AM

Please advise

Moderator's Comments:
Mod Comment Please use CODE tags as required by forum rules!
Last edited by RudiC; 10-27-2017 at 12:16 PM.. Reason: Added CODE tags.
# 2  
Old 10-27-2017
Your input data does not match the format specified in your control file. Also convert sysdate-1 to timestamp:-

Code:
"decode(:SRC_SYS_CRT_TS, NULL, to_timestamp(sysdate-1), to_timestamp(:SRC_SYS_CRT_TS,'DD-MON-YY HH.MI.SS.FF AM'))")

---------- Post updated at 14:49 ---------- Previous update was at 12:40 ----------

Also note that NLS_TIMESTAMP_FORMAT initialization parameter determines the timestamp format when a character string is converted to the TIMESTAMP datatype.

TIMESTAMP datatype

So instead of converting, I would suggest simply specify the field is timestamp in the control file.
Code:
(SRC_SYS_CRT_TS TIMESTAMP 'DD-MON-YY HH.MI.SS.FF AM')

Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Milliseconds to Date in Sun OS 5.10

I am using Sun OS 5.10 I am Using nawk to extract specific column from csv file. The third column of csv is the time in Milliseconds and I need to convert it to Date then save it in another csv file. I am use this command to extract the columns I need and save it in tttn.csv nawk 'BEGIN... (6 Replies)
Discussion started by: Hozaifa
6 Replies

2. Shell Programming and Scripting

Calculate Time diff in milli milliseconds(Time format : HH:MM:SS,NNN)

Hi All, I have one file which contains time for request and response. I want to calculate time difference in milliseconds for each line. This file can contain 10K lines. Sample file with 4 lines. for first line. Request Time: 15:23:45,255 Response Time: 15:23:45,258 Time diff... (6 Replies)
Discussion started by: Raza Ali
6 Replies

3. Shell Programming and Scripting

How do I do a short delay (milliseconds) in a shell script?

I need to put a small delay into a shell script. I'm looking for something smaller than "sleep" - a second is way too long. I want to sleep something like 10 milliseconds. I've tried "usleep" and "nanosleep", but the script doesn't recognize them. I'm using the bash shell but I'm willing to... (9 Replies)
Discussion started by: harmlesscat
9 Replies

4. Solaris

Process execution time in milliseconds

Hey everyone, I'm coming from Linux where the top command gave me lots of process info (particularly CPU time in milliseconds) and I'm trying to find similar info in Solaris. So far I've looked at prstat and ps but neither give cpu time in milliseconds, both seem to have 1 second... (2 Replies)
Discussion started by: maniac_ie
2 Replies

5. Shell Programming and Scripting

Getting Time in MilliSeconds with Perl

I use something like this in perl to get the date and time: use Time::localtime; use Time::gmtime; $tm = gmtime; $time_str = sprintf "%04d-%02d-%02d %02d:%02d:%02d", $tm->year + 1900, $tm->mon + 1, $tm->mday, $tm->hour, $tm->min, $tm->sec; It gives me something like this: 2010-08-26... (1 Reply)
Discussion started by: lforum
1 Replies

6. Shell Programming and Scripting

Convert milliseconds to standard time

hello, I have the uptime of the server showing as upTime=2427742050 How do I convert it to standard time. Thanks Chiru (1 Reply)
Discussion started by: chiru_h
1 Replies

7. Shell Programming and Scripting

how to use sleep comand for milliseconds?

how to use sleep comand for milliseconds? (3 Replies)
Discussion started by: brkavi_in
3 Replies

8. Programming

C time in milliseconds function.

I need a c function which return the time in: hour min sec and mil sec I am writing on unix os. (3 Replies)
Discussion started by: kamil
3 Replies

9. UNIX for Dummies Questions & Answers

useing date or other time style utility to get milliseconds.

hello everyone. im sure someone has run into the problem of timestamping files and end up haveing 2 files with the same name thus over writeing one of them. In my application i am trying to get a timestamp w/ milliseconds but i am haveing no luck and finding an answer in the man pages. I know... (3 Replies)
Discussion started by: Optimus_P
3 Replies
Login or Register to Ask a Question