SQL query date conversion

I have a query that takes a really long time, im trying to optimize it.  One thing that I want to do is change the way that we are converting the data.  I am currently converting the dates from char values to a date.  is there a better way to do this?
(CASE ISDATE(substring(birth_date, 1, 2) + '/' + substring(birth_date, 3, 2) + '/' + substring(birth_date, 5, 4))WHEN 1 THEN convert(datetime,(substring(birth_date, 1, 2) + '/' + substring(birth_date, 3, 2) + '/' + substring(birth_date, 5, 4)),101)ELSE NULL END)as birth_date,

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DB_FuryAuthor Commented:
oh thanks for spotting that, this is SQL 2005
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
The proper way is ofcourse to store dates as dates.

This gives two paths that differ weather you own the database design or not.

1. You own the database and can do whatever.
Solution, If you really need the char values, create a new column and populate it with an update query to populate the dates. When this is done, you'll utilize the correct column instead of the char column.

2. You dont own the database, but your optimizing some report i e crystal report, data to excel or something similar.
Solution, Create a small database, named <DBNAME>_DateFix.
Create a table, with primary key from the table containing 'birth_date' and then a date column. Now populate it using primary keys and the conversion to dates. When this is done, you'll utilize the correct column from the new database instead of the char column in the original database. Make sure you set security for the new database to match the security for the original database.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alfred A.Commented:

I fully agree with "martenrune".  The proper way is to store dates as dates.

SELECT field concatenation most of the time slows down a query and you will really feel it especially if you have a very large database.

Also, is there a reason why you need to check for the dd, mm and yyyy?  Is the char birth_date field not always in dd/mm/yyyy form?  If the information in the birth_date field which is char has the dd/mm/yyyy pattern, you could just use, for example, a direct CONVERT(datetime,birth_date,101).

Just "my two cents" of it.  :-)
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

DB_FuryAuthor Commented:
it has to be a char value, i get the data in the format mmddyyyy, instead of doing the conversion in the select statement i think im just going to select it into a temp table and do a update on that information and then insert all the information into a table.  it sounds odd but i have to accept every value on my form as a char value i cant accept it as a date field becuase they could enter xxxxxx for the date and i have to accept every thing they enter, my app will make them edit that field.
Tim HumphriesDirectorCommented:

In your app, why don't you store the values entered, and if they enter a valid date (in the form mmddyyyy) then populate a true date field (on the same table) with the date - otherwise set this date field to 1/1/1900 or similar. Then you have the text entered as well as a date field you can use properly in your queries.

Tim HumphriesDirectorCommented:
Hi - sorry - just realise this is exactly what martenrune suggested. I obviously agree!

Alfred A.Commented:
Hi DB_Fury,

Just a suggestion, you could design your form to incorporate for example, three dropdown boxes that captures month, day, and year.  Or three textboxes, and then validate them on the form before even touching the database.  You have to force your users to behave. :-)

Another suggestion is use an AJAX calendar in your form.

This would at least save you the time to deal with complex situations in the database.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.