query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query help

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2013-07-14 : 14:29:24

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

30421 Posts

Posted - 2013-07-14 : 15:21:17

SELECT * FROM dbo.TabX WHERE ISDATE(dt_dateX) = 0

N 56°04'39.26"
E 12°55'05.63"

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-14 : 21:31:37

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;

aakcse
Aged Yak Warrior

570 Posts

Posted - 2013-07-14 : 23:54:21

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

aakcse
Aged Yak Warrior

570 Posts

Posted - 2013-07-14 : 23:57:04

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

-Neil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-15 : 00:43:25

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources