December 14, 2007 at 1:18 pm
We receive a text file from ADP on a daily basis with all employees in it. Our DBA has a job that takes the text file and dumps it into a table in SQL Server for me. His job then calls my stored procedure that reads the data in that table and updates user information in a different system based on that current data from ADP. The process hiccuped today when evaluting a field as a date. This is verbatim from my proc:
update UserAssessmentInfo
set StatusCode = 'T',
LastUpdateID = 'ADP_DailyFeed',
LastUpdateDate = @ChangeDate
from ADP_DailyFeed adp
inner join UserInfo u on adp.EmplID = u.EmployeeID and adp.Empl_Rcd_Nbr = u.RecordNumber
inner join UserAssessmentInfo ua on u.UserID = ua.UserID
where ua.AssessmentPeriodID = @AssessmentPeriodID
and ua.StatusCode in ('A', 'L') -- assoc Active or on LOA in PCA...
and adp.Status in ('T', 'D') -- ... but just was Termed or Deactivated in ADP
and convert(datetime, EffDt) < @ChangeDate -- only do this if the effective date was prior to today
I got some bad data in the EffDt column today because when this section of the proc runs, I get the famous:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
All columns in the table the DBA creates from the ADP file are varchar which is why I have to convert EffDt to datetime and why a bad value in today's data is breaking the process.
The point is, I wanted an easy way to identify the offending record. My first thought was, how can I use the famous Tally Table??? I could not figure out how to do it as a set based operation. I had to resort to:
-- Get all Employee IDs and dates in a temp table for analysis
create table #Dates (EmplID int, EffDt varchar(20), EffDate datetime)
insert into #Dates
select EmplID, EffDt, NULL
from ADP_DailyFeed
create index #Dates_ix1 on #Dates(EmplID)
-- Attempt to update the EffDate column in the
-- temp table to a datetime.
declare @EmplID int
declare row_cursor cursor for
select EmplID from #Dates
open row_cursor
fetch next from row_cursor into @EmplID
while @@fetch_status = 0
begin
begin try
update #Dates
set EffDate = convert(datetime, EffDt)
where EmplID = @EmplID
end try
begin catch
select 'Bad date for Employee ID: ' + convert(varchar, @EmplID)
end catch
fetch next from row_cursor into @EmplID
end
close row_cursor
deallocate row_cursor
-- Return any rows where the EffDate is null
-- which would indicate the date could not
-- be converted
select * from #Dates
where EffDate is null
The table has 22,266 records and this ad hoc operation took about 20 seconds to complete. Philosophies on importing all columns as varchar aside. The main point/question is, is there ANY way to do this as a set based operation?? I'd love to implement the famous Tally Table if possible.
Paging Dr. Moden.... Dr. Jeff Moden... you're wanted in forum T-SQL (SS2K5).....
Thank you -
Lisa
December 14, 2007 at 1:34 pm
What about ISDATE() ?
Idenitify the bad data:
SELECT *
FROM YourTable
WHERE ISDATE(EffDt) = 0
December 14, 2007 at 1:40 pm
Good heavens! That was so simple it's embarrassing! Nothing like trying to kill an ant with a sledgehammer!
Thank you PW!
(I was just so anxious to use that darn Tally Table!!)
December 14, 2007 at 5:58 pm
Um... be careful... ISDATE() lies almost as bad as ISNUMERIC() is when you try to use it as an ISALLDIGITS function. Sure, the following will allow a conversion, but do you really want it to?
[font="Courier New"] SELECT ISDATE('2000'),
ISDATE('MAY 2000')
SELECT CAST('2000' AS DATETIME)
SELECT CAST('MAY 2000' AS DATETIME)[/font]
PW has the correct idea, though... If the dates are supposed to be in a given format, you might want to check that format along with using ISDATE() to validate the data.
Sorry about the Tally table on this one but it isn't the right thing to use here...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply