I've just had a view set up for querying analytical data. Here is the code:
SELECT [Log Date] AS PI_TIMESTAMP, [Plant Area] + [Product] + [Process(Mfg)] + [Sample Point] +
[Test Name] + '_RESULT' AS ALIAS, [Result_Clean] AS PI_VALUE, 0 AS PI_STATUS
FROM dbo.vw_STARLIMS_PI
WHERE [Log Date] > ? AND
([Sample Status] = 'Done' OR
[Sample Status] = 'OOS' OR
[Sample Status] = 'Released' OR
[Sample Status] = 'Upload')
ORDER BY 1 ASC;
I'm getting the following error.
Msg 242, Level 16, State 3, Line 1 The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
Here is the [Log Date] format. The SQL database is using mdy.
2021-12-30 12:10:00.000
Any idea on what to try first? I would assume there are some incorrect Log Date entries that won't convert to this datetime format, but I'm not sure how to address the issue in that case. I am not sure what other possibilities there are to cause this error. I am fairly green in this field.
EDIT: Here is an excerpt from the view's code relating to log time formatting:
CAST(CONVERT(VARCHAR(24), LOGDATE, 101) + ' ' + LOGTIM AS DATETIME ) AS [Log Date]
The ? in the query are placeholders for the last scan time (LST). Looking at the documentation, it appears to be SQL_Timestamp data type.
Data Types in dbo.vw_STARLIMS_PI
Data Types of LOGTIM and LOGDATE that make up Log Date
It appears the time portion of the Log Date is converted from nvarchar when the view is created. LOGTIM appears to be the cause of the error.