Workflow and ServiceDesk Community

 View Only
  • 1.  Workflow - Problem reading date from MS SQL

    Posted Oct 09, 2012 05:08 PM

    The row in my ms sql table is type "date"

    When I read the data into a workflow using an SQL table generator, the dates come in with a time attached to them (12:00 am).  I don't the time on them, only the date.

    So, apparently I have set something up incorrectly, but I have not been able to figure it out yet.

    Any suggestions on where to look?   Let me know if additional information is needed.

     



  • 2.  RE: Workflow - Problem reading date from MS SQL

    Posted Oct 09, 2012 05:10 PM

    (Typo in the original message...corrected below)

    The row in my ms sql table is type "date"

     

    When I read the data into a workflow using an SQL table generator, the dates come in with a time attached to them (12:00 am).  I don't want the time on them, only the date.

    So, apparently I have set something up incorrectly, but I have not been able to figure it out yet.

    Any suggestions on where to look?   Let me know if additional information is needed.



  • 3.  RE: Workflow - Problem reading date from MS SQL
    Best Answer

    Posted Oct 09, 2012 05:39 PM

    Hi. Workflow imports data with sql types of 'date' and 'datetime' as the .NET type called DateTime, since there is no native type of Date in .NET that does not have the time component. Instead, .NET provides native tools for formatting a date, such that undesired components can be hidden. Workflow exposes this formatting capability to the UI in various places where dates might be displayed.

    So the solution is to format the date for display in such a way that the time component is stripped. If you're displaying this inside a formbuilder component in Workflow, then have a look at the formatting options in the AsciiMergeLabel. If you choose Date Formatter and drag out your date field to the canvas, then select it, you'll see options for formatting your date appear on the left.

    If you're displaying the date elsewhere, like in a report in the processmanager portal, check the formatting options for the column inside the report.



  • 4.  RE: Workflow - Problem reading date from MS SQL

    Posted Oct 10, 2012 08:28 AM

    Optionally, you can do this all in your SQL statement:

    SELECT

    ...

    CONVERT(VARCHAR(10), date, 101) as formatted_date,

    ...

    Kind of the long way, but it's an option. Your dates are essentially treated like strings at this point.



  • 5.  RE: Workflow - Problem reading date from MS SQL

    Posted Oct 10, 2012 10:05 AM

    Great advice.  I got it to drop the time in the Form builder with AsciiMergeLabel as suggested by Halley1.

    I am now attempting to do the same in the grid component ins

    If I open the Column Configuration for one of my columns, there is a "Format:" field.

    I tried MM/DD/YYYY, and it showed 08/DD/YYYY

    I tried MM-DD-YYYY and it shows 08-DD-YYYY

    I tried "Date" and it didn't show any part of the date.  

    So far, I haven't been able to find any documentation on the "Format:" property of Column Configuration in the grid component.

    Do you know how to do this?  Or, do you know where I can get documentation on how to use the Format property of the Column Configuration interface?

     



  • 6.  RE: Workflow - Problem reading date from MS SQL

    Posted Oct 10, 2012 10:10 AM

    Format is probably going to match the C# date format documentation.

    See this for more info: http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx

    Your d's and y's up above just need to be lowercase



  • 7.  RE: Workflow - Problem reading date from MS SQL

    Posted Oct 10, 2012 10:49 AM

    That was it!   Thanks reecardo, and thanks Halley1.