Datetime problem, please help...

[hokky]

Hi guys,

I've got a real problem, but I think I just need simple solution.

in my desc table in oracle database, I checked via linux, it looks like this :

date_stored datetime(6)

and the record in "date_stored" column becomes :

20-Jun-14

Actually the data before we put into the table is 20-06-2014,
I was just wondering if I alter the table description into datetime(8), will it solve the whole record in that column or I have to truncate the data and restore into the table back ?

Is there anyone can offer me solution please ?

Thanks guys,

 

[feherke]

Hi

Are you sure there is a problem ? To me looks like a formatting difference :

[blue]SQL>[/blue] [b]select[/b] sysdate,to_char(sysdate,[i]'dd-mm-yyyy'[/i]) [b]from[/b] dual;

SYSDATE   TO_CHAR(SY
--------- ----------
06-JUN-07 06-06-2007

Feherke.

http://rootshell.be/~feherke/

 

[SantaMufasa]

in my desc table in oracle database, I checked via linux, it looks like this

date_stored datetime(6)

Please pardon my skepticism, Hokky, but I just don't "buy" the contents of your describe.


To assuage my concern, could you please do a screen copy-and-paste of the DESCRIBE of your table, from the DESCRIBE verb at least down through the column in question?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[hokky]

Here it is more explanation :

SQL> desc ELECTRICITY_MWH_BY_MONTH;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 MWHCATEGORY                               NOT NULL VARCHAR2(32)
 PERIOD                                    NOT NULL TIMESTAMP(6)
 PEAKLOAD                                  NOT NULL NUMBER(32,4)
 OFFPEAKLOAD                               NOT NULL NUMBER(32,4)
 BASELOAD                                  NOT NULL NUMBER(32,4)

        ID MWHCATEGORY
---------- --------------------------------
PERIOD
---------------------------------------------------------------------------
  PEAKLOAD OFFPEAKLOAD   BASELOAD
---------- ----------- ----------

      4058 Holland
01-NOV-17 12.00.00.000000 AM
       352         368        720

but in Toad application,
The PERIOD column showing this : "1/11/2017 12:00:00.000000 AM"

which is correct

 

[SantaMufasa]

Ah, now that DESCRIBE I'll buy.

Now, to your specific issues...I have created a table with four columns: one DATE column and three TIMESTAMP columns with different precision indicators:

create table hokky (a date, b timestamp, c timestamp(6), d timestamp(9));

Table created.

insert into hokky values (sysdate,systimestamp,systimestamp,systimestamp);

1 row created.

select * from hokky;

A
---------
B
--------------------------------
C
--------------------------------
D
--------------------------------
06-JUN-07
06-JUN-07 06.26.42.893000 PM
06-JUN-07 06.26.42.893000 PM
06-JUN-07 06.26.42.893000000 PM

Notice that our increasing the precision (from the default precision of "(6)") for a "timesamp column simply increases the number of digits behind the decimal of fractional seconds. Notice also that the greatest precision that Oracle and the system even store are thousandths of seconds (i.e., timestamp(3)).


DATE columns consume 7 bytes; TIMESTAMP columns consume 11 bytes.

Regardless of the internal representation you choose (i.e., DATE or TIMESTAMP), the results you display from either format depend upon the format mask that you choose:

select to_char(a,'fmDay, Month Ddspth, yyyy')a
      ,to_char(b,'fmDay, Month Ddspth, yyyy')b
      ,to_char(c,'fmDay, Month Ddspth, yyyy')c
      ,to_char(d,'fmDay, Month Ddspth, yyyy')d
from hokky
/

A
-------------------------------
B
-------------------------------
C
-------------------------------
D
-------------------------------
Wednesday, June Sixth, 2007
Wednesday, June Sixth, 2007
Wednesday, June Sixth, 2007
Wednesday, June Sixth, 2007

So, don't let the external appearance of DATE or TIMESTAMP columns fool you into thinking that the internal representation mimics output format masks.

Let us know if you have questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[hokky]

Alrite Mufasa,

Thanks for your explanation.

Now the question is how do I make the default timestamp(6) looks like

06-11-2007 06.26.42.893000 PM

instead of

06-JUN-07 06.26.42.893000 PM

we have a problem if they store the year only yy, because from this table we're going to export into a file and from that file we are going to bcp into sql server and the big problem in sql because as dispay is dd-mm-yy, every year with 50 is going to be interpreted as 1950 instead of 2050.

because of this

Under Two digit year support, in the When a two-digit year is entered, interpret it as a year between box, type or select a value that is the ending year of the time span. 
The default time span for Microsoft® SQL Server™ is 1950-2049, which represents a cutoff year of 2049. This means that SQL Server interprets a two-digit year of 49 as 2049, a two-digit year of 50 as 1950, and a two-digit year of 99 as 1999. To maintain backward compatibility, leave the setting at the default value.

Now, The question we need to get the default view as dd-mm-yyyy, is there any way of doing it other than change the init.ora file?

Please let me know

Thanks

 

[SantaMufasa]

Hokky,

Again, the solution is, as I said before, the date format mask. Here is code that creates the format you want. (Remember, "c" is the name of the TIMESTAMP column in the HOKKY table.):

select to_char(c,'mm-dd-yyyy hh.mm.ss.ff PM') from hokky;

DATETIME
-----------------------------
06-06-2007 06.06.42.893000 PM

Let us know if this resolves what you need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[hokky]

no,

the problem is we grep the data straight from the table using perl code.
In perl code we fetch the data row by row, which means it's going to be dd-mm-yy because that's how it looks like.
Then dump into a file.

So you reckon I should change in perl code first before fetch the data from table ?

here's the perl code to grep the data

                print "Dumping [$table] ...\n";
                print "    Rows: $row_count\n";
                print "    File: $output_file\n";

                my $dump_ok = 1;
                my $rows_dumped = dumpTableData($db,$table,$output_file);
                $dump_ok = 0 if ($rows_dumped < 0);
                $dump_ok = 0 if ($rows_dumped != $row_count);
                $dump_ok = 0 if (`wc -l "$output_file"` != $row_count);

                print "    ".$codes[$dump_ok]."\n";

AS you can see, they just dump the table to a file.
CAn you help me ? or I should go to perl forum about this

 

[Turkbear]

Hi,
It is not the grep but the fetch ( for want of a better word) that matters..In your code to select rows from the table, use the formatting code Santa provided..






To Paraphrase:"The Help you get is proportional to the Help you give.."