java date format stored as varchar - need to convert it into date

[ankur rathi]

Hi,

I am stuck at a basic issue related to SQL.

I've a table which stores date in varchar.

It's has data like this:

Mon Apr 25 08:11:20 GMT 2011
Mon Apr 25 08:14:15 GMT 2011
Mon Apr 25 11:46:00 GMT 2011
Tue Apr 26 09:22:08 GMT 2011

My requirement is to compare dates for two rows.

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?

Thanks in advance.

[Andrey Kozhanov]

Well, theoretically it is possible to construct date string in acceptable format using 'substring' SQL function.

[ankur rathi]

Andrey Kozhanov wrote:Well, theoretically it is possible to construct date string in acceptable format using 'substring' SQL function.

Thanks, I actually thought that but it seems bit complex to convert a string of format "Mon Apr 25 08:14:15 GMT 2011" to format "20110425081415", in SQL - or may be I am not very comfirtable with it.

I was hoping if I could do that with less code/effort.

[Sudheer Bhat]

select to_date(SUBSTR('Mon Apr 25 08:11:20 GMT 2011',INSTR('Mon Apr 25 08:11:20 GMT 2011',' ',1,2)+1,2)||'-'|| SUBSTR('Mon Apr 25 08:11:20 GMT 2011',INSTR('Mon Apr 25 08:11:20 GMT 2011',' ',1,1)+1,3)||'-'|| SUBSTR('Mon Apr 25 08:11:20 GMT 2011',INSTR('Mon Apr 25 08:11:20 GMT 2011',' ',1,5)+1,4)||' '|| SUBSTR('Mon Apr 25 08:11:20 GMT 2011',INSTR('Mon Apr 25 08:11:20 GMT 2011',' ',1,3)+1,8),'DD-MON-YYYY HH24:MI:SS') from dual

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.

[Paul Sturrock]

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.

[Sean Clark]

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.

Sean

[chris webster]

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.

[Mike Zal]

I agree with everyone that it would be better practice to change the database field type. TO answer your question though, see if the following works

to_date(column_name,'Dy Mon HH24:MI:SS TZD YYYY')

In theory that should return your varchar column as a date type column. I don't have access to a database right now to verify.

[ankur rathi]

Thanks a ton everyone!

I am going with altering column type to date. Thanks again!