asked on

date time > 01.10.2017 an out-of-range value.

hi experts

the column store [Fe creac ]

Column 0	Usuarios    	Grupo	Tipo          	Creado por  	Fe creac  	Válido de 	Fin valid 	Entr sist 1  	Entr sist 2	Clave acc 1	Clave acc 2	Bloqueo1	Bloqueo2                                       	EntrSisInc	Column 15
	A_MATERIALES	     	A Diálogo     	ATI         	14.11.2016	          	          	20.02.2018  	07:18:10  	          	15.11.2017	       	                                              	          	
	AABARCA     	     	A Diálogo     	SMIADMIN    	31.01.2012	          	          	17.02.2018  	10:12:30  	          	18.12.2017	       	                                              	          	
	AAGUILAR    	     	A Diálogo     	SMIADMIN    	28.11.2011	          	          	19.02.2018  	11:01:50  	          	15.12.2017	       	                                              	          	
	AAROCA      	     	A Diálogo     	SMIADMIN    	13.12.2013	          	          	20.02.2018  	08:12:10  	          	06.02.2018	       	                                              	          	
	ABALLADARES 	     	A Diálogo     	ATI         	17.07.2017	          	          	04.08.2017  	18:32:07  	          	18.07.2017	       	Responsable

Open in new window

this query has errors
SELECT*
FROM [SMI].[dbo].[rsusr200_20022018_1335]
where CAST([Fe creac ] AS datetime) >= CAST('01.10.2017' AS datetime)

Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Jim Horn

For starters what's a [Fe creac ], and if the trailing space supposed to be there, as it's not clear from your data model.

Just for kicks and giggles, run this and see if there are any [Fe creac] values that cannot be converted to a datetime, and if yes figure out how to handle them.

SELECT [Fe creac  ]
FROM [SMI].[dbo].[rsusr200_20022018_1335] 
WHERE ISDATE([Fe creac  ]) = 0

Open in new window

enrique_aeo

ASKER

SELECT*
FROM [SMI].[dbo].[rsusr200_20022018_1335] = 625 rows

SELECT [Fe creac ]FROM [SMI].[dbo].[rsusr200_20022018_1335]
WHERE ISDATE([Fe creac ]) = 0 = 405 rows
experts.png

Scott Pletcher

WHERE CONVERT(datetime, [Fe creac], 104) >= CAST('01.10.2017' AS datetime)

SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Wills

Note need to be on SQL2012 for TRY_CONVERT : https://docs.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql

AND PLEASE NOTE depending on your DB settings:

You must not do : cast('01.10.2017' as datetime) -- it will give you 10th Jan 2017, not 1st Oct 2017

Which is part of the reason why you get that error - use '20171001' instead.
e.g.

select cast('01.10.2017' as datetime)

-- now try a different dmy value

select cast('21.12.2011' as datetime)

Open in new window

date time &gt; 01.10.2017 an out-of-range value.

date time > 01.10.2017 an out-of-range value.