How do I store a time span value in SQL Server 2008?

Hello Experts:

I have a string that represents a time duration-
example:  24d 21h 27m

I'm not sure the best way to parse this into a DateTime or equivalent value that I can use in .NET to compare with other DateTime values, and then store in SQLServer -- what SQL data type?  numeric for conversion back to DateTime in my app?, or store as a Time?? not sure.

Thanks
cmh_rwhelanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
Hi,
you can use ticks for this pupose,
covert timespan to ticks and store in db as long value,
read long value from db and change it to timespan
you may find following helpful
http://stackoverflow.com/questions/852267/mapping-a-database-value-to-a-timespan-using-dbml
Thanks
0
cmh_rwhelanAuthor Commented:
OK - That is the first solution I came up with also - just fishing for something that might be easier to query...

let's see what else rolls in from the other experts , but this is certaily a workable solution
0
lenordisteCommented:
if you only require a precision in minutes, than it is probably less cumbersome to store it as such (a long corresponding to your timespan in minutes). This way it's still easy to retrieve the Timespan in .NET (Timespan.FromMinutes) and you can still use a lot of built in SQL Server date functions without any problems (DateAdd can work with minutes for instance).

can you give us a few examples of what you want to achieve? this will help us giving you a more appropriate solution.
0
cmh_rwhelanAuthor Commented:
I think you're right about storing the miniute - certainly precise enough.

The problem is simple:  I get passed a string that represents a duration of time in the following format:

24d 21h 27m

ie -  (twenty-four days, twenty-one hours and twenty-seven minutes)

I have to parse this into a TimeSpan - something like this :
 
char[] delimiters = new char[]{' '};
string[] lifeTimeString = row.LifeTime.Split(delimiters[0]);

TimeSpan lifeTime = new TimeSpan(

Convert.ToInt16(
lifeTimeString[0].Substring(0, lifeTimeString[0].Length - 1)), 

Convert.ToInt16(
lifeTimeString[1].Substring(0,lifeTimeString[0].Length - 1)),
  
Convert.ToInt16(
lifeTimeString[2].Substring(0, lifeTimeString[0].Length - 1))

);

Open in new window


Then need to get the total  minutes from the TimeSpan -

                   TimeSpan  absLifeTime = lifeTime.Duration();
                   long lifeTimeMinutes = (long)absLifeTime.TotalMinutes;

From there I can store it in SQL Server.

Retrieving it should work with TimeSpan.FromMinutes

Any other suggestions?




0
lenordisteCommented:
you could do this which is a bit easier to read:
//Convert the string into a format which can be parsed with Timespan d.hh:mm
string lifeTimeString = row.LifeTime.Replace("d ",".").Replace("h ",":").Replace("m","")
TimeSpan lifeTime = Timespan.Parse(lifeTimeString);

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.