StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>SQL Server can convert a time-only portion of a datetime value from string to datetime, however in your example, you have a precision of 4 decimal places. SQL Server 2005 only recognizes 3 places. Therefore, you will need to truncate the right-most character:</p> <pre><code>create table #TableWithTimeValues ( TimeField varchar(13) not null ) insert into #TableWithTimeValues select '04:00:00.0000' union all select '05:00:00.0000' union all select '06:00:00.0000' SELECT CAST(TimeField as datetime) FROM #TableWithTimeValues --Msg 241, Level 16, State 1, Line 1 --Conversion failed when converting datetime from character string. SELECT CAST(LEFT(TimeField, 12) as datetime) FROM #TableWithTimeValues --Success! </code></pre> <p>This will convert valid values into a DATETIME starting on 1900-01-01. SQL Server calculates dates based on 1 day = 1 (integer). Portions of days are then portions of the value 1 (i.e. noon is 0.5). Because a date was not specified in the conversion, SQL Server assigned the value of 0 days (1900-01-01), which accommodates our need to average the time portion.</p> <p>To perform an AVG operation on a DATETIME, you must first convert the DATETIME to a decimal value, perform the aggregation, then cast back. For example</p> <pre><code>SELECT CAST(AVG(CAST(CAST(LEFT(TimeField, 12) as datetime) AS FLOAT)) AS DATETIME) FROM #TableWithTimeValues --1900-01-01 05:00:00.000 </code></pre> <p>If you need to store this with an extra decimal place, you can convert the DATETIME to a VARCHAR with time portion only and pad the string back to 13 characters:</p> <pre><code>SELECT CONVERT(VARCHAR, CAST(AVG(CAST(CAST(LEFT(TimeField, 12) as datetime) AS FLOAT)) AS DATETIME), 114) + '0' FROM #TableWithTimeValues </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload