{"id":109207,"date":"2018-11-14T05:59:43","date_gmt":"2018-11-14T13:59:43","guid":{"rendered":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/power-apps\/working-with-time-columns-in-sql-server\/"},"modified":"2025-06-11T08:06:08","modified_gmt":"2025-06-11T15:06:08","slug":"working-with-time-columns-in-sql-server","status":"publish","type":"post","link":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/power-apps\/working-with-time-columns-in-sql-server\/","title":{"rendered":"Working with Time columns in SQL Server"},"content":{"rendered":"<p><em>This post shows some expressions that we needed to use to consume SQL Server <a href=\"https:\/\/docs.microsoft.com\/sql\/t-sql\/data-types\/time-transact-sql\">time columns<\/a> &#8211; and it was fairly complex. We have since released a new feature in PowerApps that makes this easier. Take a look at the <a href=\"https:\/\/powerapps.microsoft.com\/en-us\/blog\/more-regular-expressions-for-canvas-apps\/\">announcement for the new regular expression functions<\/a> for more information.<\/em><\/p>\n<p>When we use <a href=\"https:\/\/docs.microsoft.com\/sql\/t-sql\/data-types\/time-transact-sql\">time columns in SQL Server<\/a> and try to use them in PowerApps, we don&#8217;t get a time value that we can use directly &#8211; instead, we get a text (string) type, with an encoding of the time that is not very user-friendly:<\/p>\n<p><img decoding=\"async\" alt=\"App showing time columns from SQL Server in the duration ISO 8601 format\" src=\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-content\/uploads\/2018\/11\/BlogPost201811-001.png\" style=\"width: 334px; height: 379px;\"\/><\/p>\n<p>This is currently a missing feature in PowerApps &#8211; time columns should have a better support (since there is a Time type in PowerApps), so while this is not addressed, I&#8217;ll show in this post a way to work with the time columns today.<\/p>\n<h2>The problem<\/h2>\n<p>A few months ago I wrote about <a href=\"https:\/\/powerapps.microsoft.com\/en-us\/blog\/working-with-datetime-values-in-sql\/\">working with date\/time values in SQL<\/a>, as there are a couple of different ways to represent those values. Time columns have a similar issue &#8211; they can represent two different concepts:<\/p>\n<ul>\n<li>A certain time in day: school starts at 08:25, ends at 14:55; tea will be served at 17:00.<\/li>\n<li>A duration of an event: NBA basketball quarters last for 12 minutes; the current marathon record is 2 hours, 1 minute and 29 seconds.<\/li>\n<\/ul>\n<p>The SQL time column type can be used for both concepts, so we don&#8217;t have a semantic problem this time. Instead, the issue arises because of a mismatch between the SQL connector and PowerApps &#8211; the former transmits time values as durations (in the <a href=\"https:\/\/en.wikipedia.org\/wiki\/ISO_8601#Durations\">ISO 8601 duration format<\/a>), while the latter only accepts absolute time values over the wire. This issue will likely be addressed in an upcoming release, but since we may have apps that rely on the current behavior (time columns returned as string values), we can&#8217;t just change it to return time values or those apps would be broken.<\/p>\n<h2>Converting between the duration format and a time value<\/h2>\n<p>There&#8217;s currently no built-in function that can be used to convert the duration format and a time value in PowerApps, so we&#8217;ll use some string manipulation functions for that. If all you want from this post is an expression that does that, then here you go (this assumes that the time value can be referenced as <code>ThisItem.Time<\/code>):<\/p>\n<pre>\u00a0 \u00a0 Time(\n\u00a0 \u00a0 \u00a0 \u00a0 If(\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 IsBlank(Find(\"H\", ThisItem.Time)), \/\/ Check if there is an hour component\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0,\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\/\/ If not, the value is zero\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Value(\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\/\/ Otherwise, take the substring between 'PT' and 'H'\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Mid(\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ThisItem.Time,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 3,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Find(\"H\", ThisItem.Time) - 3))),\n\u00a0 \u00a0 \u00a0 \u00a0 If(\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 IsBlank(Find(\"M\", ThisItem.Time)), \/\/ Check if there is a minute component\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0,\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\/\/ If not, the value is zero\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Value(\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\/\/ Otherwise take the substring between the rest of the value\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Mid(\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\/\/\u00a0 \u00a0 \u00a0after the hour component and the 'M' indicator\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ThisItem.Time,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 If(\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 IsBlank(Find(\"H\", ThisItem.Time)),\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 3,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Find(\"H\", ThisItem.Time) + 1),\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Find(\"M\", ThisItem.Time) -\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 If(\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 IsBlank(Find(\"H\", ThisItem.Time)),\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 3,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Find(\"H\", ThisItem.Time) + 1)))),\n\u00a0 \u00a0 \u00a0 \u00a0 If(\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 IsBlank(Find(\"S\", ThisItem.Time)), \/\/ Check if there is a second component\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0,\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\/\/ If not, the value is zero\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Value(\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\/\/ Otherwise take the substring after the minute or hour indicator,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Substitute(\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \/\/\u00a0 \u00a0 \u00a0remove the 'S' indicator, then take the value\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Mid(\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ThisItem.Time,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 If(\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 !IsBlank(Find(\"M\", ThisItem.Time)),\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Find(\"M\", ThisItem.Time) + 1,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 !IsBlank(Find(\"H\", ThisItem.Time)),\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Find(\"H\", ThisItem.Time) + 1,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 3)),\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"S\",\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"\"))))\n<\/pre>\n<p>Or if you want to display it in a certain format, you can use it inside a <a href=\"https:\/\/docs.microsoft.com\/powerapps\/maker\/canvas-apps\/functions\/function-text\">Text expression<\/a>\u00a0that converts it to what you want, such as<\/p>\n<pre>\u00a0 \u00a0 Text(&lt;the big expression above&gt;, DateTimeFormat.LongTime24)<\/pre>\n<h3>Drilling down the above expression<\/h3>\n<p>If you want to go deeper, let&#8217;s go over that expression, and maybe you&#8217;ll be able to use some of the concepts for another scenario. When trying to parse this kind of data, it&#8217;s always good to find some examples of data on that format, so that we can keep an eye for special cases that may arise. After populating a sample SQL table with some of those values and looking at how they&#8217;re represented in PowerApps, here are some examples of the types of values that we&#8217;ll need to parse:<\/p>\n<ul>\n<li>PT2H1M39S (2:01:39)<\/li>\n<li>PT12H (12:00:00)<\/li>\n<li>PT2H2S (2:00:02)<\/li>\n<li>PT58M18S (0:58:18)<\/li>\n<li>PT34S (0:00:34)<\/li>\n<li>PT5M (0:05:00)<\/li>\n<\/ul>\n<p>The value always starts with the &#8216;PT&#8217; (period \/ time) identifier, followed by non-zero components of the period (hour \/ minute \/ second). Notice that the components are not always present &#8211; that makes breaking down the parts a little harder, for example, as we cannot rely on the presence of a &#8216;H&#8217; and a &#8216;M&#8217; to find the minute component between them.<\/p>\n<p>We start then by finding the hour. There are two cases here: there is an hour component (in case we find a &#8216;H&#8217; character in the string) or not. If not, the value is simple: 0. Otherwise, we can use the <a href=\"https:\/\/docs.microsoft.com\/powerapps\/maker\/canvas-apps\/functions\/function-left-mid-right\">Mid function<\/a>\u00a0to take the value from the 3rd character (after &#8216;PT&#8217;) and the &#8216;H&#8217; marker:<\/p>\n<pre>\u00a0 \u00a0 If(\n\u00a0 \u00a0 \u00a0 \u00a0 IsBlank(Find(\"H\", ThisItem.Time)), \/\/ Check if there is an hour component\n\u00a0 \u00a0 \u00a0 \u00a0 0,\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\/\/ If not, the value is zero\n\u00a0 \u00a0 \u00a0 \u00a0 Value(\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\/\/ Otherwise, take the substring between 'PT' and 'H'\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Mid(\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ThisItem.Time,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 3,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Find(\"H\", ThisItem.Time) - 3)))<\/pre>\n<pre><\/pre>\n<p>For the minute, there are now three cases: there is no minute component (simple, value is zero). But if there is a minute component, then there may be an hour component or not, so we need to account for both cases. If there is an hour component, then we need to account for it when calculating the indices for taking the substring from the duration format.<\/p>\n<pre>\u00a0 \u00a0 If(\n\u00a0 \u00a0 \u00a0 \u00a0 IsBlank(Find(\"M\", ThisItem.Time)), \/\/ Check if there is a minute component\n\u00a0 \u00a0 \u00a0 \u00a0 0,\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\/\/ If not, the value is zero\n\u00a0 \u00a0 \u00a0 \u00a0 Value(\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\/\/ Otherwise take the substring between the rest of the value\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Mid(\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\/\/\u00a0 \u00a0 \u00a0after the hour component and the 'M' indicator\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ThisItem.Time,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 If(\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 IsBlank(Find(\"H\", ThisItem.Time)), \/\/ If there is no hour component,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 3,\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\/\/ Start right after 'PT'\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Find(\"H\", ThisItem.Time) + 1),\u00a0 \u00a0 \u00a0\/\/ Else start after the hour component\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Find(\"M\", ThisItem.Time) -\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 If(\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 IsBlank(Find(\"H\", ThisItem.Time)),\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 3,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Find(\"H\", ThisItem.Time) + 1))))\n<\/pre>\n<p>To get the seconds component, we could do something similar to the minutes &#8211; take a substring from the minute component (if it exists), or the hour component (again, if it exists), or the &#8216;PT&#8217; identifier. Using the <a href=\"https:\/\/docs.microsoft.com\/powerapps\/maker\/canvas-apps\/functions\/function-left-mid-right\">Mid function<\/a>\u00a0in this way starts to get quite long, so another alternative is to first remove everything that is not the second component, remove the &#8216;S&#8217; identifier (using the <a href=\"https:\/\/docs.microsoft.com\/powerapps\/maker\/canvas-apps\/functions\/function-replace-substitute\">Substitute function<\/a>), then taking the value of it:<\/p>\n<pre>\u00a0 \u00a0 If(\n\u00a0 \u00a0 \u00a0 \u00a0 IsBlank(Find(\"S\", ThisItem.Time)), \/\/ Check if there is a second component\n\u00a0 \u00a0 \u00a0 \u00a0 0,\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\/\/ If not, the value is zero\n\u00a0 \u00a0 \u00a0 \u00a0 Value(\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\/\/ Otherwise take the substring after the minute or hour indicator,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Substitute(\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \/\/\u00a0 \u00a0 \u00a0remove the 'S' indicator, then take the value\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Mid(\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ThisItem.Time,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 If(\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 !IsBlank(Find(\"M\", ThisItem.Time)), \/\/ If there is a minute component\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Find(\"M\", ThisItem.Time) + 1,\u00a0 \u00a0\/\/ Then start right after it\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 !IsBlank(Find(\"H\", ThisItem.Time)), \/\/ Else if there is an hour component\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Find(\"H\", ThisItem.Time) + 1,\u00a0 \u00a0\/\/ Then start after it\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 3)),\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \/\/ Else start after 'PT'\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"S\",\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"\")))\n<\/pre>\n<p>Hopefully that helped clarify a little about the structure of this (quite large) expression. We&#8217;ll try to make this simpler in the future, but that&#8217;s something that can be used today.<\/p>\n<h2>Using time values in forms<\/h2>\n<p>The (quite large) expression from the previous section can be used to display the proper time value in a gallery, for example, but how would it work in forms?<\/p>\n<p>On display forms it&#8217;s fairly straightforward, we can update the <code>Default<\/code> property of the data card that displays the time value so that it is formatted as we want. For example, we can use the following expression to display the time in hours:minutes:seconds format (again, assuming that the column name is &#8216;Time&#8217;):<\/p>\n<pre>\u00a0 \u00a0 Text(\n\u00a0 \u00a0 \u00a0 \u00a0 Time(\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 If(\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 IsBlank(Find(\"H\", ThisItem.Time)), \/\/ Check if there is an hour component\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0,\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\/\/ If not, the value is zero\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Value(\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\/\/ Otherwise, take the substring between 'PT' and 'H'\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Mid(\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ThisItem.Time,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 3,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Find(\"H\", ThisItem.Time) - 3))),\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 If(\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 IsBlank(Find(\"M\", ThisItem.Time)), \/\/ Check if there is a minute component\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0,\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\/\/ If not, the value is zero\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Value(\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\/\/ Otherwise take the substring between the rest of the value\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Mid(\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\/\/\u00a0 \u00a0 \u00a0after the hour component and the 'M' indicator\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ThisItem.Time,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 If(\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 IsBlank(Find(\"H\", ThisItem.Time)),\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 3,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Find(\"H\", ThisItem.Time) + 1),\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Find(\"M\", ThisItem.Time) -\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 If(\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 IsBlank(Find(\"H\", ThisItem.Time)),\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 3,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Find(\"H\", ThisItem.Time) + 1)))),\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 If(\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 IsBlank(Find(\"S\", ThisItem.Time)), \/\/ Check if there is a second component\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0,\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\/\/ If not, the value is zero\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Value(\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\/\/ Otherwise take the substring after the minute or hour indicator,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Substitute(\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \/\/\u00a0 \u00a0 \u00a0remove the 'S' indicator, then take the value\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Mid(\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ThisItem.Time,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 If(\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 !IsBlank(Find(\"M\", ThisItem.Time)),\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Find(\"M\", ThisItem.Time) + 1,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 !IsBlank(Find(\"H\", ThisItem.Time)),\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Find(\"H\", ThisItem.Time) + 1,\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 3)),\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"S\",\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"\")))),\n\u00a0 \u00a0 \u00a0 \u00a0 \"hh:mm:ss\")\n<\/pre>\n<p><img decoding=\"async\" alt=\"Updated display form for showing time columns\" src=\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-content\/uploads\/2018\/11\/BlogPost201811-002.png\" style=\"width: 335px; height: 276px;\"\/><\/p>\n<p>For edit forms, if we want to let the user edit the time value in a text input control, then the expression above will work fine &#8211; it will display the editable text in the hh:mm:ss format which can be updated by the user. Writing the value back is not a problem &#8211; the SQL connector accepts the data in that format when writing back to the server.<\/p>\n<p><img decoding=\"async\" alt=\"Updated edit form for showing time columns\" src=\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-content\/uploads\/2018\/11\/BlogPost201811-003.png\" style=\"width: 332px; height: 344px;\"\/><\/p>\n<p>But like with a date column where we have a date picker, we can use a &#8220;time picker&#8221; as well, with a trio of dropdown values that can be used to select the time in a more user-friendly way:<\/p>\n<p><img decoding=\"async\" alt=\"Updated display form for showing time columns using dropdown controls\" src=\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-content\/uploads\/2018\/11\/BlogPost201811-004.png\" style=\"width: 341px; height: 357px;\"\/><\/p>\n<p>To implement that, we can add three dropdown controls (I&#8217;ll call them &#8216;ddHour&#8217;, &#8216;ddMinute&#8217; and &#8216;ddSecond&#8217; here) to the data card corresponding to the time column, and set their properties as follows:<\/p>\n<pre>\u00a0 \u00a0 ddHour.Items: [\"00\",\"01\",\"02\",\"03\",\"04\",\"05\",\"06\",\"07\",\"08\",\"09\",\"10\",\"11\",\"12\",\"13\",\"14\",\"15\",\"16\",\"17\",\"18\",\"19\",\"20\",\"21\",\"22\",\"23\"]\n\u00a0 \u00a0 ddMinute.Items: [\"00\",\"01\",\"02\",\"03\",\"04\",\"05\",\"06\",\"07\",\"08\",\"09\",\"10\",\"11\",\"12\",\"13\",\"14\",\"15\",\"16\",\"17\",\"18\",\"19\",\"20\",\"21\",\"22\",\"23\",\"24\",\"25\",\"26\",\"27\",\"28\",\"29\",\"30\",\"31\",\"32\",\"33\",\"34\",\"35\",\"36\",\"37\",\"38\",\"39\",\"40\",\"41\",\"42\",\"43\",\"44\",\"45\",\"46\",\"47\",\"48\",\"49\",\"50\",\"51\",\"52\",\"53\",\"54\",\"55\",\"56\",\"57\",\"58\",\"59\"]\n\u00a0 \u00a0 ddSecond.Items: [\"00\",\"01\",\"02\",\"03\",\"04\",\"05\",\"06\",\"07\",\"08\",\"09\",\"10\",\"11\",\"12\",\"13\",\"14\",\"15\",\"16\",\"17\",\"18\",\"19\",\"20\",\"21\",\"22\",\"23\",\"24\",\"25\",\"26\",\"27\",\"28\",\"29\",\"30\",\"31\",\"32\",\"33\",\"34\",\"35\",\"36\",\"37\",\"38\",\"39\",\"40\",\"41\",\"42\",\"43\",\"44\",\"45\",\"46\",\"47\",\"48\",\"49\",\"50\",\"51\",\"52\",\"53\",\"54\",\"55\",\"56\",\"57\",\"58\",\"59\"]\n\u00a0 \u00a0 ddHour.Default: Left(Parent.Default, 2)\n\u00a0 \u00a0 ddMinute.Default: Mid(Parent.Default, 4, 2)\n\u00a0 \u00a0 ddSecond.Default: Mid(Parent.Default, 7, 2)\n<\/pre>\n<p>And then set the Update property in the time data card to<\/p>\n<pre>\u00a0 \u00a0 ddHour.Selected.Value &amp; \":\" &amp; ddMinute.Selected.Value &amp; \":\" &amp; ddSecond.Selected.Value\n<\/pre>\n<p>And that closes the circle, allowing us to update the time values back to the server.<\/p>\n<h2>Wrapping up<\/h2>\n<p>The expressions above can be used to better work with time columns in a SQL Server database. Currently it&#8217;s not as straightforward as it can be, but it can be done. As we get more requests for it, we can prioritize it appropriately and make improvements to the product to make this scenario simpler. If you feel that this is important, please vote up on <a href=\"https:\/\/powerusers.microsoft.com\/t5\/PowerApps-Ideas\/integration-of-SQL-time-columns-in-PowerApps\/idi-p\/171915\">this feature request<\/a>\u00a0on the <a href=\"https:\/\/aka.ms\/powerapps-ideas\">PowerApps Ideas board<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When we connect an app to a SQL Server table that has a column of type time, the values don&#8217;t show in a user-friendly format by default in PowerApps. In this post we&#8217;ll go over some expressions that can be used to improve the experience for the app users, by converting from the duration format to a more natural hh:mm:ss one.<\/p>\n","protected":false},"author":91,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ms_queue_id":[],"ep_exclude_from_search":false,"_classifai_error":"","_classifai_text_to_speech_error":"","_alt_title":"","ms-ems-related-posts":[],"footnotes":""},"audience":[3378],"content-type":[3423],"job-role":[],"product":[3473],"property":[],"topic":[],"coauthors":[2126],"class_list":["post-109207","post","type-post","status-publish","format-standard","hentry","audience-it-professional","content-type-tips-and-guides","product-power-apps"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.2 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Working with Time columns in SQL Server - Microsoft Power Platform Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Working with Time columns in SQL Server - Microsoft Power Platform Blog\" \/>\n<meta property=\"og:description\" content=\"When we connect an app to a SQL Server table that has a column of type time, the values don&#039;t show in a user-friendly format by default in PowerApps. In this post we&#039;ll go over some expressions that can be used to improve the experience for the app users, by converting from the duration format to a more natural hh:mm:ss one.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/power-apps\/working-with-time-columns-in-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"Microsoft Power Platform Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-11-14T13:59:43+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-06-11T15:06:08+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-content\/uploads\/2018\/11\/BlogPost201811-001.png\" \/>\n<meta name=\"author\" content=\"Carlos Figueira\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Carlos Figueira\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"9 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/\"},\"author\":[{\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/author\/carlos-figueira\/\",\"@type\":\"Person\",\"@name\":\"Carlos Figueira\"}],\"headline\":\"Working with Time columns in SQL Server\",\"datePublished\":\"2018-11-14T13:59:43+00:00\",\"dateModified\":\"2025-06-11T15:06:08+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/\"},\"wordCount\":1164,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-content\/uploads\/2018\/11\/BlogPost201811-001.png\",\"keywords\":[\"Formulas\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/\",\"url\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/\",\"name\":\"Working with Time columns in SQL Server - Microsoft Power Platform Blog\",\"isPartOf\":{\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-content\/uploads\/2018\/11\/BlogPost201811-001.png\",\"datePublished\":\"2018-11-14T13:59:43+00:00\",\"dateModified\":\"2025-06-11T15:06:08+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/#primaryimage\",\"url\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-content\/uploads\/2018\/11\/BlogPost201811-001.webp\",\"contentUrl\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-content\/uploads\/2018\/11\/BlogPost201811-001.webp\",\"width\":334,\"height\":379,\"caption\":\"graphical user interface, text, application, email\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Working with Time columns in SQL Server\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#website\",\"url\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/\",\"name\":\"Microsoft Power Platform Blog\",\"description\":\"Innovate with Business Apps\",\"publisher\":{\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#organization\",\"name\":\"Microsoft Power Platform Blog\",\"url\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-content\/uploads\/2020\/03\/Microsoft-Logo-e1685482038800.png\",\"contentUrl\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-content\/uploads\/2020\/03\/Microsoft-Logo-e1685482038800.png\",\"width\":194,\"height\":145,\"caption\":\"Microsoft Power Platform Blog\"},\"image\":{\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#\/schema\/person\/40b9433cbce91943b0d9859bd49d8480\",\"name\":\"Carlos Figueira\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/bddcffc124516b0145da0546e0bf6920ca4bfa3200550fc1e36a3f9d2307a105?s=96&d=mm&r=gbf1d82d40330127e78a92cdfef0de8ab\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/bddcffc124516b0145da0546e0bf6920ca4bfa3200550fc1e36a3f9d2307a105?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/bddcffc124516b0145da0546e0bf6920ca4bfa3200550fc1e36a3f9d2307a105?s=96&d=mm&r=g\",\"caption\":\"Carlos Figueira\"},\"url\":\"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/author\/carlosff\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Working with Time columns in SQL Server - Microsoft Power Platform Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"Working with Time columns in SQL Server - Microsoft Power Platform Blog","og_description":"When we connect an app to a SQL Server table that has a column of type time, the values don't show in a user-friendly format by default in PowerApps. In this post we'll go over some expressions that can be used to improve the experience for the app users, by converting from the duration format to a more natural hh:mm:ss one.","og_url":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/power-apps\/working-with-time-columns-in-sql-server\/","og_site_name":"Microsoft Power Platform Blog","article_published_time":"2018-11-14T13:59:43+00:00","article_modified_time":"2025-06-11T15:06:08+00:00","og_image":[{"url":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-content\/uploads\/2018\/11\/BlogPost201811-001.png","type":"","width":"","height":""}],"author":"Carlos Figueira","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Carlos Figueira","Est. reading time":"9 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/#article","isPartOf":{"@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/"},"author":[{"@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/author\/carlos-figueira\/","@type":"Person","@name":"Carlos Figueira"}],"headline":"Working with Time columns in SQL Server","datePublished":"2018-11-14T13:59:43+00:00","dateModified":"2025-06-11T15:06:08+00:00","mainEntityOfPage":{"@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/"},"wordCount":1164,"commentCount":0,"publisher":{"@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#organization"},"image":{"@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-content\/uploads\/2018\/11\/BlogPost201811-001.png","keywords":["Formulas"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/","url":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/","name":"Working with Time columns in SQL Server - Microsoft Power Platform Blog","isPartOf":{"@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/#primaryimage"},"image":{"@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-content\/uploads\/2018\/11\/BlogPost201811-001.png","datePublished":"2018-11-14T13:59:43+00:00","dateModified":"2025-06-11T15:06:08+00:00","breadcrumb":{"@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/#primaryimage","url":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-content\/uploads\/2018\/11\/BlogPost201811-001.webp","contentUrl":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-content\/uploads\/2018\/11\/BlogPost201811-001.webp","width":334,"height":379,"caption":"graphical user interface, text, application, email"},{"@type":"BreadcrumbList","@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/2018\/11\/14\/working-with-time-columns-in-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/"},{"@type":"ListItem","position":2,"name":"Working with Time columns in SQL Server"}]},{"@type":"WebSite","@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#website","url":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/","name":"Microsoft Power Platform Blog","description":"Innovate with Business Apps","publisher":{"@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#organization","name":"Microsoft Power Platform Blog","url":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-content\/uploads\/2020\/03\/Microsoft-Logo-e1685482038800.png","contentUrl":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-content\/uploads\/2020\/03\/Microsoft-Logo-e1685482038800.png","width":194,"height":145,"caption":"Microsoft Power Platform Blog"},"image":{"@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/#\/schema\/person\/40b9433cbce91943b0d9859bd49d8480","name":"Carlos Figueira","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/bddcffc124516b0145da0546e0bf6920ca4bfa3200550fc1e36a3f9d2307a105?s=96&d=mm&r=gbf1d82d40330127e78a92cdfef0de8ab","url":"https:\/\/secure.gravatar.com\/avatar\/bddcffc124516b0145da0546e0bf6920ca4bfa3200550fc1e36a3f9d2307a105?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/bddcffc124516b0145da0546e0bf6920ca4bfa3200550fc1e36a3f9d2307a105?s=96&d=mm&r=g","caption":"Carlos Figueira"},"url":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/author\/carlosff\/"}]}},"bloginabox_animated_featured_image":null,"bloginabox_display_generated_audio":false,"distributor_meta":false,"distributor_terms":false,"distributor_media":false,"distributor_original_site_name":"Microsoft Power Platform Blog","distributor_original_site_url":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog","push-errors":false,"_links":{"self":[{"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/posts\/109207","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/users\/91"}],"replies":[{"embeddable":true,"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/comments?post=109207"}],"version-history":[{"count":4,"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/posts\/109207\/revisions"}],"predecessor-version":[{"id":124358,"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/posts\/109207\/revisions\/124358"}],"wp:attachment":[{"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/media?parent=109207"}],"wp:term":[{"taxonomy":"audience","embeddable":true,"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/audience?post=109207"},{"taxonomy":"content-type","embeddable":true,"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/content-type?post=109207"},{"taxonomy":"job-role","embeddable":true,"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/job-role?post=109207"},{"taxonomy":"product","embeddable":true,"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/product?post=109207"},{"taxonomy":"property","embeddable":true,"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/property?post=109207"},{"taxonomy":"topic","embeddable":true,"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/topic?post=109207"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/cm-edgetun.pages.dev\/en-us\/power-platform\/blog\/wp-json\/wp\/v2\/coauthors?post=109207"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}