Return Invalid date


 I am getting this error "Conversion failed when converting datetime from character string."

I want a query which returns the invalid dates.

Who is Participating?
CodeCruiserConnect With a Mentor Commented:
First, make sure you do not use datatypes as columnnames. Date is a type and using it as a column name is an invitation to trouble.

Try the examples from here
>I want a query which returns the invalid dates.
Please explain
You need to post the part of the code that gives you the error, right?!?
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

csaanswersAuthor Commented:
Select convert(datetime,date,103)
from dbo.test

I was using convert(datetime,date,103) for where clause too. Since it gave me errors i used in select. It too gave me this error.
Alpesh PatelAssistant ConsultantCommented:
Correct : Select convert(datetime,'20110214',103)
Incorrect : Select convert(datetime,'2011-02-14',103)

Please find your result
John ClaesSenior .Net Consultant & Technical AnalistCommented:

I can see that you want you're table to be scanned to retrieve the values that are not correct Date's

The following scripts generates a Temporary table with all you're vallues and will loop through them.
I'm inserting the values manualy but you should do this from you're original table ;-)

The Values that are not Parseable into the dateTimeFormat will get the Validata Value 2.

This way you can instead of the Select * from ... part link this to you're original table to retrieve ID's or do you're thing to correct them.

Poor beggar

create table #Tmp
datevalue varchar(20),
validDate int,
insert into #Tmp select '14/02/2011',0
insert into #Tmp select '14:02:2011',0
insert into #Tmp select '14:02/2011',0
insert into #Tmp select '14/02:2011',0
declare @DateValue nvarchar(20)
while ((select count(*) from #tmp where validDate=0)<>0)
	select top 1 @DateValue= datevalue from #Tmp where validDate=0
			begin try
				update #Tmp set validDate=1 where datevalue = @DateValue and convert(datetime,datevalue,103) = convert(datetime,@DateValue,103)
				--select convert(datetime,date) from #Tmp
			end try
			begin catch
				update #Tmp set validDate=2 where datevalue = @DateValue
			end catch
select * from #Tmp where validDate= 2 --Error occured
drop table #Tmp

Open in new window

What format is the date stored? Is it consistent or depends on user regional settings. To avoid these problems, I always store dates in consistent format using

csaanswersAuthor Commented:
Hi ,

How can i get the return value of the convert(datetime,date,103).
Exmple, ISDATE(date as datetime) returns the invalid dates.

SO if i can use If else condition to check the convert(datetime,date,103).

Convert to generate an error if the date can not be parsed.

>ISDATE(date as datetime) returns the invalid dates.
IsDate is expected to return 0/1 value not invalid date.

You can try this

Select TheDate=CASE IsDate(dt) When 1 Then Convert(Date, dt, 103) Else Null End
From ...
csaanswersAuthor Commented:
Date is the column name which is a char field.
I have found out from the IsDate(date). the 0 returned values are all the dates which are 28/03/2011, 13/12/2010. I checked the regional settings and it set to the dd/MM/yyyy. Why IsDate(date) ruturning these dates as invalid.

Code cruiser please specify the Convert(Date, dt, 103).
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.