OraFAQ Forum: Client Tools » How to save time format to 24 hour while importing data from xls file

Home » Other » Client Tools » How to save time format to 24 hour while importing data from xls file

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to save time format to 24 hour while importing data from xls file [message #612645]

Wed, 23 April 2014 02:08

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

msol25
Messages: 396
Registered: June 2011

Senior Member

Please use :

to_char(created_date,'DD/MM/YYYY HH24:MI:SS')

Report message to a moderator

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

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')

Report message to a moderator

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

msol25
Messages: 396
Registered: June 2011

Senior Member

please change NLS_DATE_FORMAT Parameter for getting result in 24 hours format

Report message to a moderator

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

msol25
Messages: 396
Registered: June 2011

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

cookiemonster
Messages: 13961
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.

Report message to a moderator

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

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

Report message to a moderator

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

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

what datatype is the column?

Report message to a moderator

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

ashwanth77
Messages: 95
Registered: April 2013
Location: India

Member

its in date datatype

Report message to a moderator

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

msol25
Messages: 396
Registered: June 2011

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

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

What tool are you using to query the data?

Report message to a moderator

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

ashwanth77
Messages: 95
Registered: April 2013
Location: India

Member

toad 12.1

Report message to a moderator

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

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Bong!

Report message to a moderator

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

cookiemonster
Messages: 13961
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.

Report message to a moderator

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

msol25
Messages: 396
Registered: June 2011

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

Report message to a moderator

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

cookiemonster
Messages: 13961
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.

Report message to a moderator

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

Littlefoot
Messages: 21823
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).

Report message to a moderator

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

EdStevens
Messages: 1376
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.

Report message to a moderator

Previous Topic:	NUMBER datatype
Next Topic:	ER Diagram to pdf

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 02 15:24:10 CST 2025