SQL> select to_char(systimestamp,'YYYY-MM-DD"T"hh24:mi:sstzh:tzm') isodt from dual;
ISODT
-------------------------
2010-03-25T08:03:40+01:00

Regards
Michel

To insert you use TO_TIMESTAMP with the same format.
You cannot use a DATE datatype but have to use one of the TIMSTAMP% ones as DATE does not know nor support time zone.
You can nevertheless use DATE if you assume/convert all your date/time into DB time zone and use DBTIMEZONE function to retrieve the date:

SQL> select to_char(sysdate,'YYYY-MM-DD"T"hh24:mi:ss')||dbtimezone osidt from dual;
OSIDT
-------------------------
2010-03-25T08:03:30+01:00

---

Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand

My senario is like
i need to insert and retrive the date feilds in
ISO 8601 compliant standard ie..YYYY-MM-DDThh:mm:ss±hh:mm.

Hi Still i am unable to follow the INSERT way.. can you please give me the syantax or example.

Or rather:
insert into t values
(from_tz(to_timestamp('2010-03-25T08:03:30','YYYY-MM-DD"T"hh24:mi:ss'),'+01:00'));

Now it depends on what is the exact stuff you get and in which exact table you want to store.

---

Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand

For inserting the Such date type what should be the datatype we need to use? TIMESTAMP WITH TIME ZONE?????

Quote:

For inserting the Such date type what should be the datatype we need to use? TIMESTAMP WITH TIME ZONE?????

Yes.

Regards
Michel

To store a timezone, no.
To do what you want, it depends on your environment, I told you about alternative ways in a previous post.

Note: a lone interrogation mark is sufficient to indicate this is a question, you don't need to multiply them (unless you want to make a breeding).

Regards
Michel

Michel Cadot wrote on Thu, 25 March 2010 05:25

To store a timezone, no.
To do what you want, it depends on your environment, I told you about alternative ways in a previous post.

Note: a lone interrogation mark is sufficient to indicate this is a question, you don't need to multiply them (unless you want to make a breeding).

Regards
Michel

thanks michel, for the responce

create or replace
function CONVERT_TimeZone (dt in timestamp, DestinationTimeZone in varchar2, SourceTimeZone in varchar2)
return timestamp with time zone
as
	retval timestamp with time zone
begin	

	retval := from_tz(dt, timezone1) at time zone SourceTimeZone;
	return retval;
end;

select CONVERT_TimeZone(to_timestamp('03/18/2010 15:24','mm/dd/yyyy hh24:mi'),'-5:00','+9:30') from dual ;

Output: 3/19/2010 5:54:00 AM

DELETED AT OP'S DEMAND

Function:

create or replace
function CONVERT_TimeZone (dt in timestamp, DestinationTimeZone in varchar2, SourceTimeZone in varchar2)
return timestamp with time zone
as
	retval timestamp with time zone
begin	

	retval := from_tz(dt, timezone1) at time zone SourceTimeZone;
	return retval;
end;

Function will return null if there's exception.

mjkreddy1982 wrote on Fri, 26 March 2010 10:43

Function:

create or replace
function CONVERT_TimeZone (dt in timestamp, DestinationTimeZone in varchar2, SourceTimeZone in varchar2)
return timestamp with time zone
as
	retval timestamp with time zone
begin	

	retval := from_tz(dt, timezone1) at time zone SourceTimeZone;
	return retval;
end;

Function will return null if there's exception.

Not as you've got it written there it won't, you'd need to add an exception handler.