x

compare a datetime with a varchar(30)

I have a subquery which converts a text coloumn into datetime. Since it is in text format there are coloumns wich contains bad data. I know the first answer would be to correct the data, I strongly agree that. I do not have the privileges to do that, unfortunately i have to deal with it.

below is my query

  INNER JOIN TABLE XYZ
     ON XYZ.COLOUMN1=YZX.COLOUMN2
     LEFT JOIN ( SELECT ABC.stu_id
                ABC.stu_name
                CONVERT(DATETIME,LMN.startDate,111) STARTDATE
                CONVERT(DATETIME,LMN.endDate,111) ENDDATE
     FROM STUDENT ABC
     INNER JOIN AN_STUDENT_TABLE LMN
                 ON ABC.stu_id=LMN.stu_id
     WHERE ISDATE(startDate)=1
     AND   ISDATE(endDate)=1
     GROUP BY ABC.stu_id,ABC.stu_name,STARTDATE,ENDDATE) DIN  ON DIN.stu_id=LMNOP.stu_id
     WHERE e.date BETWEEN DIN.STARTDATE AND DIN.ENDDATE 

when i compare e.date with the startdate and enddate it fails giving me an well know error

 "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

I also tried to work something like

 instead use CONVERT(DATETIME,CASE WHEN ISDATE(LMN.startDate)=1 THEN LMN.startDate END,111) and same for end date 

This didnt workout as well. When i closely looked my coloumns endDate and startDate this is what i could find

 startDate      endDate
 01/23/1200    
 01/01/2009     09/14/0210
 01/01/2010     12/31/2020
 01/01/2009     05/12/1212
 09/25/1008
 01/01/2002     01/01/1320
 10/01/1001
 01/01/2009     01/23/1200
 10/01/1007
 09/08/1010
 08/01/0120
 01/01/0201
 09/22/1009

what can be done to atleast skip/overcome those bad data records which cannot be converted/compared? I tried my best to figure this out but failed. Any help/advice appretiated!

more ▼

asked Sep 02, 2011 at 10:16 AM in Default

avatar image

palum
249 27 29 33

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Given that you can't guarantee that the data presented is a valid date for SQL Server's purposes, then I would suggest looking at slicing & dicing the data.

From what you've shown, the first two parts of the date are OK, it's just the year that's causing problems. In which case, I would suggest checking that convert(integer, substring(startdate,7,4)) > 1900 before doing anything else.

Maybe create a temporary table, or a CTE, or a view that's populated only with data that matches the criterion above, and using that for the rest of your query.

Sorry - can't give you more time on this now as I've got to go out. Perhaps one of m'learned colleagues will take over from here... :-)

more ▼

answered Sep 02, 2011 at 10:31 AM

avatar image

ThomasRushton ♦♦
41.8k 20 50 53

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x434
x11

asked: Sep 02, 2011 at 10:16 AM

Seen: 1018 times

Last Updated: Sep 02, 2011 at 10:16 AM

Copyright 2017 Redgate Software. Privacy Policy