CODEWITHSUNDEEP
databaseoracle-database
Shahid Ghafoor
Shahid Ghafoor

Reputation: 3103

date format remain same in oracle database

INSERT INTO DELLL (
DATETIMEMY) 
SELECT    to_date(to_char(SU_MODIFYDATE, 'YYYY/MM/DD'),'YYYY/MM/DD') AS DATETIMEMY
FROM SER_TBLSERVICES WHERE SVE_SERVICEID=422

SU_MODIFYDATE = 01/18/2013

but after insertion in DELLL date (DATETIMEMY) format still same is still same

DATETIMEMY = 01/18/2013

Upvotes: 0

Views: 185

Answers (3)

Rachcha
Rachcha

Reputation: 8816

DazzaL is right with his answer which discusses formatting, storage and retrieval of dates.

I would like to discuss what you are doing in your code.

Oracle stores dates upto precision of a seconds. In simple terms you can retrieve the date in MM/DD/YYYY HH24:MI:SS format.

By issuing to_date(to_char(SU_MODIFYDATE, 'YYYY/MM/DD'),'YYYY/MM/DD') you are actually truncating the date up to the day part. So when you try to retrieve this value, the HH24:MI:SS part will have 00:00:00 because you have truncated the date.

If you want all the details (from year, month, day to hour, minute and seconds) from SU_MODIFYDATE to be inserted into the column in DELLL, you should just use

INSERT INTO DELLL (
DATETIMEMY) 
SELECT SU_MODIFYDATE AS DATETIMEMY
FROM SER_TBLSERVICES WHERE SVE_SERVICEID=422

This will ensure all parts of date in SU_MODIFYDATE column are inserted into the new column.

If you want to truncate the date, for example, upto the minute the use to_date(to_char(SU_MODIFYDATE, 'YYYY/MM/DD HH24:MI'),'YYYY/MM/DD HH24:MI')

Likewise, you can truncate dates from year part upto the second part as per your choice.

If you want to insert truncated dates then you should use the query you already have. If you want to insert dates with all their parts then avoid truncating by using to_char and to_date.

Upvotes: 0

DazzaL
DazzaL

Reputation: 21973

you dont assign a format to a DATE. They are stored internally as a number and have no format. the format comes when you want to select the date. so in your case you should just do this:

first just insert the date as-is, do not try to convert it to a char and back again:

SQL> INSERT INTO DELLL (
  2  DATETIMEMY) SELECT SU_MODIFYDATE
  3  FROM SER_TBLSERVICES WHERE SVE_SERVICEID=422;

1 row created.

now to select it in a specific format, you can use TO_CHAR to format it.

SQL> select * from delll;

DATETIMEMY
----------
01/18/2013 <-- which isn't what you want to see, you wanted to see yyyy/mmm/dd. so...

SQL> select to_char(DATETIMEMY,'yyyy/mm/dd') DATETIMEMY from DELLL
  2  /

DATETIMEMY
----------
2013/01/18

or, to apply to all selects in that session that have a date datatype, you can alter the default display format:

SQL> alter session set nls_date_format='yyyy/mm/dd';

Session altered.

SQL> select * from delll;

DATETIMEMY
----------
2013/01/18

Upvotes: 2

istepaniuk
istepaniuk

Reputation: 4271

The INSERT or SELECT in your example does not modify how a date is stored. Dates are formatted when you SELECT them, using your current connection format preferences/locale.

You are using TO_DATE, selecting that returned value is the same as with any other date, it will be formatted according to the locale.

Upvotes: 0

Related Questions