Solved

Return Invalid date

Posted on 2011-02-14
10
1,063 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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now