SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 query help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aakcse
Aged Yak Warrior

India
570 Posts

Posted - 07/14/2013 :  14:29:24  Show Profile  Reply with Quote
I've a table

Tab X with col (id int Primarykey, dt_dateX Varhcar(8))

the code is trying to insert the table X data into table Y by doing
(SUBSTRING(dt_dateX ,1,2) + '/' + SUBSTRING(dt_dateX ,3,2) + '/' + SUBSTRING(dt_dateX ,5,4))

Tab Y with col ( id int, dt_dateY smalldatetime)

table X is production table with 1.5 million rec, out of which very few records have data issue and failing for insert into table Y
I want to capture the id value for them to report it for correction.
the above is the code in one of the production SP, my task here is just find the corrupt data(present in dt_dateY ) ...


can you please help me in developing a query or T-SQL loop so that I can isolate the corrupt data

-Neil

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 07/14/2013 :  15:21:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT * FROM dbo.TabX WHERE ISDATE(dt_dateX) = 0



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 07/14/2013 :  21:31:37  Show Profile  Reply with Quote
One of these - the first one if the data in dt_dateX is in expected to be in the MMDDYYYY format, and the second if it is DDMMYYYY format.
SELECT * FROM dbo.TabX WHERE 
ISDATE(SUBSTRING(dt_dateX,5,4)+SUBSTRING(dt_dateX,1,2)+SUBSTRING(dt_dateX,3,2)) = 0;

SELECT * FROM dbo.TabX WHERE 
ISDATE(SUBSTRING(dt_dateX,5,4)+SUBSTRING(dt_dateX,3,2)+SUBSTRING(dt_dateX,1,2)) = 0;
Go to Top of Page

aakcse
Aged Yak Warrior

India
570 Posts

Posted - 07/14/2013 :  23:54:21  Show Profile  Reply with Quote
Thanks this helps

Along with the above the below query helped a lot, as the date was exceeding the limit of 2079 for which it was failing to insert into table Y which has datatype defined as smalldatetime

select * from TabX where substring(ltrim(rtrim(dt_dateX )),5,4) > 2079
Or substring(ltrim(rtrim(dt_dateX )),5,4) < 1900




-Neil
Go to Top of Page

aakcse
Aged Yak Warrior

India
570 Posts

Posted - 07/14/2013 :  23:57:04  Show Profile  Reply with Quote
08162010 is the format of date in TabX dt_dateX column, hence the first also query helped

-Neil
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/15/2013 :  00:43:25  Show Profile  Reply with Quote
quote:
Originally posted by aakcse

08162010 is the format of date in TabX dt_dateX column, hence the first also query helped

-Neil


So far as its consistent you're safe and can apply a single logic to handle all of them.
The issue comes when you've dates with different formats in the same column with varchar type. Then you might have to consider them as groups based on date format and then apply a separate rule to handle each of them.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000