This discussion is archived
12 Replies Latest reply: Dec 10, 2012 3:32 AM by Marwim RSS

Date Format

fame Explorer
Currently Being Moderated
Hi,


Date Format : *2012-10-03T10:02:26.5066744+05:30*

select sysdate from dual;

i want this format in the select statement.



Regards,

Fame
  • 1. Re: Date Format
    ranit B Expert
    Currently Being Moderated
    try playing with this... it has a 'TimeZone' element.
    select systimestamp from dual;
    gives
    12/9/2012 11:42:39.662046 PM -06:00
    Or this...
    WITH xx AS (
        SELECT to_char(sysdate,'yyyy-mm-dd') datex FROM DUAL
    )
    SELECT 
        datex
        ||'T'||
        to_char(sysdate,'hh24:mm:sssss')||extract(timezone_hour from systimestamp)
        ||':'||
        lpad(extract(timezone_minute from systimestamp),2,'0')
    FROM xx;  
    gives
    2012-12-09T23:12:85657-6:00
    Edited by: ranit B on Dec 10, 2012 11:12 AM
    -- added o/p

    Edited by: ranit B on Dec 10, 2012 11:17 AM
  • 2. Re: Date Format
    sb92075 Guru
    Currently Being Moderated
    fame wrote:
    Hi,


    Date Format : *2012-10-03T10:02:26.5066744+05:30*

    select sysdate from dual;

    i want this format in the select statement.
    use TO_CHAR() function to format the results as your desire
  • 3. Re: Date Format
    Purvesh K Guru
    Currently Being Moderated
    fame wrote:
    Hi,


    Date Format : *2012-10-03T10:02:26.5066744+05:30*

    select sysdate from dual;

    i want this format in the select statement.
    What you have provided in Date Format is actually a TIMESTAMP field, which is a Date in itself along with the Time (in Milliseconds) and Zone component in addition to Date Datatype.

    Since, Date datatype does not store Time in Milliseconds and the Time Zone information, you cannot get what you are asking for.

    Please read Date Datatype and TimeStamp datatype for more information.
  • 4. Re: Date Format
    fame Explorer
    Currently Being Moderated
    Hi,

    Here is my query

    SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS AM' ) FROM DUAL


    it will not return timezone.
    Why?


    Regrads
    Fame
  • 5. Re: Date Format
    Manik Expert
    Currently Being Moderated
    Try:
    SELECT TO_CHAR(systimestamp,'YYYY-MM-DD HH24:MI:SSXFF AM TZR' ) FROM DUAL;
    Cheers,
    Manik.
  • 6. Re: Date Format
    Ashu_Neo Pro
    Currently Being Moderated
    OP wrote :
    Date Format : 2012-10-03T10:02:26.5066744+05:30
    What you are asking to display sysdate in above format! But is not possible! I guess.
    As sysdate won't store fraction of sec and time zone values. For that, you can use current_timestamp or systimestamp.
    You can try upto :
    SQL> SELECT to_char(sysdate,'YYYY-MM-DD"T"HH24:MI:SS.FF') DD from dual;
    
    DD
    -------------------
    2012-12-10T12:27:21
    Thanks!
  • 7. Re: Date Format
    Purvesh K Guru
    Currently Being Moderated
    fame wrote:
    Hi,

    Here is my query

    SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS AM' ) FROM DUAL


    it will not return timezone.
    Why?
    You would have never asked this question if you would care about reading the responses to your questions carefully enough to visit the posted links.

    Since you are so un-caring, here are extracts from Oracle Documentation:-

    Date Datatype :-
    *"+The DATE data type stores date and time information. Although date and time information can be represented in both character and number data types, the DATE data type has special associated properties. For each DATE value, Oracle Database stores the following information: century, year, month, date, hour, minute, and second.+"*

    Timestamp Datatype :-
    +"The TIMESTAMP data type is an extension of the DATE data type. It stores year, month, day, hour, minute, and second values. It also stores fractional seconds, which are not stored by the DATE data type."+

    Timestamp with Timezone Datatype :-
    +"The TIMESTAMP data type is an extension of the DATE data type. It stores year, month, day, hour, minute, and second values. It also stores fractional seconds, which are not stored by the DATE data type."+
  • 8. Re: Date Format
    fame Explorer
    Currently Being Moderated
    hi,

    I dont want from timestamp, i want from sysdate only.
  • 9. Re: Date Format
    Marwim Expert
    Currently Being Moderated
    So you got the answer that systime does not contain the timezone
    I dont want from timestamp, i want from sysdate only.
    Just like my children. I tell them that somthing does not work the way they want and they still insist.

    Marcus
  • 10. Re: Date Format
    BluShadow Guru Moderator
    Currently Being Moderated
    fame wrote:
    hi,

    I dont want from timestamp, i want from sysdate only.
    What a stupid statement to make.

    If you want milliseconds and you want timezone information, you need a TIMESTAMP datatype. The DATE datatype (which is what SYSDATE is) DOES NOT contain that additional information. So you can want it all you like.... you're not going to get it.
  • 11. Re: Date Format
    Marwim Expert
    Currently Being Moderated
    The DATE datatype (which is what SYSDATE is) DOES NOT contain that additional information. So you can want it all you like.... you're not going to get it.
    Technically correct, but...
    SELECT TO_CHAR(CAST(SYSDATE AS TIMESTAMP WITH TIME ZONE),'YYYY-MM-DD HH24:MI:SSXFF TZR' )  "Sysdate with Timezone"
    FROM dual;
    
    Sysdate with Timezone                                        
    --------------------------------------------------------------
    2012-12-10 10:11:06.000000 EUROPE/BERLIN
    You can use sysdate. When you cast it as timestamp with timezone it will take the timezone from systimestamp and display it. In your query you use only sysdate :-)

    Interestingly the same format used in TO_CHAR for the real systimestamp gives another output
    SELECT TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SSXFF TZR' )  "Systimestamp with Timezone"
    FROM dual;
    
    Systimestamp with Timezone                                   
    --------------------------------------------------------------
    2012-12-10 10:12:21.180205 +01:00 
    Regards
    Marcus

    Edited by: Marwim on 10.12.2012 10:13
  • 12. Re: Date Format
    Marwim Expert
    Currently Being Moderated
    I dont want from timestamp, i want from sysdate only.
    What a stupid statement to make.
    That's why he marked "extract(timezone_hour from systimestamp)" as correct ;-)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points