ビデオヘルプ

Workflow - Problem reading date from MS SQL

作成: 09 Oct 2012 • 更新: 10 Oct 2012 | コメント数: 6
この問題は解決されました。 ソリューションを参照してください。

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.

コメント コメント数: 6最新のコメントを表示

nealman の写真

(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 の写真

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.

ソリューション
reecardo の写真

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 の写真

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 の写真

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 の写真

That was it!   Thanks reecardo, and thanks Halley1.