Solved

How to insert specific format like "Mon Jan 06 12:05:26 GMT 2014" while inserting in to time stamp column type in oracle.

Posted on 2014-01-07
3
609 Views
Last Modified: 2014-01-08
How to insert specific format like "Mon Jan 06 12:05:26 GMT 2014" while inserting in to time stamp column type in oracle. we have migartion of data from other db which has the format of date column like "Mon Jan 06 12:05:26 GMT 2014". in order to avoid two different types of dates. we want to keep the datatype in oracle also like this format "Mon Jan 06 12:05:26 GMT 2014"
0
Comment
Question by:ajaybelde
3 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 39762747
Dates and timestamps in Oracle do not have a format per say.  They are stored in an internal format that is described in the docs.

They only have a format when they are displayed.  This is driven by a default format then be NLS_DATE_FORMAT (for DATE.  TIMESTAMP has a similar one) and last by TO_CHAR with a format mask.



All that said, what are you wanting help with?

Inserting from one database into Oracle?
How the application should insert into an Oracle column?
How the application should convert the timestamp into a string for display?

Also confirm you are talking about the timestamp data type and not the date data type.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 39762773
to convert your string into a timestamp with time zone type use the TO_TIMESTAMP_TZ function and include the proper format

to_timestamp_tz('Mon Jan 06 12:05:26 GMT 2014','Dy Mon dd hh24:mi:ss TZR yyyy')


as noted previously though,  the text format of the string has nothing to do with how the data is stored in the database.

so, similarly to extract the text representation of your timestamp values use TO_CHAR


to_char(your_column,'Dy Mon dd hh24:mi:ss TZR yyyy')
0
 

Author Closing Comment

by:ajaybelde
ID: 39765802
by changing the parameter  set NLS_TIMESTAMP_TZ_FORMAT='Day MON DD HH24.MI.SSXFF TZR RRRR' it allowed me the way i want to insert
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
form builder not starting 3 55
best datatype for oracle table email creation 8 55
Oracle 12c Default Isolation Level 17 41
Migration from sql server to oracle 5 24
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

856 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