Home » SQL & PL/SQL » SQL & PL/SQL » Want to select date from timestamp
Want to select date from timestamp [message #213731] Fri, 12 January 2007 01:37 Go to next message
amit_nanote
Messages: 56
Registered: July 2005
Location: Indore
Member

Hi I want to select the date from the timestamp data.
select cast(timestampcol as date) from dual;
sort of output.
Don want to get Day month year from the timestamp and then concatenate. want a single column output.
Is there any simple function.

Thank you.
Re: Want to select date from timestamp [message #213737 is a reply to message #213731] Fri, 12 January 2007 02:18 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
Don want to get Day month year from the timestamp and then concatenate. want a single column output.

What do you mean by this?

If you want us to help you, better elaborate your problem and give sample data.

By
Vamsi
Re: Want to select date from timestamp [message #213773 is a reply to message #213737] Fri, 12 January 2007 04:34 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you mean something like this (use of the TO_CHAR function)?
SQL> create table brisime (col timestamp);

Table created.

SQL> insert into brisime values (current_timestamp);

1 row created.

SQL> select to_char(col, 'dd.mm.yyyy. hh24:mi:ss') result from brisime;

RESULT
--------------------
12.01.2007. 11:31:33

SQL>
Re: Want to select date from timestamp [message #213830 is a reply to message #213773] Fri, 12 January 2007 08:07 Go to previous messageGo to next message
amit_nanote
Messages: 56
Registered: July 2005
Location: Indore
Member

hi,
I am explaining my problem ...
suppose timestamp is 12.01.2007. 11:31:33, I want only "12.01.2007" as output.
One way is to get the mentioned output by
select substr(to_char('12.01.2007. 11:31:33'),1,10) from dual;
Second is
Extracting Day, month and year
SELECT EXTRACT(DAY FROM TO_DATE('12.Jan.2007 11:31:33','DD-MON-YYYY HH24:MI:SS')) ||'-'|| EXTRACT(MONTH FROM TO_DATE('12.Jan.2007 11:31:33','DD-MON-YYYY HH24:MI:SS')) ||'-'||EXTRACT(YEAR FROM TO_DATE('12.Jan.2007 11:31:33','DD-MON-YYYY HH24:MI:SS')) as DateFound
FROM dual;

I want to know that is there any function in Oracle which directly give me date from the timestamp. As MySQL have a equivalent function from the same -
date(timestamp)='12.Jan.2007 11:31:33'

Hope This will help u to find the result for me.
Thank you.
Re: Want to select date from timestamp [message #213831 is a reply to message #213830] Fri, 12 January 2007 08:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to read up on Date Formats

SQL> create table brisime (col timestamp);

Table created.

SQL> insert into brisime values (current_timestamp);

1 row created.

SQL> select to_char(col, 'dd/mm/yyyy') result from brisime;

RESULT
----------
12/01/2007


Re: Want to select date from timestamp [message #213834 is a reply to message #213830] Fri, 12 January 2007 08:22 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
You need to read about the DATE type in the Oracle documentation. Dates are stored as floating point numbers. The portion to the left of the decimal represents the date and the portion to the right of the decimal represents the time.

When you use a DATE type in Oracle, it is always stored in this manner. When you query a DATE field, the results are displayed based on the default DATE format (DD-MON-RR) set in your SQLplus session. IF YOU WANT A DIFFERENT FORMAT, YOU HAVE TO SPECIFY THE FORMAT.

Perform the test Littlefoot describes, but go one step further...

select to_char(col, 'dd.mm.yyyy. hh24:mi:ss') result from brisime;

select to_char(col, 'dd.mm.yyyy') result from brisime;

select to_char(col, MM/DD/YYYY') result from brisime;

select to_char(col, 'Month fmDD, YYYY - HH:MI:SS am') result from brisime;




Notice that you get the same timestamp information in various formats. Same DATE entry displayed in different ways.

If you can remember "everytime you query a DATE field, you need to format how you display it", you and dates will get along fine.

Regards,
Ron

P.S. Also remember, '01-JAN-07' is different from to_date('01-JAN-07','DD-MON-RR'). The first is a string and the second is a DATE type number.
Re: Want to select date from timestamp [message #213836 is a reply to message #213830] Fri, 12 January 2007 08:25 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
hi amit,

if you know the deisred output you can create your user user defined functions.

Ashu
Re: Want to select date from timestamp [message #213853 is a reply to message #213731] Fri, 12 January 2007 09:13 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member
How about casting ?

select cast(systimestamp as date) from dual;

Srini


Re: Want to select date from timestamp [message #213855 is a reply to message #213853] Fri, 12 January 2007 09:23 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Depending on what you want to achieve truncating to the date-only part is also an option

select trunc(systimestamp) from dual;

Re: Want to select date from timestamp [message #213866 is a reply to message #213855] Fri, 12 January 2007 10:25 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I think these depend on the NLS_DATE_FORMAT.
Better to use to_char only.

By
Vamsi
Previous Topic: ora-01722 error when using to_number
Next Topic: DATE
Goto Forum:
  


Current Time: Thu Dec 08 10:39:29 CST 2016

Total time taken to generate the page: 0.15623 seconds