Return Invalid date

Hi,

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

I want a query which returns the invalid dates.

Thanks
csaanswersAsked:
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

http://msdn.microsoft.com/en-us/library/ms187347.aspx
0
 
CodeCruiserCommented:
>I want a query which returns the invalid dates.
Please explain
0
 
jppintoCommented:
You need to post the part of the code that gives you the error, right?!?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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.
0
 
Alpesh PatelAssistant ConsultantCommented:
Correct : Select convert(datetime,'20110214',103)
Incorrect : Select convert(datetime,'2011-02-14',103)

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

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.

Regards
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
Go
declare @DateValue nvarchar(20)
while ((select count(*) from #tmp where validDate=0)<>0)
	Begin 
	select top 1 @DateValue= datevalue from #Tmp where validDate=0
		begin 
			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
		end
	end
select * from #Tmp where validDate= 2 --Error occured
drop table #Tmp

Open in new window

0
 
CodeCruiserCommented:
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

Date.ToString("dd/MM/yyyy")
0
 
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).

Thanks
0
 
CodeCruiserCommented:
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 ...
0
 
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).
0
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.