Convert String to Datetime Error

Question

I am writing a query to poll one of our devices and report back to the status to our Solarwinds server. It is a semi-advanced SQL Query, my results come out correct but I get this error message:

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.

I am new to SQL so I'm not sure what to do to resolve. My code block is below:

SELECT tbl2.Load [Source], tbl3.Destination [Destination], tbl1.Status [Status], tbl4.Status [TimeStamp],
CONVERT(datetime, SUBSTRING (tbl4.Status,5,3) + ' ' + SUBSTRING(tbl4.Status,9,2) + ' , ' +  SUBSTRING(tbl4.Status,25,4) + ' ' + SUBSTRING(tbl4.Status,11,6)) AS DATE, 
DATEDIFF(HOUR,CONVERT(datetime, SUBSTRING (tbl4.status,5,3) + ' ' + SUBSTRING(tbl4.status,9,2) + ' , ' +  SUBSTRING(tbl4.status,25,4) + ' ' + SUBSTRING(tbl4.status,11,6)) , GETDATE()) AS Timediff
FROM Nodes n 

join (SELECT ca.nodeid, cs.Status [Load]
     FROM CustomPollerStatus cs 
JOIN CustomPollerAssignment ca ON (cs.CustomPollerAssignmentID=ca.CustomPollerAssignmentID)
JOIN CustomPollerS cp ON ca.CustomPollerID=cp.CustomPollerID
     where cp.UniqueName='SnapMirrorSrc'
     ) tbl2 on n.Nodeid = 
tbl2.NodeID

join (select ca.nodeid, cs.Status [Destination]
    FROM CustomPollerStatus cs 
JOIN CustomPollerAssignment ca ON (cs.CustomPollerAssignmentID=ca.CustomPollerAssignmentID)
JOIN CustomPollerS cp ON ca.CustomPollerID=cp.CustomPollerID
where cp.UniqueName='snapmirrordst'
) tbl3 on n.Nodeid = tbl3.NodeID

join (SELECT ca.nodeid, cs.Status
     FROM CustomPollerStatus cs 
JOIN CustomPollerAssignment ca ON (cs.CustomPollerAssignmentID=ca.CustomPollerAssignmentID)
     JOIN CustomPollerS cp ON ca.CustomPollerID=cp.CustomPollerID
     WHERE cp.UniqueName='SnapMirrorState'
     ) tbl1 on n.Nodeid = tbl1.NodeID

join (select ca.nodeid, cs.Status
from CustomPollerStatus cs 
join CustomPollerAssignment ca ON (cs.CustomPollerAssignmentID=ca.CustomPollerAssignmentID) 
JOIN customPollerS cp on ca.CustomPollerID=cp.CustomPollerID
WHERE cp.UniqueName='snapmirrorMirrorTimestamp'
) tbl4 on n.NodeID = tbl4.NodeID

where tbl1.Status like 'unk%'

Here are my results:

However I get this 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.

So my results come out correct, but Im getting this error. Not sure what the issue is. Any help would be kindly appreciated.

****--- EDIT ---****

So I changed my code a bit,however, now in the DATE Column im getting 1900-01-01 00:00:00.000

SELECT tbl2.Load [Source], tbl3.Destination [Destination], --tbl1.Status    [Status], 
tbl4.Status [TimeStamp],

CASE 
WHEN tbl4.status LIKE '[A-Z][A-Z][A-Z] %[1-9][1-9][1-9][1-9]' THEN 
CONVERT(datetime, SUBSTRING (tbl4.Status,5,3) + ' ' +    SUBSTRING(tbl4.Status,9,2) + ' , ' +  SUBSTRING(tbl4.Status,25,4) + ' ' +    SUBSTRING(tbl4.Status,11,6)) 
ELSE '' END AS [DATE], 

