Home » SQL & PL/SQL » SQL & PL/SQL » Date function
Date function [message #378529] Tue, 30 December 2008 21:11 Go to next message
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 Go to previous messageGo to next message
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 #378532 is a reply to message #378530] Tue, 30 December 2008 21:34 Go to previous messageGo to next message
Reporting
Messages: 6
Registered: December 2008
Junior Member
Thanks for the solution , could you please explain how this function automatically appends to 12:00:00 AM ?
Re: Date function [message #378533 is a reply to message #378532] Tue, 30 December 2008 21:41 Go to previous messageGo to next message
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 #378534 is a reply to message #378533] Tue, 30 December 2008 21:46 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Check TO_CHAR (datetime) and TRUNC (date).
Re: Date function [message #378535 is a reply to message #378534] Tue, 30 December 2008 22:00 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #378541 is a reply to message #378529] Tue, 30 December 2008 22:34 Go to previous messageGo to next message
bvkarthik
Messages: 4
Registered: May 2005
Junior Member
select to_char(trunc(sysdate), 'DD-MON-YYYY HH:MI:SS AM') from dual;

Try this query

Regards
Vijay Karthik Cool
Re: Date function [message #378542 is a reply to message #378538] Tue, 30 December 2008 22:35 Go to previous messageGo to next message
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.
Re: Date function [message #378546 is a reply to message #378542] Tue, 30 December 2008 22:50 Go to previous messageGo to next message
Reporting
Messages: 6
Registered: December 2008
Junior Member
Thank you very much !!!
Re: Date function [message #378704 is a reply to message #378541] Wed, 31 December 2008 09:42 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
bvkarthik wrote on Tue, 30 December 2008 23:34
select to_char(trunc(sysdate), 'DD-MON-YYYY HH:MI:SS AM') from dual;

Try this query

Regards
Vijay Karthik Cool


So I assume you simply copied the query that was already suggested above ??

ebrian wrote on Tue, 30 December 2008 22:24

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


Previous Topic: Need to Update 20 million records of data
Next Topic: Invailad row id , ora-01410
Goto Forum:
  


Current Time: Wed Nov 13 05:47:50 CST 2024