.NET Tutorials, Forums, Interview Questions And Answers

Welcome :Guest

Sign In

Register

Win Surprise Gifts!!!

Congratulations!!!

Top 5 Contributors of the Month
ffttt


Home >> Forum >> Sql Server >>	Post New Question		Subscribe to Forum

Sql Server Exception Handling

Posted By: Naresh Posted Date: May 20, 2010 Points:2 Category :Sql Server

hi,

Iam having a table with around 1 lakh record.
Iam having column start_date with data type varchar.
while converting to dateTime format iam getting error.Because of some record not in date time format. How can i identify those record.
So i need a query to throw exception the particular id whose date format is not correct.

My requirement is to get the ID for which the error is thrown.

Thanks
Naresh

Responses

Author: Suresh
Posted Date: May 21, 2010 Points: 5

Hi Naresh,
Kindly go through below code, it vil give solution for your issue.

Declare @t table
(
id int,
datefield varchar(30)
)
Insert into @t
Select 1,'2010-05-21 13:56:53.047'
Union All
Select 2,'2010-05-22 13:56:53.047'
Union All
Select 3,'May-20'
Union All
Select 4,'2010-05-25 13:56:53.047'
Union All
Select 5,'Naresh'

Declare @id int
Set @id=5 --Initialize your id over here

if(Select isdate(datefield) from @t where id=@id)=1
Print 'True'
Else
Print 'False'

Let me know that, do you need any further clarification.

Author: Naresh
Posted Date: May 21, 2010 Points: 5

Hi Suresh,
Thank u for ur reply

I need further clarification,

I need to display the id which is not in date format,
With ur query i cant check for one one id where u told to declare,

(Declare @id int
Set @id=5 --Initialize your id over here)

I have more than one lakh records where i cant check for one one id,

PLs give me any other solution,

Suresh thank u for ur previous query. It helped me alot

Thanks
Naresh

Thanks
Naresh

Author: Suresh Accepted Answer
Posted Date: May 21, 2010 Points: 10

Hello Naresh,
So that here ,you need to rewrite the above query like this.
Declare @t table
(
id int,
datefield varchar(30)
)
Insert into @t
Select 1,'2010-05-21 13:56:53.047'
Union All
Select 2,'2010-05-22 13:56:53.047'
Union All
Select 3,'May-20'
Union All
Select 4,'2010-05-25 13:56:53.047'
Union All
Select 5,'Naresh'

Select Stuff((Select +','+ cast(id as varchar) from @t where isdate(datefield)=0 for xml path('')),1,1,space(0)) NondateValue

--OutPUT
3,5

Here you will get Nondatevalue id are listed. Kindly try this query. Let Me know that, do you need further clarification.

Author: Naresh
Posted Date: May 21, 2010 Points: 5

Hi Suresh,

I tried the query. It worked exactly how i want.
Further if i needed any clarification i'll ask u

But when only year is present ,am not getting error on that Id

Eg:
Insert into @t
Select 1,'2010'

But if iam Having
Insert into @t
Select 1,'2010_'

Iam getting that ID.

Can u suggest me to solve the problem

Thank u for ur quick reply

Naresh

Thanks
Naresh

Author: Suresh Accepted Answer
Posted Date: May 21, 2010 Points: 10

Hello Naresh,
Here you need to execute dynamic sql.so that kindly run the below query.

Create table t
(
id int,
datefield varchar(30)
)
Insert into t
Select 1,'2010-05-21 13:56:53.047'
Union All
Select 2,'2010-05-22 13:56:53.047'
Union All
Select 3,'May-20'
Union All
Select 4,'2010-05-25 13:56:53.047'
Union All
Select 5,'Naresh'
Union All
Select 6,'2010'

Declare @result nvarchar(max)
Declare @DateValue nvarchar(max)
Select @DateValue =Stuff((Select +','+ cast(id as varchar) from t where isdate(datefield)=1 and len(datefield)>10 for xml path('')),1,1,space(0))
Set @result='Select ID from t where ID not in ('+@DateValue+')'
print @result
Exec sp_executesql @result

--Output
3
5
6

Here you will get Nondatevalue id are listed. Kindly try this query. Let Me know that, do you need further clarification.
If you got correct result,kindly mark as answer

Author: Suresh Accepted Answer
Posted Date: May 21, 2010 Points: 10

hello Naresh
If you dont want use dynamic sql. Kindly try this approach. You will get Nondate value .

Declare @t table
(
id int,
datefield varchar(30)
)
Insert into @t
Select 1,'2010-05-21 13:56:53.047'
Union All
Select 2,'2010-05-22 13:56:53.047'
Union All
Select 3,'May-20'
Union All
Select 4,'2010-05-25 13:56:53.047'
Union All
Select 5,'Naresh'
Union All
Select 6,'2010'
--Select id from @t where isdate(datefield)=1 and len(datefield)>10

Declare @t1 table
(
id int
)

Insert into @t1
Select id from @t where isdate(datefield)=1 and len(datefield)>10
--Select * from @t1
Select * from @t where id not in (Select id from @t1)

--OutPut
id datefield
3 May-20
5 Naresh
6 2010

Author: Naresh
Posted Date: May 21, 2010 Points: 5

Hi Suresh,

Both the query i tried and i got what i needed.

Thank u Suresh for replaying me immediately

Naresh

Thanks
Naresh

Post Reply

You must Sign In To post reply

Latest Forum Questions From The Same Category

Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here

Quick Links For Forum Categories:

ASP.Net	Windows Application	.NET Framework	C#	VB.Net	ADO.Net
Sql Server	SharePoint	OOPs	Silverlight	IIS	JQuery
JavaScript/VBScript	Biztalk	WPF	Patten/Practices	WCF	Others
www.DotNetSpark.com	UnAnswered	All

Hall of Fame Twitter Terms of Service Privacy Policy Contact Us Archives Tell A Friend

Sql Server Exception Handling

Related Questions Related Questions

Latest Forum Questions From The Same Category