Posted Fri, 23 Oct 2020 01:09:50 GMT by

I have a weird problem already, and this is my 1st day working with viewpoint. 


I have a field in an MSSQL 2012 database that contains a date and shift.   YYYYMMDDS, SO 202010220 to represent today and 1st shift. This shift is 0 based.  In the database, the field type is int, but when I pull it into viewpoint designer, it shows as binary. What I'm trying to do is build a derived field with the expression builder to convert to char, extract the last character, cast back to int so I can add 1 to it to arrive at the correct numbered shift.  There has to be a way to do this.  Any help would be greatly appreciated.

Posted Fri, 23 Oct 2020 01:37:04 GMT by

Hi Eric,

You can accomplish this by doing the following:

On the SQL tab of your view in design mode:

CONVERT(char(10), field) as newfield  <----you can change the length to whatever is appropriate for your task, and field is the actual field you want to convert. You can name it anything you would like. I am using newfield for the example.

You can then nest this function within the SUBSTRING function. 

Hope this helps!

Thank you! 
Jackie D/Support Analyst

You must be signed in to post in this forum.