[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

SQL query date conversion

Posted on 2010-03-23
9
Medium Priority
?
419 Views
Last Modified: 2012-05-09
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

0
Comment
Question by:DB_Fury
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 1

Author Comment

by:DB_Fury
ID: 28350308
oh thanks for spotting that, this is SQL 2005
0
 
LVL 20

Accepted Solution

by:
Marten Rune earned 2000 total points
ID: 28353997
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.

//Marten
0
 
LVL 21

Expert Comment

by:Alfred A.
ID: 28355983
Hi,

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.  :-)
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:DB_Fury
ID: 28357408
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.
0
 
LVL 15

Expert Comment

by:Tim Humphries
ID: 28358122
H,

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
0
 
LVL 15

Expert Comment

by:Tim Humphries
ID: 28358227
Hi - sorry - just realise this is exactly what martenrune suggested. I obviously agree!

Tim
0
 
LVL 21

Expert Comment

by:Alfred A.
ID: 28358330
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.

0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question