Detecting bad date with YYYYMMDD format

Every night we get a big dump of data from one of our outside financial
companies. In the file, one of the columns is a date, with the
following format:
YYYYMMDD
When we migrate this data into our database, SQL Server will
automaically cast this 8-character "varchar" field to a smalldatetime
field. This means that I can do this:
UPDATE table
SET datefield = tb.datefield
FROM loadedfile tb
However, I recently discovered that they sent us a few bad dates
(20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value."
So, my question is -- how do I go about detecting that the 8-character
string is not a valid date, while doing it on-the-fly inside the UPDATE
statement?Do your import into a seperate table with generic data types. Add a column
to the head of the table that indicates the attempted date of import. Add
another column that will indicate the type of error that you are having
with the column.
Now, do the import to this table and update column1 with getdate and column2
with NULL.
Next, run a routine that will validate all the data and update column2 from
null to something that indicates error.
Finally, do your normal import from this new table only where column2 is
null. Now you have a history of all the issues.
When everything is complete, if there is a count(*) > 0 in this table,
meaning that there were errors, you can email someone or just toss red
flags how ever you do this.
You can also check count(*) to see if > 0 and if true then you might not
want to do any of the import process and throw your flags.

> Every night we get a big dump of data from one of our outside financial
> companies. In the file, one of the columns is a date, with the
> following format:
> YYYYMMDD
> When we migrate this data into our database, SQL Server will
> automaically cast this 8-character "varchar" field to a smalldatetime
> field. This means that I can do this:
> UPDATE table
> SET datefield = tb.datefield
> FROM loadedfile tb
> However, I recently discovered that they sent us a few bad dates
> (20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
> conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value."
> So, my question is -- how do I go about detecting that the 8-character
> string is not a valid date, while doing it on-the-fly inside the UPDATE
> statement?
new|||You can use the ISDATE() function, which returns 1 if valid and 0 if not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<jsevlie@.gmail.com> wrote in message news:1132076310.299318.326260@.g43g2000cwa.googlegroups
.com...
> Every night we get a big dump of data from one of our outside financial
> companies. In the file, one of the columns is a date, with the
> following format:
> YYYYMMDD
> When we migrate this data into our database, SQL Server will
> automaically cast this 8-character "varchar" field to a smalldatetime
> field. This means that I can do this:
> UPDATE table
> SET datefield = tb.datefield
> FROM loadedfile tb
> However, I recently discovered that they sent us a few bad dates
> (20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
> conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value."
> So, my question is -- how do I go about detecting that the 8-character
> string is not a valid date, while doing it on-the-fly inside the UPDATE
> statement?
>|||You can use a WHERE clause to limit the rows processed, or use CASE within
the statement.
Cheers,
'(' Jeff A. Stucker
\
Senior Consultant
www.rapidigm.com
<jsevlie@.gmail.com> wrote in message
news:1132076310.299318.326260@.g43g2000cwa.googlegroups.com...
> Every night we get a big dump of data from one of our outside financial
> companies. In the file, one of the columns is a date, with the
> following format:
> YYYYMMDD
> When we migrate this data into our database, SQL Server will
> automaically cast this 8-character "varchar" field to a smalldatetime
> field. This means that I can do this:
> UPDATE table
> SET datefield = tb.datefield
> FROM loadedfile tb
> However, I recently discovered that they sent us a few bad dates
> (20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
> conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value."
> So, my question is -- how do I go about detecting that the 8-character
> string is not a valid date, while doing it on-the-fly inside the UPDATE
> statement?
>|||Try this:
DECLARE @.Date varchar(8)
SET @.Date = '20851014'
SELECT ISDATE(@.Date)
It returns "1" (which is obviously not right!)
Howver, do this:
DECLARE @.Date varchar(8)
SET @.Date = '20851014'
SELECT CAST(@.date AS smalldatetime)
And it throws the error I'm getting.
Then, I did this:
declare @.dyear varchar(10)
declare @.dmonth varchar(10)
declare @.dday varchar(10)
declare @.newdate varchar(10)
IF ISDATE(@.Date) = 1 BEGIN
set @.dyear = substring(@.Date, 1, 4)
set @.dmonth = substring(@.Date, 5, 2)
set @.dday = substring(@.Date, 7, 2)
set @.newdate = @.dyear + '-' + @.dmonth + '-' + @.dday
select @.Date, @.newdate, isdate(@.newdate)
END
And it still thinks my string is a valid date. What gives?|||BOL shows a SmallDateTime as:
Date and time data from January 1, 1900, through June 6, 2079, with accuracy
to the minute.
A regular DateTime field will handled dates between the years 1753 and 9999.
So the first option is to store the data as a DateTime instead of a
SmallDateTime, and CAST the field to DateTime:
UPDATE table
SET datefield = CAST(tb.datefield AS DATETIME)
FROM loadedfile tb
If the field in table is SmallDateTime and you cannot change it, then you
will have to throw in some sort of business logic - what happens if the date
is > 2079? If it is to set the date to be entered as 1/1/2079, then you
could do something like this:
UPDATE table
SET datefield = CASE WHEN CAST(db.datefield AS DATETIME) > '1/1/2079'
THEN '1/1/2079'
ELSE CAST(tb.datefield AS SMALLDATETIME)
END
FROM loadedfile tb
This will check to see if the data passed in is too big, and if it is pass
in the date of '1/1/2079'. You could then query the table for anything with
the '1/1/2079' date and know it was something that was wrong - and follow up
to fix it.
Hope this helps some
Rob|||-- check for the smalldatetime limits
update table
set datefield = tb.datefield
from loadedfile tb
where tb.datefield>='19000101' and tb.datefield<='20790606'
-- report on those that are out of smalldatetime range
-- the implicit int conversion should be OK, unless other bad data is
there as well
select <col list>
from loadedfile
where datefield<'19000101' or datefield>'20790606'
you may want to change the datatype of the loadedfile table column to
datetime [not smalldatetime, to avoid errors] instead of varchar, then
you'd be comparing dates to dates.
jsevlie@.gmail.com wrote:
> Every night we get a big dump of data from one of our outside financial
> companies. In the file, one of the columns is a date, with the
> following format:
> YYYYMMDD
> When we migrate this data into our database, SQL Server will
> automaically cast this 8-character "varchar" field to a smalldatetime
> field. This means that I can do this:
> UPDATE table
> SET datefield = tb.datefield
> FROM loadedfile tb
> However, I recently discovered that they sent us a few bad dates
> (20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
> conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value."
> So, my question is -- how do I go about detecting that the 8-character
> string is not a valid date, while doing it on-the-fly inside the UPDATE
> statement?
>|||Very nice, I like the idea about casting to a full datetime string and
then seeing if it passes a threshold. That should work, I'll give it a
try.
Thanks!