.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
satyapriyanayak
abhays
SP
Ashutosh Jha
Mahesh

Home >> Forum >> Sql Server >> Post New QuestionBookmark and Share 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
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

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