SQL Server - Converting to smalldate

Asked By SAC on 25-Feb-07 07:47 AM
I'm still having problems convery a char field to a small date.  The char
field has something like '20070216' in it and I want to convert it to a
small date.  Currently I have:

UPdate TWDHPO
Set
TWDHPO.ScheduledDateTarget =
CONVERT(varchar(10),CONVERT(smalldatetime,TWDHPO.PDDTE),101)

I get and arithmetic overflow.

When I substitute '20070216' for TWDHPO.PDDTE it works.

Thanks for the help.




David Markle replied on 25-Feb-07 08:46 AM
If your char column just has YYYYMMDD in it, SQL Server will implicitly
cast it with no problems.  Alternatively, you could use:

UPdate TWDHPO
Set
TWDHPO.ScheduledDateTarget = CAST(TWDHPO.PDDTE AS SMALLDATETIME)
...
Anthony Thomas replied on 01-Mar-07 09:48 AM
If it works with a literal string but not the TWDHPO.PDDTE, then some value
in that column is outside of the acceptable range allowable by a
SMALLDATETIME data type.

Try this:

SELECT
CONVERT(VARCHAR(10), CONVERT(SMALLDATETIME, t1.PDDTE, 112), 101) AS
SmallDT
,CONVERT(VARCHAR(10), CONVERT(DATETIME, t1.PDDTE, 112), 101) AS FullDT

FROM
dbo.TWDHPO AS t1 WITH(NOLOCK)

If you still get the error message, then the PDDTE data has out of range
data.  To find it, run the following:

SELECT
t1.*

FROM
dbo.TWDHPO AS t1 WITH(NOLOCK)

WHERE
ISDATE(t1.PDDTE) = 0
OR NOT(
DATELENGTH(t1.PDDTE) = 8
AND CAST(LEFT(t1.PDDTE, 4) AS NUMERIC(4, 0)) >= 1900.
AND CAST(SUBSTRING(t1.PDDTE, 5, 2) AS NUMERIC(2, 0)) BETWEEN 1. AND
12.
AND CAST(RIGHT(t1.PDDTE, 2) AS NUMERIC(2, 0)) BETWEEN 1. AND 31.
)

Now, if this doesn't find the bad data, then you have something like 2/29
for a non-leap year, like 1900, which is an odd 400 year where the normal
4-year leap is skipped, 9/31, which doesn't exist.

You might also have NULL or '' (empty string) values, which will also cause
you conversion problems, but the query above should catch those instances.

Sincerely,


Anthony Thomas


--