I could do that in Java after retriving the data from database using "date" & "string" functions... However I'd like to do that in query (SQL) itself. To do that I need to have this column in date format - I searched on net & found two functions cast & convert, they can convert varchar to date but they need data in a specific format. For example: 20060308000000 or 2006-03-08 00:00:00.
In my case cast or convert functions return null.
Is there anyway I can achive this in SQL?
Is there anyway I can convert varchar (on format 'Tue Apr 26 09:22:08 GMT 2011') to varchar of format which cast or convert expects?
Any other functions I can use here?
This will convert your string to date(In oracle SQL). If you still have chance to revisit your data model, please do not store date as VARCHAR2. Its a very bad practice and if somebody changes the format in which they store the date into varchar2 column, you will have to change all your code again.
Yeah. If I were asked to do this I'd be looking to change the data model as a first step (you could leave the old field and add a trigger to the new to allow backward compatibility).
Queries with complex functions will probably end up as a performance drag and will probably need complex function based indices to help. And (as Sudheer notes) this is a very brittle way of storing dates.
Might not help in your case - but worth the argument with whoever asked you to do this I reckon.
Sudheer Bhat wrote:This will convert your string to date(In oracle SQL). If you still have chance to revisit your data model, please do not store date as VARCHAR2. Its a very bad practice and if somebody changes the format in which they store the date into varchar2 column, you will have to change all your code again.
I'd like to add to this by saying, not only is it bad practise, but you lose the ability to do any useful date queries such as searching for a specific date, day range, month range, time range etc.
Another vote for changing your data model to use a DATE column. The DATE data-type has been available in relational databases for at least 20 years, so even a casual database user should be aware of it by now. If you are still storing date values in character columns, that is just as pointless as storing numeric values in character columns: you lose access to all the built-in numeric/date logic in SQL, and the character data values take up more space in your database.
If you can't change the existing column, you could still add a new DATE column to the table, then add a trigger on the table to write the VARCHAR "date" value to the real DATE column every time the VARCHAR "date" value is updated. This would ensure that you always have a real date available when you want to run a query that includes date comparisons, and it also means you only have to perform the string/date conversion when the value has changed, not during every query.