| convert datetime to date [message #570391] |
Thu, 08 November 2012 09:45  |
 |
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 #570408 is a reply to message #570402] |
Thu, 08 November 2012 17:44  |
Solomon Yakobson
Messages: 1397
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.
|
|
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