CASE 
WHEN tbl4.status LIKE '[A-Z][A-Z][A-Z] %[1-9][1-9][1-9][1-9]' THEN 
CAST (DATEDIFF(HOUR,CONVERT(datetime, SUBSTRING (tbl4.status,5,3) + ' ' +     SUBSTRING(tbl4.status,9,2) + ' , ' +  SUBSTRING(tbl4.status,25,4) + ' ' +     SUBSTRING(tbl4.status,11,6)) , GETDATE())AS bigint)
ELSE '' END AS 'Time Since Last Snap'
FROM Nodes n 

join (SELECT ca.nodeid, cs.Status [Load]
   FROM CustomPollerStatus cs 
  JOIN CustomPollerAssignment ca ON     (cs.CustomPollerAssignmentID=ca.CustomPollerAssignmentID)
   JOIN CustomPollerS cp ON ca.CustomPollerID=cp.CustomPollerID
   where cp.UniqueName='SnapMirrorSrc'
   ) tbl2 on n.Nodeid = 
tbl2.NodeID

join (select ca.nodeid, cs.Status [Destination]
FROM CustomPollerStatus cs 
JOIN CustomPollerAssignment ca ON    (cs.CustomPollerAssignmentID=ca.CustomPollerAssignmentID)
JOIN CustomPollerS cp ON ca.CustomPollerID=cp.CustomPollerID
where cp.UniqueName='snapmirrordst'
) tbl3 on n.Nodeid = tbl3.NodeID

join (select ca.nodeid, cs.Status
from CustomPollerStatus cs 
join CustomPollerAssignment ca ON     (cs.CustomPollerAssignmentID=ca.CustomPollerAssignmentID) 
JOIN customPollerS cp on ca.CustomPollerID=cp.CustomPollerID
WHERE cp.UniqueName='snapmirrorMirrorTimestamp'
) tbl4 on n.NodeID = tbl4.NodeID

Try doing a SELECT without the conversion to DATETIME and see if any values are not valid dates. — D Stanley, Sep 22 '16 at 14:17
Also, instead of putting your whole SELECT, provide only those parts that are causing the issue (i.e. related to the field in question). — FDavidov, Sep 22 '16 at 14:21
Your picture shows the status column filled with the value 'unknown'. Is this tbl4.Status you are trying to convert to a datetime? — openshac, Sep 22 '16 at 14:24
@openshac No the unknown is a different value. Im trying to convert the TimeStamp column because it's just a text string not a correct datetime and I want to alert on that date time if the Timediff column is larger than 6. — jamie hutchings, Sep 22 '16 at 15:15
I should've said - Im trying to convert the string TimeStamp into datetime and dump it into the DATE column properly formatted — jamie hutchings, Sep 22 '16 at 15:34

openshac · Answer 1 · 2016-09-22T16:29:46.127

It looks like you have some invalid data in the tbl4.Status column.

To identify the invalid data temporarily modify the select and where clause statement to:

select tbl4.Status, *
...
where isdate(SUBSTRING (@status,5,3) 
        + ' ' + SUBSTRING(@status,9,2)
        + ' , ' +  SUBSTRING(@status,25,4) 
        + ' ' + SUBSTRING(@status,11,6)) = 0

EDIT:

Your case statement

CASE
ELSE '' END AS [DATE]

Will always return '1900-01-01 00:00:00.000' when it falls into the ELSE part. This indicates an invalid date.

With the edit you have made above simply add tbl4.Status to your select and add then identify the invalid rows by adding this to you where clause:

[DATE] = '1900-01-01 00:00:00.000'

This will identify the incorrect status rows.

Hi, see my edited comment above – jamie hutchings Sep 22 '16 at 16:11 — jamie hutchings, Sep 22 '16 at 16:11

score 0 · Answer 2 · answered Sep 22 '16 at 16:02

0

there is perhaps an error for retrieving the year part: try with SUBSTRING(tbl4.status,22,4) instead of SUBSTRING(tbl4.status,25,4)

answered Sep 22 '16 at 16:02

Didier68

1,027
12
26

Convert String to Datetime Error

2 Answers2