Days, Hours, & Minutes from a Long?

[robertfah]

I've got a field in my table called TimeSpent and it's a bigInt. We store the number of TICKS from our C# application that corresponds to a number of days, hours, and minutes they input. For instance, if the user puts in 0 days, 0 hours, and 10 minutes, then the value that is stored is 6000000000.

Is there a way in SQL to convert that value to 3 seperate values of Days, Hours, & Minutes?

 

[Qik3Coder]

You have to part out your values.
Depending on how convoluted you want it will be something like:

I'd put this in a function that returns a pseudo date
remembering that the "zero date" is 1900-01-01
'1900-01-01 00:01:00.000'

Declare @ticks bigint
set @ticks = 6000000000
declare @years int, @days int, @hours int, @mins int

if(@Ticks > [Ticks in a year)
begin
[tab]set @years = @ticks/[Ticks in a year]
[tab]set @ticks = @ticks - (@Years * [Ticks in a year])
end

if(@Ticks > [Ticks in a day)
begin
[tab]set @days = @ticks/[Ticks in a day]
[tab]set @ticks = @ticks - (@Day * [Ticks in a day])
end

if(@Ticks > [Ticks in a hour)
begin
[tab]set @hours = @ticks/[Ticks in a hour]
[tab]set @ticks = @ticks - (@Day * [Ticks in a hour])
end

if(@Ticks > [Ticks in a mins)
begin
[tab]set @mins = @ticks/[Ticks in a mins]
[tab]set @ticks = @ticks - (@mins * [Ticks in a mins])
end

Lodlaiden

You've got questions and source code. We want both!

 

[Olaf Doschke]

10 minutes is 600 seconds and - as you say - 6000000000 ticks, so 10000000 ticks is a second.

You know the conversion factors from days to hours, hours, to minutes, minutes to seconds, then it's easy to figure out how ticks convert to anything.

To put in words, what Qik3Coder gave you: You begin with the highest time interval (days or even years) and compute from ticks to that time interval. Then you just take the int value of that result and substract the ticks this corresponds to and restart from the rest of ticks with the next shorter time interval.

You do this kind of algorithm any day, when you pay an amount of money with the available bills and coins.

Bye, Olaf.