12 Replies Latest reply: Dec 10, 2012 5:32 AM by Marwim RSS

    Date Format

    fame
      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
          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
            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
              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
                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
                  Try:
                  SELECT TO_CHAR(systimestamp,'YYYY-MM-DD HH24:MI:SSXFF AM TZR' ) FROM DUAL;
                  Cheers,
                  Manik.
                  • 6. Re: Date Format
                    Ashu_Neo
                    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
                      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
                        hi,

                        I dont want from timestamp, i want from sysdate only.
                        • 9. Re: Date Format
                          Marwim
                          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
                            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
                              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
                                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 ;-)