Video Screencast Help

Workflow - Problem reading date from MS SQL

Created: 09 Oct 2012 • Updated: 10 Oct 2012 | 6 comments
This issue has been solved. See solution.

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.

 

Comments 6 CommentsJump to latest comment

nealman's picture

(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.

Halley1's picture

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.

SOLUTION
reecardo's picture

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.

nealman's picture

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?

 

reecardo's picture

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

nealman's picture

That was it!   Thanks reecardo, and thanks Halley1.