sql - Convert String to Datetime Error -


this question has answer here:

i writing query poll 1 of our devices , report status our solarwinds server. semi-advanced sql query, results come out correct error message:

msg 242, level 16, state 3, line 1 conversion of nvarchar data type datetime data type resulted in out-of-range value.

i new sql i'm not sure resolve. code block 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)) 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()) timediff nodes n   join (select ca.nodeid, cs.status [load]      custompollerstatus cs  join custompollerassignment ca on (cs.custompollerassignmentid=ca.custompollerassignmentid) join custompollers cp on ca.custompollerid=cp.custompollerid      cp.uniquename='snapmirrorsrc'      ) tbl2 on n.nodeid =  tbl2.nodeid  join (select ca.nodeid, cs.status [destination]     custompollerstatus cs  join custompollerassignment ca on (cs.custompollerassignmentid=ca.custompollerassignmentid) join custompollers cp on ca.custompollerid=cp.custompollerid cp.uniquename='snapmirrordst' ) tbl3 on n.nodeid = tbl3.nodeid  join (select ca.nodeid, cs.status      custompollerstatus cs  join custompollerassignment ca on (cs.custompollerassignmentid=ca.custompollerassignmentid)      join custompollers cp on ca.custompollerid=cp.custompollerid      cp.uniquename='snapmirrorstate'      ) tbl1 on n.nodeid = tbl1.nodeid  join (select ca.nodeid, cs.status custompollerstatus cs  join custompollerassignment ca on (cs.custompollerassignmentid=ca.custompollerassignmentid)  join custompollers cp on ca.custompollerid=cp.custompollerid cp.uniquename='snapmirrormirrortimestamp' ) tbl4 on n.nodeid = tbl4.nodeid  tbl1.status 'unk%'

here results:

enter image description here

however error: msg 242, level 16, state 3, line 1 conversion of nvarchar data type datetime data type resulted in out-of-range value.

so results come out correct, im getting error. not sure issue is. kindly appreciated.

****--- edit ---****

so changed code bit,however, in 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 '[a-z][a-z][a-z] %[1-9][1-9][1-9][1-9]'  convert(datetime, substring (tbl4.status,5,3) + ' ' +    substring(tbl4.status,9,2) + ' , ' +  substring(tbl4.status,25,4) + ' ' +    substring(tbl4.status,11,6))  else '' end [date],   case  when tbl4.status '[a-z][a-z][a-z] %[1-9][1-9][1-9][1-9]'  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 'time since last snap' nodes n   join (select ca.nodeid, cs.status [load]    custompollerstatus cs    join custompollerassignment ca on     (cs.custompollerassignmentid=ca.custompollerassignmentid)    join custompollers cp on ca.custompollerid=cp.custompollerid    cp.uniquename='snapmirrorsrc'    ) tbl2 on n.nodeid =  tbl2.nodeid  join (select ca.nodeid, cs.status [destination] custompollerstatus cs  join custompollerassignment ca on    (cs.custompollerassignmentid=ca.custompollerassignmentid) join custompollers cp on ca.custompollerid=cp.custompollerid cp.uniquename='snapmirrordst' ) tbl3 on n.nodeid = tbl3.nodeid  join (select ca.nodeid, cs.status custompollerstatus cs  join custompollerassignment ca on     (cs.custompollerassignmentid=ca.custompollerassignmentid)  join custompollers cp on ca.custompollerid=cp.custompollerid cp.uniquename='snapmirrormirrortimestamp' ) tbl4 on n.nodeid = tbl4.nodeid

there perhaps error retrieving year part: try substring(tbl4.status,22,4) instead of substring(tbl4.status,25,4)


-

Comments

Post a Comment

Popular posts from this blog

angular - Is it possible to get native element for formControl? -

i've got angular2 reactive form . created formcontrol s , assigned input fields by [formcontrol]=... . understand creates nativeelement <-> formcontrol link. my question: possible nativeelement formcontrol ? wanna myformcontrol.nativeelement.focus() i can share 1 terrible solution works me. in reactive forms can use either 1) formcontroldirective ts mycontrol = new formcontrol('') template <input type="text" [formcontrol]="mycontrol"> or 2) formcontrolname ts myform: formgroup; constructor(private fb: formbuilder) {} ngoninit() { this.myform = this.fb.group({ foo: '' }); } template <form [formgroup]="myform"> <input type="text" formcontrolname="foo"> </form> so these directives write patch like 1) formcontroldirective const originformcontrolngonchanges = formcontroldirective.prototype.ngonchanges; formcontroldirective.proto
Read more

unity3d - Rotate an object to face an opposite direction -

i have object , b. when click on object b, rotates object b (a faces b). b doesn't face a. need following : when faces b, need face opposite direction. have code rotating @ b. how rotate face opposite direction after that? vector3 targetdirection = target - transform.position; float step = speed * time.deltatime; vector3 newdirection = vector3.rotatetowards (turretdome.transform.forward, targetdirection, step, 0.0f); turretdome.transform.rotation = quaternion.lookrotation (newdirection); you object facing object b, want inverse direction of object after that? objecta.transform.rotation = quaternion.inverse(objecta.transform.rotation) but lets assume example turretdome object a, (negate direction): turretdome.transform.rotation = quaternion.lookrotation (-newdirection); naturally, both of these snippets not show how smoothen rotation, seem know how use time.deltatime. just incase unsure, quaternion.lerp this
Read more

javascript - Why jQuery Select box change event is now working? -

i working on product filters user select select box according his/her preference not getting selected value user in jquery. html code:- <select name="product_filter" id="product_filter"> <option value="price_low_first">price : low high</option> <option value="price_high_first">price : high low</option> <option value="latest">latest</option> <option value="popular">most popular</option> </select> jquery code: $(function () { $("#product_filter").change(function () { alert("hi") var selectedtext = $(this).find("option:selected").text(); var selectedvalue = $(this).val(); alert("selected text: " + selectedtext + " value: " + selectedvalue); }); }); hi believe code perfect. once please check jquery.min.js
Read more