Date Time error

Question

Post reply

Date Time error

dva2007

SSCertifiable

Points: 7707
More actions
September 15, 2008 at 4:55 am

#190770

Hi friends,
I am having one table which is having startdate and enddate for one work. Now enddate might be null because work is continue.
When i try to convert it like below it gives me error.
select convert(datetime, startdate) from table
select convert(datetime, enddate) from table
where enddate is not null
the startdate works ok but enddate gives problem and says:
Syntax error converting datetime from character string.
Any suggestion please?
thanks.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 1

well.... use the correct datatype !

In this case datetime.

This way one cannot enter invalid data in the first place.

Now you're stuck with character columns containing data that is invalid with regards to datetime.

you can get the overview using:

select *

from yourtable

where ISDATE ( yourdatecol ) = 0

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Amit Khanna-455738 SSC Eights! Points: 865 More actions · Answer 2

Hi,

Please tell the data type of Start Date and End Date.

Thanks,

Amit Khanna

dva2007 SSCertifiable Points: 7707 More actions · Answer 3

Hi,

Well, it is already designed system. I cant change it easily. Both dates in the table is varchar. Thats the problem.

Thanks for looking.

dva2007 SSCertifiable Points: 7707 More actions · Answer 4

Hi,

If we accept the fact about wrong desing then try to sort out.

If there is a problem with varchar datatype then it should not work for startdate. How come it is working for one and not for the other?

Thanks.

dva2007 SSCertifiable Points: 7707 More actions · Answer 5

Hi Friends,

I found the solution. it was running for some time but failing at some point as table is having millions of record. I tried to see at point where it fails and found record with 'XXXX' into date field.

Thanks for your suggestion DBA.

dva2007 SSCertifiable Points: 7707 More actions · Answer 6

Hi ALZDBA,

I have found all invalid data. It is there for some reason. so i just need to ignore those particular records.

Thanks for your help.

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 7

dva2007 (9/15/2008)
Hi ALZDBA,
I have found all invalid data. It is there for some reason. so i just need to ignore those particular records.
Thanks for your help.

This is typical abuse caused by using incorrect datatypes.

You should convert this issue to another column (valid/invalid status or so) and correct your startdate and enddate columns (and convert them to datetime if you can ! )

At least test it !

Or else you'll be stuck with this kind of data bugs till the end of time.

select convert(datetime, enddate)

from table

where isdate(enddate) = 1