July 21, 2017 at 1:46 pm
While trying to convert a varchar field to a date I am getting the error "conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value."
There are no null or blank values in the table but there must be dates that were manually entered incorrectly such as 2/30/2017. There are too many rows to scan manually so I am trying to design a query that will identify the row that is failing and return just that.
Has anyone ever written such a query that can find a value that is not a valid date or time?
July 21, 2017 at 1:58 pm
yoyodynedata - Friday, July 21, 2017 1:46 PMWhile trying to convert a varchar field to a date I am getting the error "conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value."
There are no null or blank values in the table but there must be dates that were manually entered incorrectly such as 2/30/2017. There are too many rows to scan manually so I am trying to design a query that will identify the row that is failing and return just that.
Has anyone ever written such a query that can find a value that is not a valid date or time?
Sure. Check out the TRY_PARSE function, selecting rows where the result is NULL.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 21, 2017 at 2:19 pm
where isdate(col) = 0
August 22, 2017 at 10:59 pm
karl 93387 - Friday, July 21, 2017 2:19 PMwhere isdate(col) = 0
careful using isdate when checking to see if a value can be cast to smalldatetimeDECLARE @dt VARCHAR(20);
SET @dt = '1753-01-01 03:00:00';
SELECT ISDATE(@dt); -- returns 1 since value can be cast to a datetime
SELECT CAST(@dt AS SMALLDATETIME); -- fails with conversion error
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 23, 2017 at 11:03 am
use TRY_CONVERT. for invalid date, you'll get NULL
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply