• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 62
  • Last Modified:

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
  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.
3 Solutions
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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_aeoAuthor Commented:
  FROM [SMI].[dbo].[rsusr200_20022018_1335] = 625 rows

  SELECT [Fe creac  ]FROM [SMI].[dbo].[rsusr200_20022018_1335]
  WHERE ISDATE([Fe creac  ]) = 0 = 405 rows
Scott PletcherSenior DBACommented:
WHERE CONVERT(datetime, [Fe creac], 104) >=  CAST('01.10.2017' AS datetime)
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

ste5anSenior DeveloperCommented:
You're on SQL Server 2008-? Otherwise you could use TRY_PARSE()/TRY_CAST().

Your query requires the correct date format:

FROM [SMI].[dbo].[rsusr200_20022018_1335]
WHERE CAST([Fe creac  ] AS DATETIME)   >=  '20171001';

Open in new window

Avoid unnecessary casts and specify date literals using an unambiguous format.
Mark WillsTopic AdvisorCommented:
Try using convert  with a style code of 103 or 104

for example :
declare @d varchar(10) = '28.11.2017'
select convert(datetime, @d, 103)

-- where as you get the error code

select cast(@d as datetime)

Open in new window

So, for you
  FROM [SMI].[dbo].[rsusr200_20022018_1335]
  where convert(datetime, [Fe creac  ] ,103)   >=  '20171001'   -- the string '20171001' will be implicitly converted

Open in new window

see : https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql#date-and-time-styles
Vitor MontalvãoMSSQL Senior EngineerCommented:
CONVERT may still return error if it can't convert to a proper date (for example '23.23.2016'). To avoid the query breaking because of conversion errors, use the TRY_CONVERT as it will return NULL when it can't convert to the desired data type.
FROM [SMI].[dbo].[rsusr200_20022018_1335]
WHERE try_convert(datetime, [Fe creac  ], 103)   >=  CAST('01.10.2017' AS datetime)

Open in new window

Mark WillsTopic AdvisorCommented:
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.
select cast('01.10.2017' as datetime)

-- now try a different dmy value

select cast('21.12.2011' as datetime)

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now