Return Invalid date

>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?!?

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

Select allOpen 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).