SQL - DateTime conversion error

Question

Hello, 
 I have created the following query, but am getting an annoying message saying "the conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value." (see below screenshot). 
 SELECT S.ITMREF_0, I.ITMDES1_0, S.STOFCY_0, S.LOT_0, S.STA_0, S.USRFLD1_0, CONVERT(DATETIME, S.USRFLD1_0 , 101), (DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0 , 1), GETDATE()))*(-1), ISDATE(S.USRFLD1_0) FROM x3v6.CICPROD.STOCK S LEFT OUTER JOIN ITMMASTER I ON S.ITMREF_0 = I.ITMREF_0 ORDER BY (DATEDIFF(DAY, CONVERT(DATETIME, S.USRFLD1_0 , 1), GETDATE()))*(-1) 
 
 The field I'm trying to convert is an alphanumeric field (20 char) - USRFLD1 ffrom STOCK table, but after running the ISDATE(S.USRFLD1_0) function, I found that there are about 10 random entries that are NOT converting for whatever reason. It makes no sense to me. 
 Does anyone have a work around for this? I would be a critical report for one of our departments to have. I've tried a few different things but to no avail. 
 
 Thank you

Mirabelli7 · Accepted Answer

Hi Bob, 
 
 This is how it runs with the basic stripped down version. As you can see there are a few '0';s that are messing up the query. The field was added as a customized field and added to the index (STO0). I may try using another table and see if it works. Unfortunately, I keep getting that same syntax error with the DECLARE statement. 
 Regards, 
 Mike

Delamater · Answer

Hi Mirabelli7, 
 
I was able to take your code and reproduce a similar problem on my machine using the following change (I didn't have your user field): 
 
DECLARE @usrfield VARCHAR(20) 
SET @usrfield = '01/01/2010' 
 
SELECT S.ITMREF_0, I.ITMDES1_0, S.STOFCY_0, S.LOT_0, S.STA_0, @usrfield , 
CONVERT( 
	DATETIME, 
	@usrfield , 
	101 
), 
( 
	DATEDIFF 
 (DAY, 
 CONVERT(DATETIME, @usrfield , 1), 
 GETDATE()))*(-1), 
 ISDATE(@usrfield 
 ) 
--CAST( @usrfield AS DATETIME) 
FROM DEMO.STOCK S 
LEFT OUTER JOIN DEMO.ITMMASTER I ON S.ITMREF_0 = I.ITMREF_0 
--ORDER BY (DATEDIFF(DAY, CONVERT(DATETIME, @usrfield , 1), GETDATE()))*(-1)

I received error 241, "Conversion failed when converting date and/or time from character string." 
 
I solved that by using a cast instead of a convert. 
There is an implicit conversion chart that might help you here: 
 msdn.microsoft.com/.../ms187928.aspx 
 
You'll notice on that chart that an implicit conversion will work fine when going from nvarchar to datetime. 
 
If you want to alter the date format to code 101, you might try a double cast, first performing the implicit conversion with the cast, then a convert with parameter 101 to control the way the date is displayed. 
 
Good luck, hope this helps. 
 
Bob

Delamater · Answer

Here's an example of what I mean for the cast technique: 
 
DECLARE @usrfield VARCHAR(20) 
SET @usrfield = '03/18/2016' 
 
SELECT 
	S.ITMREF_0, 
	I.ITMDES1_0, 
	S.STOFCY_0, 
	S.LOT_0, 
	S.STA_0, 
	@usrfield, 
	DATEDIFF(DAY, CAST( @usrfield AS DATETIME), GETDATE()-1) 
 
FROM DEMO.STOCK S 
LEFT OUTER JOIN DEMO.ITMMASTER I ON S.ITMREF_0 = I.ITMREF_0

I think I stayed true to your original query needs. 
Hope this helps. 
 
Bob

Sage X3

SQL - DateTime conversion error

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING