Home > Calculated Fields, General - Builders > Date & Time fields in GP Builders

Date & Time fields in GP Builders

Typically – most fields in GP are Date fields.   GP doesn’t have many fields where time is stored.  

Side note – Many of you know that GP added a time stamp field into many GP tables for version GP10 (field – DEX_ROW_TS in SQL).  But unfortunately – this field in NOT available in SmartList builders (hint, hint, GP programmers – that might be nice….) Unless you connect to the GP table like it is another SQL table…

A little background is necessary…  Actually in SQL all GP date fields are actually date/time fields.  It is just that the time is always 12:00 am.   This assumption is built into SmartList and the other builders.  In the field formatting you can only select formats with dates, not with times. 

This can cause issues when you are working with data in the Builders from other SQL data sources.  SmartList builder assumes that the field is just a date field and only gives you those formats.  That seems OK, until your users try to do searches on these fields like they are used to in GP.  Such as equal to 07/26/2010 … if there is actually a time in this field then the users will not get what they expect (chances are no records will be returned – because the search is actually looking for 07/26/2010 12:00 am !)

To prevent this and make the date/time field work like date field in GP, make a new calculated field with a Field Type of Date and the following calculation:
CAST(FLOOR( CAST( {table.field} AS FLOAT ) ) AS DATETIME)

If you want the users to see the time from this orginal field, you will need to add another calculated field use as Field Type of String and the following calculation:
CONVERT(CHAR(8), {table.field}  , 108)

Now, if you SQL people have better functions, please let me know (I don’t begin to claim much of a SQL background) – but these have worked just fine for me.

Hopefully, this trick will come in handy the next time you are using the Builders to view data from other SQL sources.  (Which is a great feature of the Builders!)  If you have other SQL data that your people need and use, why aren’t you leveraging the Builders to see that data in GP?  But that is whole other subject entirely.

Happy Building,
Devon

  1. July 27, 2010 at 3:16 am

    Have a read of this post. There are some “Gotchas” when converting dates.

    http://blogs.msdn.com/b/developingfordynamicsgp/archive/2010/06/23/smartlist-builder-based-on-sql-view-not-returning-data.aspx

    David

    • July 27, 2010 at 1:57 pm

      Dave has a good point in his post… My calculated field statements above are only tested in Builder’s themselves – not out in SQL.

      Thanks, Dave!

  1. July 27, 2010 at 3:08 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s