Home
|
Tutorial
|
Articles
|
Forum
|
Interview Question
|
Code Snippets
|
News
|
Fun Zone
|
Poll
|
Web Links
|
Certification
|
Search
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
Related Questions Related Questions
Exception Handling in sql server
Best practices of Exception handling in ASP.NET web applications
Exception Handling in Own Massage
exception handling in c#.net?
Exception handling
How to use throw () method in exception handling. Give me an example
Exception handling using NLog tool
query handling in asp
How to copy a file from server to local system
To save image in the SQL server.
Latest Forum Questions From The Same Category
Help to get SQL output
how to restore my sql database from my hosting provider?
SQL query to update date range
Add allowance to employee table is wrong or true according to my case
how to remove trailing zeroes in a decimal in sql server
Required Union All Result in two different column
Sql Multiple table data retrival
Table Translation records
How select huge Data from sql without take long time?
Passing using parameter in store procedure get the top3 record from the table
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