Toad World® Forums

Number to date DDMMYYYY


#1

Hi
I have a number but I need the format DDMMYYYY Could you help me Please?

I_am_number
20181230
20181129
20181028

select to_date(to_char(I_am_number), ‘YYYYMMDD’ ) from tbl_number;
select to_char( sysdate, ‘DDMMYYYY’) from dual; – OK
select to_char(to_date(to_char(I_am_number), ‘YYYYMMDD’ ), ‘DDMMYYYY’) from tbl_number; – Is it correct?
data grid
12/30/2018
11/29/2018
10/28/2018

But I need
30/12/2018
29/11/2018
28/10/2018

Thanks a lot


#2

I’m away from my databases right now, but what you need is to convert the number, which appears to be in yyyymmdd format to a date, then convert that back to character format in dd/mm/yyyy format. I suspect:

Select to_char(to_date(i_am_number, ‘yyyymmdd’), ‘dd/mm/yyyy’) from tbl_number;

That will work, and you can test it in sql*plus to be sure, however, your problem might be how you have configured Toad’s grid to display dates. So, View->Toad Options, then on the left, grid, and have a look see.

Happy Christmas.

By the way, always store dates as dates. Trust me on that matter. If you do nothing else, make sure you store data in the correct format. :wink:

Cheers,
Norm. [TeamT]


#3

Hello
Some time ago there were people who defined the dates as numbers, the column or field is already defined as a number. Now I need the format DD / MM / YYYY dates.
Thanks a lot


#4

Morning Antoine_mc1,

My previous rep,y showed how to do it. Convert from a number in yyyymmdd format to a date with to_date, then convert that back to a char in dd/mm/yyyy format with to_char.

That’s gor fisplay if vourse, if you need to do some calculations, just convert the number to a date as described, and do what needs foing with dstes, in date data types.

HTH

Cheers,
Norm[TeamT]


#5

Sorry, the previous reply got a little garbled!

The final paragraph should start with “That’s for display of course…” and finish with “if you need to do some calculations, just convert the number to a date as described, and do what needs doing with dates, in date data types.”

Sigh.

Cheers,
Norm. [TeamTypo]