We help IT Professionals succeed at work.

The Conversion of a varchar data type to datetime data type resulted in an out-of-range value

22,057 Views
Last Modified: 2014-06-03
The data is stored in the database as col1 = 'yyyymmdd' and col2 = 'hh:mm' CAST(table.col1 + ' ' + table.col2 AS DATETIME) Produces the out of range error at some point. I know it is a date because CAST(col1 AS DATETIME) gives the error but CAST(col2 AS DATETIME) does not.

Tried CASE WHEN ISDATE(col1) = 1 THEN CAST(col1 AS DATETIME) ELSE col1 END but this results in the same error. Ideally I would like to see the problem value(s) if it cannot cast properly.

This is a simple select statement. I cannot do anything about the data except ignore it, convert it or show it as is.

Thanks
Comment
Watch Question

Top Expert 2014

Commented:
You can't mix datatypes in CASE expressions (or at least without taking care that they will implicitly cast OK)

I have tried something similar to your Query but I am getting error, SQL will try to figure out output datatype during which CAST will throw exception at compile.

DECLARE @col1 varchar(50)
DECLARE @col2 varchar(50)

SELECT @col1 = '20140516xxx';  -- wrong date
SELECT @col2 = '08:01';

SELECT DateValue = 
		CASE WHEN (SELECT ISDATE(@col1)) = 1
			THEN CAST(@col1 AS DATETIME)
			ELSE @col1 
		END

Open in new window

when confronted with a statement like the following SQL Server will use datatype precedence to determine what the overall datatype of the expression should be

SELECT CASE WHEN 1=1 THEN 'not-a-date' ELSE getdate() END

Open in new window

For the above datetime has higher precedence than char so it implicitly casts the string to a date which fails.

The folowing succeeds however as sql_variant has a higher precedence

SELECT CASE WHEN 1=1 THEN cast('not-a-date' as sql_variant) ELSE getdate() END

Open in new window

So you can return multiple mixed datatypes that way (I'm not sure how easy sql_variant is to work with though)

Other than that you could return NULL for invalid datetimes instead of returning the nonsense data or cast everything to a string if you must return it.
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Md Shah,

Your solution is perfect. The only thing I was missing was the SELECT ISDATE part. Now how can I select where VaildJoinDate is NULL?

By the way after SELECT *, ValidJoinDate = does not work. I do not need * first of all and when I try to add the = part it is invalid syntax.

My Query is more like
Select
table1.col1
,table2.col1
,CASE WHEN (SELECT ISDATE(table2. col2)) = 1
THEN CAST(blahblah)
ELSE NULL
END AS MyDate

How can I select when MyDate is NULL? It is not recognized as a valid column in my where clause. I ask because you can "easily see" over 10 records which ones are bad. I have 290K records and cannot really.

Author

Commented:
Never mind. I see i need to add the whole case when to the WHERE clause. 290K records and only one was the problem.
Top Expert 2014

Commented:
Hi,

Ok. However, as you can see screenshots of my earlier comment, SELECT *, ValidJoinDate =  has worked for me. Anyways, I am using MS SQL Server 2008 R2 version.

Moreover, if you to want get ValidJoinDate column values which are NULL then you can also opt for below 2 styles apart from one what you have achieved.
/**********************************************************/
SELECT * FROM
	(SELECT *, CASE WHEN (SELECT ISDATE(JoinDate)) = 1
				THEN CAST(JoinDate AS DATETIME)
			ELSE NULL
			END AS ValidJoinDate
		FROM [dbo].[Employee]
	) AS subQ WHERE subQ.ValidJoinDate IS NULL
	
/**********************************************************/
;WITH Tbl AS
	(SELECT *,CASE WHEN (SELECT ISDATE(JoinDate)) = 1
				THEN CAST(JoinDate AS DATETIME)
			ELSE NULL
			END AS ValidJoinDate
		FROM [dbo].[Employee]
	)
SELECT  *
FROM    Tbl
WHERE ValidJoinDate IS NULL

/**********************************************************/

Open in new window

These 2 will also work...

Anyways, Happy that your issue got resolved.

Good luck for further Querying :-)
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
a very handy and relevant feature  of TSQL is "CROSS APPLY"

using CROSS APPLY allows the column alias to be used in both the select and where clauses of a query. e.g.
SELECT
      table1.col1
    , table2.col1
    , CASE
            WHEN col2_isdate = 1
                  THEN CAST(blahblah AS date)
            ELSE NULL
      END AS MyDate
FROM table1
      INNER JOIN table2
            ON table1.x = table2.y
      CROSS APPLY (
            SELECT
                  ISDATE(table2.col2) AS col2_isdate
      ) AS ca1
WHERE col2_isdate = 1

Open in new window

Author

Commented:
Here again Paul I am using your solution instead of the one I marked as the answer. You came in late and the first contrib solved it but I find I am using yours.
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
:) no problem, I'm not trying to poach, it was just a little tip. cheers, Paul
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.