Home » SQL & PL/SQL » Client Tools » How to save time format to 24 hour while importing data from xls file
How to save time format to 24 hour while importing data from xls file [message #612645] Wed, 23 April 2014 02:08 Go to next message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
Im importing data from a xls to oracle table.during import operation the time gets inserted in 12 hour format
created date in xls file
03/07/2000 14:17:16

The way get inserted into oracle table
3/7/2000 2:17:16 PM


i want to be inserted as 03/07/2000 14:17:16

[Updated on: Wed, 23 April 2014 02:11]

Report message to a moderator

Re: How to save time format to 24 hour while importing data from xls file [message #612646 is a reply to message #612645] Wed, 23 April 2014 02:10 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Please use :

to_char(created_date,'DD/MM/YYYY HH24:MI:SS')
Re: How to save time format to 24 hour while importing data from xls file [message #612648 is a reply to message #612646] Wed, 23 April 2014 02:12 Go to previous messageGo to next message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
im directly loading data from xls file how can i use to_char(created_date,'DD/MM/YYYY HH24:MI:SS')
Re: How to save time format to 24 hour while importing data from xls file [message #612649 is a reply to message #612648] Wed, 23 April 2014 02:15 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
please change NLS_DATE_FORMAT Parameter for getting result in 24 hours format
Re: How to save time format to 24 hour while importing data from xls file [message #612650 is a reply to message #612649] Wed, 23 April 2014 02:16 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
please use :

alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS';

[Updated on: Wed, 23 April 2014 02:17]

Report message to a moderator

Re: How to save time format to 24 hour while importing data from xls file [message #612652 is a reply to message #612650] Wed, 23 April 2014 02:38 Go to previous messageGo to next message
cookiemonster
Messages: 10960
Registered: September 2008
Location: Rainy Manchester
Senior Member
Read this: http://edstevensdba.wordpress.com/category/nls_date_format/

Date's aren't stored in a particular format.
Re: How to save time format to 24 hour while importing data from xls file [message #612654 is a reply to message #612650] Wed, 23 April 2014 02:43 Go to previous messageGo to next message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
i altered nls_date_format as you said.still the date gets inserted as 3/7/2000 2:17:16 PM

select value from nls_session_parameters where parameter='NLS_DATE_FORMAT';

DD/MM/YYYY HH24:MI:SS
Re: How to save time format to 24 hour while importing data from xls file [message #612658 is a reply to message #612654] Wed, 23 April 2014 02:47 Go to previous messageGo to next message
cookiemonster
Messages: 10960
Registered: September 2008
Location: Rainy Manchester
Senior Member
what datatype is the column?
Re: How to save time format to 24 hour while importing data from xls file [message #612659 is a reply to message #612658] Wed, 23 April 2014 02:48 Go to previous messageGo to next message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
its in date datatype
Re: How to save time format to 24 hour while importing data from xls file [message #612661 is a reply to message #612654] Wed, 23 April 2014 02:49 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
hi,

After executing:


alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS'; 



Please check format for below query :


select  Sysdate
from    dual;



If you want change at system level then please use :


ALTER SYSTEM SET NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS' SCOPE=SPFILE;

please check :

select  sysdate
from    dual;

[Updated on: Wed, 23 April 2014 02:50]

Report message to a moderator

Re: How to save time format to 24 hour while importing data from xls file [message #612662 is a reply to message #612661] Wed, 23 April 2014 02:50 Go to previous messageGo to next message
cookiemonster
Messages: 10960
Registered: September 2008
Location: Rainy Manchester
Senior Member
What tool are you using to query the data?
Re: How to save time format to 24 hour while importing data from xls file [message #612663 is a reply to message #612662] Wed, 23 April 2014 02:50 Go to previous messageGo to next message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
toad 12.1
Re: How to save time format to 24 hour while importing data from xls file [message #612664 is a reply to message #612663] Wed, 23 April 2014 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 59080
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Bong!

Re: How to save time format to 24 hour while importing data from xls file [message #612666 is a reply to message #612664] Wed, 23 April 2014 02:55 Go to previous messageGo to next message
cookiemonster
Messages: 10960
Registered: September 2008
Location: Rainy Manchester
Senior Member
Toad, and a lot of other GUIs ignore NLS settings for displaying dates. Look in the preferences menu (or equivalent) for the date format settings.
Re: How to save time format to 24 hour while importing data from xls file [message #612671 is a reply to message #612666] Wed, 23 April 2014 03:21 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
hi,

I am able to see changed date format in TOAD also.


select value 
from   nls_session_parameters 
where  parameter='NLS_DATE_FORMAT';

---o/p ----DD-MON-RR


alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS';

select value 
from   nls_session_parameters 
where  parameter='NLS_DATE_FORMAT';

---o/p DD/MM/YYYY HH24:MI:SS

Re: How to save time format to 24 hour while importing data from xls file [message #612674 is a reply to message #612671] Wed, 23 April 2014 03:29 Go to previous messageGo to next message
cookiemonster
Messages: 10960
Registered: September 2008
Location: Rainy Manchester
Senior Member
And what do you think that proves? It certainly doesn't prove that toad pays any attention to that parameter when querying date columns.
Re: How to save time format to 24 hour while importing data from xls file [message #612676 is a reply to message #612674] Wed, 23 April 2014 04:28 Go to previous messageGo to next message
Littlefoot
Messages: 19602
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
TOAD allows you to set DATE display format under its Options - Data Grids - Data - Display (Date format & time format).
Re: How to save time format to 24 hour while importing data from xls file [message #612702 is a reply to message #612654] Wed, 23 April 2014 07:32 Go to previous message
EdStevens
Messages: 271
Registered: September 2013
Senior Member
ashwanth77 wrote on Wed, 23 April 2014 02:43
i altered nls_date_format as you said.still the date gets inserted as 3/7/2000 2:17:16 PM



Did you read the link cookiemonster gave you?

If the column is, as you asserted, defined as a DATE, then the date did NOT get inserted as "3/7/2000 2:17:16 PM". It got INSERTED in oracle's binary format for dates. What you are seeing is how that binary date is being formatted for presentation and that formatting is being done by the client process. Your task is to figure out how that client process determines how it will format and present a DATE.
Previous Topic: NUMBER datatype
Next Topic: ora-28040 no matching authentication protocol
Goto Forum:
  


Current Time: Tue Sep 16 05:16:27 CDT 2014

Total time taken to generate the page: 0.07645 seconds