Date function [message #378529] |
Tue, 30 December 2008 21:11 |
Reporting
Messages: 6 Registered: December 2008
|
Junior Member |
|
|
I am trying to append the sysdate to 12:00:00 AM but its not displaying time , its only displaying date.Can anybody help?
Thanks in adv
Reporting
|
|
|
Re: Date function [message #378530 is a reply to message #378529] |
Tue, 30 December 2008 21:24 |
|
ebrian
Messages: 2794 Registered: April 2006
|
Senior Member |
|
|
Append or prefix ?
Prefix:
SQL> select to_char(trunc(sysdate), 'DD-MON-YYYY HH:MI:SS AM') from dual;
TO_CHAR(TRUNC(SYSDATE),
-----------------------
30-DEC-2008 12:00:00 AM
Append:
SQL> select to_char(trunc(sysdate), 'HH:MI:SS AM DD-MON-YYYY') from dual;
TO_CHAR(TRUNC(SYSDATE),
-----------------------
12:00:00 AM 30-DEC-2008
[Updated on: Tue, 30 December 2008 21:26] Report message to a moderator
|
|
|
|
Re: Date function [message #378533 is a reply to message #378532] |
Tue, 30 December 2008 21:41 |
Reporting
Messages: 6 Registered: December 2008
|
Junior Member |
|
|
I am using following query :
SELECT TO_DATE(TO_CHAR(sysdate,'dd-MON-YYYY') || '00:00:00 ' , 'dd-MON-YYYY HH24:MI:SS') FROM SYS.DUAL;
but its not displaying time , can you help me in understanding why time is not getting displayed?
|
|
|
|
Re: Date function [message #378535 is a reply to message #378534] |
Tue, 30 December 2008 22:00 |
danish_fsd@yahoo.com
Messages: 38 Registered: February 2008 Location: Pakistan
|
Member |
|
|
hi
Try this one. Hope it will help you.
SELECT TO_char(to_date(to_char(sysdate, 'dd-MON-YYYY')||'00:00:00', 'dd-MON-YYYY HH24:MI:SS') ,
'dd-MON-YYYY HH24:MI:SS') FROM sys.dual
Regards
Danish
|
|
|
Re: Date function [message #378538 is a reply to message #378535] |
Tue, 30 December 2008 22:19 |
Reporting
Messages: 6 Registered: December 2008
|
Junior Member |
|
|
Thanks ebrain & Danish ..
It works Danish but i am juts geing curious to understand why time is coming in case of to_char only?Is there any function which can be used to get datetime ?
|
|
|
|
Re: Date function [message #378542 is a reply to message #378538] |
Tue, 30 December 2008 22:35 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Reporting wrote on Wed, 31 December 2008 05:19 | It works Danish but i am juts geing curious to understand why time is coming in case of to_char only?Is there any function which can be used to get datetime ?
|
You shall realize the difference between VARCHAR2 and DATE data type and its representation in result set.
VARCHAR2 is string, so its value is directly displayed.
DATE is in special format; so before displaying it, it has to be converted into string. Implicitely, NLS_DATE_FORMAT parameter is used.
If you want to only display the DATE column, convert it to string. However use DATE data type when processing it further (in subquery, PL/SQL, ...).
Also, Danish's code is too complicated, it would be sufficient to use only SELECT TO_CHAR(sysdate,'dd-MON-YYYY') || '00:00:00 ' FROM SYS.DUAL;
But I would prefer ebrian's solution.
|
|
|
|
|