CODEWITHSUNDEEP
c#sql-server-2008datetime
woggles
woggles

Reputation: 7444

Storing a TimeSpan between two DateTimes

I need a way to store and manipulate a date range using C# and storing the data in a SQL database. Is storing two DateTimes the best way to go about this?

For example I need to employees to be able to select the duration they spent working on a specific project by selecting the start date and end date using a DatePicker.

I have the following further requirements:

Does this representation make sense? Should I rather look at storing a DateTime for the StartDate and a TimeSpan taking into account my requirements?

Edit: also

Does my representation of end date make sense? So that 2nd of may will be saved as '2011-05-03 00:00:00.000' because that is when the duration ends. Bearing this in mind I'll need to subtract a day from the end date when displaying this in a calendar..

Upvotes: 2

Views: 1132

Answers (6)

user687474
user687474

Reputation:

I suggest to save the start and end date to your database. The difference can always be calculated.

The critical aspect of date ranges is how to handle the boundaries. You can use a mapper for the start/end date to ensure correct time calculations (Inside/Touching):

// ----------------------------------------------------------------------
public void TimePeriodMapperSample()
{
  TimeCalendar timeCalendar = new TimeCalendar();
  CultureInfo ci = CultureInfo.InvariantCulture;

  DateTime start = new DateTime( 2011, 3, 1, 13, 0, 0 );
  DateTime end = new DateTime( 2011, 3, 1, 14, 0, 0 );

  Console.WriteLine( "Original start: {0}",
                     start.ToString( "HH:mm:ss.fffffff", ci ) );
  // > Original start: 13:00:00.0000000
  Console.WriteLine( "Original end: {0}",
                     end.ToString( "HH:mm:ss.fffffff", ci ) );
  // > Original end: 14:00:00.0000000

  Console.WriteLine( "Mapping offset start: {0}", timeCalendar.StartOffset );
  // > Mapping offset start: 00:00:00
  Console.WriteLine( "Mapping offset end: {0}", timeCalendar.EndOffset );
  // > Mapping offset end: -00:00:00.0000001

  Console.WriteLine( "Mapped start: {0}",
    timeCalendar.MapStart( start ).ToString( "HH:mm:ss.fffffff", ci ) );
  // > Mapped start: 13:00:00.0000000
  Console.WriteLine( "Mapped end: {0}",
    timeCalendar.MapEnd( end ).ToString( "HH:mm:ss.fffffff", ci ) );
  // > Mapped end: 13:59:59.9999999
} // TimePeriodMapperSample

Check out the article Time Period Library for .NET (section Calendar Time Periods).

Upvotes: 2

A G
A G

Reputation: 22587

Only store start and end dates. Then to calculate no of days worked:

Round(DateDiff(hour, StartDate, EndDate)/24, 2)

Upvotes: 1

Kamran Khan
Kamran Khan

Reputation: 9986

Ideally, you will need two fields anyway:

  • Store two date/time separately --OR--
  • Store one date/time and store the time-elapsed

Reviewing your requirements, I would go for two separate date/time fields; and calculate the fulldays/halfdays including (adding/subtracting) holidays.

Keep a separate configuration table to define/configure the max/minimum duration of the day.

Perform any calculations on the day/time, within the query, or alternatively on the UI, upon user actions - if you desire.

Upvotes: 1

V4Vendetta
V4Vendetta

Reputation: 38210

I believe two DateTime Fields along with checkboxes corresponding to Start and End date to indicate whether its Half or Full day would be sufficient for you. based on the Checkbox state you can manipulate the time component to suit your needs. You will just have to store the Start and End DateTime values which would adhere to the requirements you posted.

Upvotes: 0

Falanwe
Falanwe

Reputation: 4744

It really depends on how you intend to use your data. If your second date will never be used outside of calculating the TimeSpan, you should not store it, and store the TimeSpan instead. If you use the end date frequently and only seldom calculate the duration, you should store the 2 datetimes.

You can even consider to store the three values if you use them frequently and the increase in memory space is not a problem.

Upvotes: 0

George Duckett
George Duckett

Reputation: 32428

To me it seems like storing 2 datetime's makes the most sense.

Upvotes: 0

Related Questions