11

I want to select a date field from oracle database, using select to_date(trndt, 'yyyymmdd') from cf20gldt, but the system was showing an error.

Florin Ghita
  • 17,525
  • 6
  • 57
  • 76
user2786306
  • 221
  • 2
  • 6
  • 12
  • 6
    `to_date()` converts a string to a *date*. You are looking for `to_char()` which converts a date to a string in a specified format (btw: **always** include the actual error message, we can not see your screen from over here) –  Oct 28 '13 at 07:44

3 Answers3

15

Reference

Change it as to_char

SELECT TO_CHAR(trndt, 'yyyymmdd') FROM cf20gldt;

if your trndt column is not date type then you have to change it as

SELECT TO_CHAR(TO_DATE(trndt), 'yyyymmdd') FROM cf20gldt;

For example display system date

SELECT TO_CHAR(sysdate,'yyyymmdd') FROM dual;
Naveen Kumar Alone
  • 7,536
  • 5
  • 36
  • 57
  • 2
    Your answer is correct so far, but you should never use to_date without specifying a format. Omitting the format makes your select statement dependent on the current regional settings, which may or may not match the format present in the date string. – Thorsten Kettner Oct 28 '13 at 08:06
  • @Naveen : what to do when i have to use "select * " in my query and need date in DD/MM/YYYY format – user3782114 Jun 29 '16 at 07:31
  • 1
    @user3782114, to do that you would use the following: `ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY';`. But don't use `SELECT *`. – David Faber Apr 05 '17 at 13:52
  • @user3782114 in realtime applcation development, select * from is not recommended to get the data from DB. Need to mention what are the columns you need. – Naveen Kumar Alone Apr 06 '17 at 11:31
2

To_date is to convert a column to date format . If your trndt is not date already then use below :

select to_char(to_date(trndt), 'yyyymmdd') from cf20gldt;

Or if your column trndate is already in date format then use below:-

select to_char(trndt, 'yyyymmdd') from cf20gldt;
user2342436
  • 492
  • 3
  • 17
  • 3
    Correct, but don't use to_date without specifying a format. See my corresponding comment on Naveen's answer. – Thorsten Kettner Oct 28 '13 at 08:10
  • I wrote to_date without any format so that it takes the database format and then use to_char for the required format (here yyyymmdd). – user2342436 Oct 28 '13 at 08:18
  • Yes, I understand this. But in case a date is stored in a string, it is stored in a certain known format. Name that format rather than have the dbms guess. A British user may have his/her setting to DD/MM/YYYY and his American collegue to MM/DD/YYYY. They do that, because they want to have dates displayed in the way they are used to (American or British). That must by no means affect the interpretation of the date stored as a varchar2 in the database. – Thorsten Kettner Oct 28 '13 at 09:15
2

You could use: 

SELECT to_char(sysdate, 'yyyy-mm-dd') date,
        to_char(sysdate, 'hh24:mm:ss') hr
        FROM DUAL;

Change sysdate by your field type Date.

Paul Giancarlo Diaz
  • 41
  • 5