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

Hi Bob

Thank you for the response. I used your outlined solution using Declare and Set operators, with the result:

DECLARE S.USRFLD1_0 VARCHAR(20)

SET S.USRFLD1_0 = '01/01/2010'

SELECT S.ITMREF_0, I.ITMDES1_0, S.STOFCY_0, S.LOT_0, S.STA_0, S.USRFLD1_0,

CONVERT(DATETIME, S.USRFLD1_0 , 1),(DATEDIFF(DAY,

CONVERT(DATETIME, S.USRFLD1_0 , 1), GETDATE()))*(-1), ISDATE(S.USRFLD1_0)

CAST(S.USRFLD1_0 AS DATETIME)

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)

However, I am getting "Incorrect syntax near '.' "

I am not sure of where I went wrong here. I think maybe in the Declare or Set statement. And I assume when you mean use 'CAST' instead of 'CONVERT' you mean to replace wherever it says CONVERT in the query with CAST..AS.. right?

Thank you.

Hi,

I mentioned "I solved that by using a cast instead of a convert. "
So yes, I agree with your clarification, use a CAST instead of a CONVERT.

I'll take a look at your syntax right now and post back in a few.

Do note the commented line above in my example:

--CAST( @usrfield AS DATETIME)

That's the syntax I used to get around the problem I was facing. Maybe it will work in your circumstance. If you could post an example of what the data looks like that you are casting (the records it fails on), that'd be nice too. 

Cheers,
Bob

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

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