Home » SQL & PL/SQL » SQL & PL/SQL » change date format (oracle 9.2.0.7.0)
change date format [message #443932] Thu, 18 February 2010 00:55 Go to next message
chuikingman
Messages: 90
Registered: August 2009
Member
Hi,
I have below data
SQL> desc IMEI
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MSISDN                                    NOT NULL VARCHAR2(20)
 IMEI                                      NOT NULL VARCHAR2(16)
 DATE_MOD                                           NUMBER(13)
 IMSI                                               VARCHAR2(18)
 ICCID                                              VARCHAR2(20)
 T_PROF                                             RAW(20)
 EXTRA_DATA                                         VARCHAR2(100)

SQL> select DATE_MOD from IMEI;

  DATE_MOD
----------
1.2199E+12
1.2348E+12
1.2278E+12
1.2263E+12
1.2278E+12
1.2378E+12
1.2414E+12


How can I change date_mod to normal date format like year/month/day hour:minutes:second ??
Please advice ??
Re: change date format [message #443933 is a reply to message #443932] Thu, 18 February 2010 01:00 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
>How can I change date_mod to normal date format like year/month/day hour:minutes:second ??

I give up.
How does NUMBER convert to DATE?
Re: change date format [message #443936 is a reply to message #443932] Thu, 18 February 2010 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe the following is what you want: Convert Unix time

Regards
Michel
Re: change date format [message #443942 is a reply to message #443936] Thu, 18 February 2010 01:54 Go to previous messageGo to next message
chuikingman
Messages: 90
Registered: August 2009
Member
The format is not correct ...
I cannot get correct date and time
Please advice ....

SQL> CREATE OR REPLACE FUNCTION convert_unix_time (
  2         p_secs_since_epoch  IN  NUMBER
  3      )
  4      RETURN DATE
  5      IS
  6          l_date                  DATE;
  7      BEGIN
  8          l_date := TO_DATE('19700101','YYYYMMDD')
  9                    +
 10                   p_secs_since_epoch / 60 / 60 / 24;
       RETURN (l_date);
 11   12     END convert_unix_time;
 13  /

Function created.

SQL> SELECT TO_CHAR(convert_unix_time(1.2199E+12)
    ,              'MM-DD-YYYY HH24:MI:SS') my_time
    FROM   DUAL
    /  2    3    4
  5  ;
    FROM   DUAL
       *
ERROR at line 3:
ORA-00933: SQL command not properly ended


SQL> SELECT TO_CHAR(convert_unix_time(1.2199E+12),'MM-DD-YYYY HH24:MI:SS') my_time FROM   DUAL;

MY_TIME
---------------------------------------------------------------------------
02-03-0691 23:06:40

SQL> SELECT TO_CHAR(convert_unix_time(1.2199E+12),'YYYY-MM-DD HH24:MI:SS') my_time FROM   DUAL;

MY_TIME
---------------------------------------------------------------------------
0691-02-03 23:06:40
Re: change date format [message #443943 is a reply to message #443942] Thu, 18 February 2010 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to give ALL decimals not just part of them.

set numwidth 30

Regards
Michel
Re: change date format [message #443947 is a reply to message #443943] Thu, 18 February 2010 02:09 Go to previous messageGo to next message
chuikingman
Messages: 90
Registered: August 2009
Member
HI,
I set numwidth to 30 and try again .
The date format is not correct ...
any advice and comment again ???
SQL> CREATE OR REPLACE FUNCTION convert_unix_time (
  2          p_secs_since_epoch  IN  NUMBER
    )
    RETURN DATE
    IS
  3    4    5    6          l_date                  DATE;
  7      BEGIN
  8          l_date := TO_DATE('19700101','YYYYMMDD')
                  +
                 p_secs_since_epoch / 60 / 60 / 24;
  9   10   11         RETURN (l_date);
   END convert_unix_time; 12
 13  /

Function created.

SQL> SELECT TO_CHAR(convert_unix_time(1219892382878),'MM-DD-YYYY HH24:MI:SS') my_time FROM   DUAL;

MY_TIME
---------------------------------------------------------------------------
11-07-0690 19:14:38

SQL> SELECT TO_CHAR(convert_unix_time(1219892382878),'YYYY-MM-DD HH24:MI:SS') my_time FROM   DUAL;

MY_TIME
---------------------------------------------------------------------------
0690-11-07 19:14:38
Re: change date format [message #443948 is a reply to message #443947] Thu, 18 February 2010 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe your data is not the one using by the function.
You FIRST have to answer to BlackSwan's question:
Quote:
How does NUMBER convert to DATE?

In other words what does your number represent?

Regards
Michel
Re: change date format [message #443955 is a reply to message #443948] Thu, 18 February 2010 02:42 Go to previous messageGo to next message
chuikingman
Messages: 90
Registered: August 2009
Member
Michel Cadot wrote on Thu, 18 February 2010 16:11
Maybe your data is not the one using by the function.
You FIRST have to answer to BlackSwan's question:
Quote:
How does NUMBER convert to DATE?

In other words what does your number represent?

Regards
Michel

I have below function to convert data
But it cannot show the hour:minutes:second
Please advice how I can fix it

BELOW is the function
SQL> var delta_timezone number;
SQL> exec select SUBSTR(sessiontimezone,1,1)||SUBSTR(sessiontimezone,2,2)*3600000 into:delta_timezone from dual;

PL/SQL procedure successfully completed.

SQL> select to_date('01-JAN-1970','DD-MON-YYYY')+(DATE_MOD+:delta_timezone)/(24*60*60*1000)from IMEI;

TO_DATE('
---------
28-AUG-08
16-FEB-09
27-NOV-08
10-NOV-08
27-NOV-08
23-MAR-09
04-MAY-09

SQL> set numwidth 30;
SQL> select DATE_MOD from IMEI;

                      DATE_MOD
------------------------------
                 1219892382878
                 1234761609715
                 1227758953376
                 1226306592171
                 1227759723947
                 1237777913082
                 1241405690423




Re: change date format [message #443957 is a reply to message #443948] Thu, 18 February 2010 02:46 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
i guess the question would be how the date converted to number?

sriram Smile

say like we have to Functions.
1)Totime----Converts date to time.
2)Todate----Converts the Number returned By the above function into date(with fractional seconds.
ind> select to_char(sysdate,'dd-mon-yy:hh12:mi:ss:am'),totime(sysdate) from dual;

TO_CHAR(SYSDATE,'DD-M TOTIME(SYSDATE)
--------------------- ---------------
18-feb-10:02:17:49:pm      1266531469

1 row selected.

ind> select to_char(todate(1266531469),'dd-mon-yy:hh12:mi:ss:am') from dual;

TO_CHAR(TODATE(126653
---------------------
18-feb-10:02:17:49:pm

1 row selected.

ind> 



So
@ OP if you provide the information how your date converted into that number we will help you.

Opps MY network problem

[Updated on: Thu, 18 February 2010 02:48]

Report message to a moderator

Re: change date format [message #443959 is a reply to message #443957] Thu, 18 February 2010 02:50 Go to previous messageGo to next message
chuikingman
Messages: 90
Registered: August 2009
Member
Hi,
I only have the previous post I provide .
I have no idea how to convert ...
Could you understand from the function I provide ???
Please advice ..

SQL> var delta_timezone number;
SQL> exec select SUBSTR(sessiontimezone,1,1)||SUBSTR(sessiontimezone,2,2)*3600000 into:delta_timezone from dual;

PL/SQL procedure successfully completed.

SQL> select to_date('01-JAN-1970','DD-MON-YYYY')+(DATE_MOD+:delta_timezone)/(24*60*60*1000)from IMEI;

TO_DATE('
---------
28-AUG-08
16-FEB-09
27-NOV-08
10-NOV-08
27-NOV-08
23-MAR-09
04-MAY-09

SQL> set numwidth 30;
SQL> select DATE_MOD from IMEI;

                      DATE_MOD
------------------------------
                 1219892382878
                 1234761609715
                 1227758953376
                 1226306592171
                 1227759723947
                 1237777913082
                 1241405690423
Re: change date format [message #443962 is a reply to message #443955] Thu, 18 February 2010 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But it cannot show the hour:minutes:second

Just use TO_CHAR function to specify the format you want the datetime to be displayed.

Regards
Michel
Re: change date format [message #443963 is a reply to message #443962] Thu, 18 February 2010 02:56 Go to previous messageGo to next message
chuikingman
Messages: 90
Registered: August 2009
Member
I have no idea about to_char function.
Could you specify in more detailed ...
Please advice ..
Could you take example ???
Re: change date format [message #443964 is a reply to message #443959] Thu, 18 February 2010 02:57 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
ind> var delta_timezone number;
ind> select SUBSTR(sessiontimezone,1,1)||SUBSTR(sessiontimezone,2,2)*3600000 from dual;

SUBSTR(SESSIONTIMEZONE,1,1)||SUBSTR(SESSI
-----------------------------------------
+18000000

1 row selected.

ind> select to_date('01-JAN-1970','DD-MON-YYYY')+(1219892382878+(SUBSTR(sessiontimezone,1,1)||SUBSTR
(sessiontimezone,2,2)*3600000))/(24*60*60*1000) from dual;

TO_DATE('
---------
28-AUG-08

1 row selected.

ind> ed
Wrote file afiedt.buf

  1* select to_char(to_date('01-JAN-1970','DD-MON-YYYY')+(1219892382878+(SUBSTR(sessiontimezone,1,1)
ind> /

TO_CHAR(TO_DATE('01-J
---------------------
28-aug-08-07:59:43:Am

1 row selected.

ind> ed
Wrote file afiedt.buf

  1  select to_char(to_date('01-JAN-1970','DD-MON-YYYY')+
  2  (1219892382878+(SUBSTR(sessiontimezone,1,1)||
  3* SUBSTR(sessiontimezone,2,2)*3600000))/(24*60*60*1000),'dd-mon-yy-hh12:mi:ss:Am') from dual
ind> /

TO_CHAR(TO_DATE('01-J
---------------------
28-aug-08-07:59:43:Am

1 row selected.

ind> 


sriram Smile
Re: change date format [message #443965 is a reply to message #443963] Thu, 18 February 2010 03:00 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
chuikingman wrote on Thu, 18 February 2010 09:56
I have no idea about to_char function.
Could you specify in more detailed ...
Please advice ..
Could you take example ???

Maybe you could have a look at Database SQL Reference before posting such RTFM question.

Regards
Michel

Re: change date format [message #443968 is a reply to message #443964] Thu, 18 February 2010 03:07 Go to previous messageGo to next message
chuikingman
Messages: 90
Registered: August 2009
Member
Could you advice what is wrong for below sql command ??
How to fix it to show the correct hour:minutes:second ???

SQL> set numwidth 30
SQL> select to_char(to_date('01-JAN-1970','DD-MON-YYYY')+(DATE_MOD+:delta_timezone)/(24*60*60*1000))from IMEI;

TO_CHAR(TO_DATE
---------------
28-AUG-08
16-FEB-09
27-NOV-08
10-NOV-08
27-NOV-08
23-MAR-09
04-MAY-09

7 rows selected.

Re: change date format [message #443969 is a reply to message #443968] Thu, 18 February 2010 03:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
THe format to show Hours, minutes and seconds is:
select to_char(to_date('01-JAN-1970','DD-MON-YYYY')+(DATE_MOD+:delta_timezone)/(24*60*60*1000),'dd-mon-yyyy hh24:mi:ss')from IMEI;


Re: change date format [message #443971 is a reply to message #443968] Thu, 18 February 2010 03:15 Go to previous message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Could you advice what is wrong for below sql command ??
How to fix it to show the correct hour:minutes:second ???

What about reading A LITTLE BIT the documentation?

Regards
Michel
Previous Topic: Improving performance of a VIEW
Next Topic: SAVEPOINT (merged 3)
Goto Forum:
  


Current Time: Sat Sep 24 23:10:29 CDT 2016

Total time taken to generate the page: 0.07596 seconds