Solved

Return Invalid date

Posted on 2011-02-14
10
1,066 Views
Last Modified: 2012-05-11
Hi,

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

I want a query which returns the invalid dates.

Thanks
0
Comment
Question by:csaanswers
10 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34886613
>I want a query which returns the invalid dates.
Please explain
0
 
LVL 33

Expert Comment

by:jppinto
ID: 34886614
You need to post the part of the code that gives you the error, right?!?
0
 

Author Comment

by:csaanswers
ID: 34886680
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 34886739
Correct : Select convert(datetime,'20110214',103)
Incorrect : Select convert(datetime,'2011-02-14',103)

Please find your result
0
 
LVL 10

Expert Comment

by:John Claes
ID: 34886807
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34887063
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
 

Author Comment

by:csaanswers
ID: 34891278
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34895322
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
 

Author Comment

by:csaanswers
ID: 34895554
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
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 50 total points
ID: 34895581
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

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question