• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5091
  • Last Modified:

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
0
cmh_rwhelan
Asked:
cmh_rwhelan
  • 2
  • 2
3 Solutions
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now