The datatype of SPTime is datetime.
Is there no way to display a time value as: 1.40.55?
There is user-friendly value in that display as opposed to 100 minutes.
Also, a period serves to enter it quickly instead of colons.
I just wonder if it is possible (as I have already done in Access) to sum
datetime values and just extract the h:mi:ss portion from it, so that it can
be displayed as 1.40.55.
[quoted text, click to view] "David Portas" wrote:
> What is the datatype of the sptime column? SQL has no time datatype. My
> preferred way would be to store the durations as a numeric value of hours,
> minutes or seconds. That way you can add up the durations using SUM in the
> usual way. Avoid storing it as a VARCHAR/CHAR since this just makes extra
> work to add and manipulate the values.
>
> --
> David Portas
> SQL Server MVP
> --
>
>
Formatting the values for display and data-entry is best handled in your
client application IMO. However, here's an example using DATETIME that will
work provided the total of your time values won't exceed 24 hours:
CREATE TABLE YourTable (sptime DATETIME CHECK (sptime >='19000101' AND
sptime <'19010102'))
INSERT INTO YourTable VALUES ('1900-01-01T01:00:00')
INSERT INTO YourTable VALUES ('1900-01-01T00:20:00')
INSERT INTO YourTable VALUES ('1900-01-01T00:05:32')
SELECT CONVERT(CHAR(8),DATEADD(SECOND,
SUM(DATEDIFF(SECOND,'19000101',sptime)),'19000101'),8)
FROM YourTable
If you may have to represent values over 24 hours then it's probably best to
divide the SUM in the above query to make it hours, minutes and seconds
rather than use the CONVERT(..,8) format.
--
David Portas
SQL Server MVP
--
What is the datatype of the sptime column? SQL has no time datatype. My
preferred way would be to store the durations as a numeric value of hours,
minutes or seconds. That way you can add up the durations using SUM in the
usual way. Avoid storing it as a VARCHAR/CHAR since this just makes extra
work to add and manipulate the values.
--
David Portas
SQL Server MVP
--
jonefer
Look at CONVERT function in the BOL
SELECT CONVERT(varchar,GETDATE(),108)
[quoted text, click to view] "jonefer" <jonefer@discussions.microsoft.com> wrote in message
news:CD4DA26A-6395-4BEC-8BEC-B225659D0F8B@microsoft.com...
> Please help me convert an access query.
> I have the following alias formula in an access query that I'd like to
> convert for SQL server for an Access Data Project: (To a Function?)
>
> AcumTime: Format(DSum("[SPTime]","qryPrograms"),"h\.nn\.ss")
>
> SPTime is time in hours minutes and seconds example: 1.25.32
>
> The alias adds the total time accumulated of hours minutes and seconds and
> serves to calculate how much of a video tape is used. I have the rest of
the
> logic worked out but am just stuck getting to this point.
>
> I'm not certain how to even add time in this manner in T-SQL.
>
>
>
