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.
enrique_aeoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

0
enrique_aeoAuthor Commented:
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
0
Scott PletcherSenior DBACommented:
WHERE CONVERT(datetime, [Fe creac], 104) >=  CAST('01.10.2017' AS datetime)
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Your query requires the correct date format:

SET DATEFORMAT DMY;
SELECT*
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.
0
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
SELECT*
  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
0
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.
SELECT *
FROM [SMI].[dbo].[rsusr200_20022018_1335]
WHERE try_convert(datetime, [Fe creac  ], 103)   >=  CAST('01.10.2017' AS datetime)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.