| Convert date format for easier lookup [message #606254] |
Tue, 21 January 2014 13:04  |
 |
tdnguyen
Messages: 2
Registered: January 2014
Location: Arlington
|
Junior Member |
|
|
Hello,
I'm working on a SQL ORacle query. I have a column called Date_IN that contains string values of dates formatted like DD-MM-YY (20-May-13). How can I filter data by just looking at year only. For example, like where year(date_in) >= 2013. How can I convert something like that or by month and year only?
Also, I have a query to retrieve data for the latest weekending. For example, Where Date_in = to_date('01/12/2014','MM/DD/YYYY') . This week I have to change the code to
Where Date_in = to_date('01/17/2014','MM/DD/YYYY'). Is there a way to have this automatically updated to the latest week ending when it is available.
Thanks
|
|
|
|
|
|
|
|
| Re: Convert date format for easier lookup [message #606259 is a reply to message #606254] |
Tue, 21 January 2014 13:17  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
You created yourself an unneccessary problem just by storing dates as string values - bad design. Now you have to deal with it. Now you have to use something like:
TO_DATE(date_in,'DD-MM-YY') >= DATE '2013-01-01'
which means it will not use index on date_in, even if you have one.
SY.
|
|
|
|
|
|
| Re: Convert date format for easier lookup [message #606854 is a reply to message #606254] |
Thu, 30 January 2014 01:23 |
subhra_88
Messages: 14
Registered: April 2007
Location: Bangalore
|
Junior Member |
|
|
Quote:Is there a way to have this automatically updated to the latest week ending when it is available.
First thing is you need to change the column to date datatype as others have advised. After that you can easily use the 'sysdate' to get the date which is 7 days back or manipulate as per your needs using date functions that oracle provides.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
