Home » SQL & PL/SQL » SQL & PL/SQL » convert datetime to date
convert datetime to date [message #570391] Thu, 08 November 2012 09:45 Go to next message
cherry3
Messages: 5
Registered: October 2012
Junior Member
Hi all,

I know this question has been asked several times. but i am starting out and i am struggling to get my head aroung it.I would like to convert datetime column of Oracle source
(RPT.SHIPMENT_VW) to date while loading to sql
Here is my code:

SELECT SHIPMENT_NBR,RECEIVED_DATE_TIME, RCVD_AT_DATE_GMT_FK
FROM RPT.SHIPMENT_VW
where WHERE RECEIVED_DATE_TIME = TO_DATE(TO_CHAR(:fromdate, 'DD/MM/YYYY'))

here RECEIVED_DATE_TIME is in 'DD/MM/YY hh:mi:si' fromat which, i want to convert to 'DD/MM/YY'
the above code throwing an error ORA:01843 not a valid month

if I use where clause like:
WHERE (RECEIVED_DATE_TIME = TO_DATE(TO_CHAR(:fromdate, 'DD/MM/YYYY'), 'DD/MM/YY'))then its not retrieving any data

If my question is not clear revert me back. I would appreciate all help offered.

Thanks
Re: convert datetime to date [message #570393 is a reply to message #570391] Thu, 08 November 2012 10:00 Go to previous messageGo to next message
BlackSwan
Messages: 23151
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


what datatype is FROMDATE?

SQL> DESC RPT.SHIPMENT_VW

post formatted results from above
Re: convert datetime to date [message #570398 is a reply to message #570393] Thu, 08 November 2012 10:37 Go to previous messageGo to next message
cherry3
Messages: 5
Registered: October 2012
Junior Member
:fromdate is runtime filter parameter. By default its fromat is RECEIVED_DATE_TIME fromat i.e 'DD/MM/YY hh:mi:ss'. I want to convet that to 'DD/MM/YY'using t-sql as I canot use ALTER
Re: convert datetime to date [message #570400 is a reply to message #570398] Thu, 08 November 2012 10:44 Go to previous messageGo to next message
BlackSwan
Messages: 23151
Registered: January 2009
Senior Member
> I want to convet that to 'DD/MM/YY'using t-sql as I canot use ALTER
Really? using T-SQL?

why did you not post requested details?

post complete results from following SQL

SELECT * FROM V$VERSION;
Re: convert datetime to date [message #570402 is a reply to message #570400] Thu, 08 November 2012 11:32 Go to previous messageGo to next message
Bill B
Messages: 1141
Registered: December 2004
Senior Member
use the following code

WHERE (trunc(RECEIVED_DATE_TIME) = TO_DATE(:fromdate, 'DD/MM/YYYY'))
Re: convert datetime to date [message #570408 is a reply to message #570402] Thu, 08 November 2012 17:44 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2078
Registered: January 2010
Senior Member
Regardles if column RECEIVED_DATE_TIME is indexed or not, it is always a good habit to use:

WHERE RECEIVED_DATE_TIME >= TO_DATE(:fromdate, 'DD/MM/YYYY')
  AND RECEIVED_DATE_TIME <  TO_DATE(:fromdate, 'DD/MM/YYYY') + 1


SY.
Re: convert datetime to date [message #570608 is a reply to message #570408] Mon, 12 November 2012 13:46 Go to previous message
cherry3
Messages: 5
Registered: October 2012
Junior Member
Thank you for all your answers.
Previous Topic: date conversion
Next Topic: Help with this Analytical Query (Windowing Function) Question
Goto Forum:
  


Current Time: Sat Dec 20 05:35:06 CST 2014

Total time taken to generate the page: 0.07932 seconds