Link to home
Create AccountLog in
Avatar of csaanswers
csaanswers

asked on

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
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

>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?!?
Avatar of csaanswers
csaanswers

ASKER

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

Please find your result
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

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")
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
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 ...
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).
ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account