aspose file tools*
The moose likes JDBC and the fly likes java date format stored as varchar - need to convert it into date Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "java date format stored as varchar - need to convert it into date" Watch "java date format stored as varchar - need to convert it into date" New topic
Author

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

ankur rathi
Ranch Hand

Joined: Oct 11, 2004
Posts: 3830
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
Ranch Hand

Joined: Mar 12, 2010
Posts: 79
Well, theoretically it is possible to construct date string in acceptable format using 'substring' SQL function.
ankur rathi
Ranch Hand

Joined: Oct 11, 2004
Posts: 3830
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
Ranch Hand

Joined: Feb 22, 2011
Posts: 75


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
Bartender

Joined: Apr 14, 2004
Posts: 10336

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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Sean Clark
Rancher

Joined: Jul 15, 2009
Posts: 377

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


I love this place!
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1774
    
  14

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.

No more Blub for me, thank you, Vicar.
Mike Zal
Ranch Hand

Joined: May 04, 2011
Posts: 144

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



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.


OCJP6, OCWCD5
ankur rathi
Ranch Hand

Joined: Oct 11, 2004
Posts: 3830
Thanks a ton everyone!

I am going with altering column type to date. Thanks again!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: java date format stored as varchar - need to convert it into date