CODEWITHSUNDEEP
sqlsql-serversql-server-2008
George Mihailescu
George Mihailescu

Reputation: 163

TSQL converting varchar to datetime in a nested query

If I am running the following query in SQL

SELECT TOP 1  
CONVERT(datetime,left(LD_publishdate,10),103) 
AS R   
FROM [patch_stats].[dbo].[vulns]  
ORDER BY R DESC

I am getting...

"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

But if I run 

SET language British
SELECT LD_publishdate 
FROM [patch_stats].[dbo].[vulns]
WHERE ISDATE (LD_publishdate) = 1

Then only the correct values are selected.

How can I insert (nest in) the second query into the first one, so only the correct results are converted?

Upvotes: 1

Views: 117

Answers (2)

Deepshikha
Deepshikha

Reputation: 10264

You can merge the two queries as:

SET language British
SELECT  
CONVERT(datetime,left(LD_publishdate,10),103) 
AS R   
FROM(
SELECT LD_publishdate 
FROM [patch_stats].[dbo].[vulns]
WHERE ISDATE (LD_publishdate) = 1
) T 
ORDER BY R DESC

But please note that ISDATE will return 0 if the expression is a datetime2 value ( which i think is the case here), but will return 1 if the expression is a valid datetime value.

DEMO

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172448

You need to stop storing dates in strings. You need to store the dates as date to avoid such kind of problem.

You can try to set 

SET DATEFORMAT DMY;

Upvotes: 1

Related Questions