What is the correct SQL type to store a Net Timespan with values 24 00 00

Question

I am trying to store a  Net TimeSpan in SQL server 2008 R2    EF Code First seems to be suggesting it should be stored as a Time 7  in SQL    However TimeSpan in  Net can handle longer periods than 24 hours    What is the best way to handle storing  Net TimeSpan in SQL server

User · Answer

Typically  I store a TimeSpan as a bigint populated with ticks from the TimeSpan Ticks property as previously suggested  You can also store a TimeSpan as a varchar 26  populated with the output of TimeSpan ToString    The four scalar functions  ConvertFromTimeSpanString  ConvertToTimeSpanString  DateAddTicks  DateDiffTicks  that I wrote are helpful for handling TimeSpan on the SQL side and avoid the hacks that would produce artificially bounded ranges  If you can store the interval in a  NET TimeSpan at all it should work with these functions also  Additionally  the functions allow you to work with TimeSpans and 100-nanosecond ticks even when using technologies that don t include the  NET Framework   DROP FUNCTION  dbo   DateDiffTicks  GO  DROP FUNCTION  dbo   DateAddTicks  GO  DROP FUNCTION  dbo   ConvertToTimeSpanString  GO  DROP FUNCTION  dbo   ConvertFromTimeSpanString  GO  SET ANSI NULLS OFF GO  SET QUOTED IDENTIFIER OFF GO  --                                               -- Author       James Coe -- Create date  2011-05-23 -- Description  Converts from a varchar 26  TimeSpan string to a bigint containing the number of 100 nanosecond ticks  --                                                       -  d  hh mm ss  fffffff         -        A minus sign  which indicates a negative time interval  No sign is included for a positive time span        d        The number of days in the time interval  This element is omitted if the time interval is less than one day         hh        The number of hours in the time interval  ranging from 0 to 23         mm        The number of minutes in the time interval  ranging from 0 to 59         ss        The number of seconds in the time interval  ranging from 0 to 59         fffffff        Fractional seconds in the time interval  This element is omitted if the time interval does not include       fractional seconds  If present  fractional seconds are always expressed using seven decimal digits         CREATE FUNCTION  dbo   ConvertFromTimeSpanString    timeSpan varchar 26   RETURNS bigint AS BEGIN     DECLARE  hourStart int     DECLARE  minuteStart int     DECLARE  secondStart int     DECLARE  ticks bigint     DECLARE  hours bigint     DECLARE  minutes bigint     DECLARE  seconds DECIMAL 9  7       SET  hourStart   CHARINDEX       timeSpan    1     SET  minuteStart   CHARINDEX       timeSpan    1     SET  secondStart   CHARINDEX       timespan   minuteStart    1     SET  ticks   0      IF   hourStart  gt  1 AND  hourStart  lt   minuteStart      BEGIN         SET  ticks   CONVERT bigint  LEFT  timespan   hourstart - 2     864000000000     END     ELSE     BEGIN         SET  hourStart   1     END      SET  hours   CONVERT bigint  SUBSTRING  timespan   hourStart   minuteStart -  hourStart - 1       SET  minutes   CONVERT bigint  SUBSTRING  timespan   minuteStart   secondStart -  minuteStart - 1       SET  seconds   CONVERT DECIMAL 9  7   SUBSTRING  timespan   secondStart  LEN  timeSpan  -  secondStart   1        IF   ticks  lt  0      BEGIN         SET  ticks    ticks -  hours   36000000000     END     ELSE     BEGIN         SET  ticks    ticks    hours   36000000000     END      IF   ticks  lt  0      BEGIN         SET  ticks    ticks -  minutes   600000000     END     ELSE     BEGIN         SET  ticks    ticks    minutes   600000000     END      IF   ticks  lt  0      BEGIN         SET  ticks    ticks -  seconds   10000000 0     END     ELSE     BEGIN         SET  ticks    ticks    seconds   10000000 0     END      RETURN  ticks END GO  --                                               -- Author       James Coe -- Create date  2011-05-23 -- Description  Converts from a bigint containing the number of 100 nanosecond ticks to a varchar 26  TimeSpan string  --                                                   -  d  hh mm ss  fffffff     -    A minus sign  which indicates a negative time interval  No sign is included for a positive time span    d    The number of days in the time interval  This element is omitted if the time interval is less than one day     hh    The number of hours in the time interval  ranging from 0 to 23     mm    The number of minutes in the time interval  ranging from 0 to 59     ss    The number of seconds in the time interval  ranging from 0 to 59     fffffff    Fractional seconds in the time interval  This element is omitted if the time interval does not include   fractional seconds  If present  fractional seconds are always expressed using seven decimal digits     CREATE FUNCTION  dbo   ConvertToTimeSpanString    ticks bigint  RETURNS varchar 26  AS BEGIN     DECLARE  timeSpanString varchar 26       IF   ticks  lt  0      BEGIN         SET  timeSpanString    -      END     ELSE     BEGIN         SET  timeSpanString          END      -- Days     DECLARE  days bigint      SET  days   FLOOR ABS  ticks   864000000000 0        IF   days  gt  0      BEGIN         SET  timeSpanString    timeSpanString   CONVERT varchar 26    days            END      SET  ticks   ABS  ticks   864000000000      -- Hours     SET  timeSpanString    timeSpanString   RIGHT  0    CONVERT varchar 26   FLOOR  ticks   36000000000 0    2            SET  ticks    ticks   36000000000     -- Minutes     SET  timeSpanString    timeSpanString   RIGHT  0    CONVERT varchar 26   FLOOR  ticks   600000000 0    2            SET  ticks    ticks   600000000     -- Seconds     SET  timeSpanString    timeSpanString   RIGHT  0    CONVERT varchar 26   FLOOR  ticks   10000000 0    2      SET  ticks    ticks   10000000      -- Fractional Seconds     IF   ticks  gt  0      BEGIN         SET  timeSpanString    timeSpanString         LEFT CONVERT varchar 26    ticks     0000000   7      END      RETURN  timeSpanString END GO  --                                               -- Author       James Coe -- Create date  2011-05-23 -- Description  Adds the specified number of 100 nanosecond ticks to a date  --                                               CREATE FUNCTION  dbo   DateAddTicks         ticks bigint        starting date datetimeoffset       RETURNS datetimeoffset AS BEGIN     DECLARE  dateTimeResult datetimeoffset      IF   ticks  lt  0      BEGIN         -- Hours         SET  dateTimeResult   DATEADD HOUR  CEILING  ticks   36000000000 0    starting date          SET  ticks    ticks   36000000000         -- Seconds         SET  dateTimeResult   DATEADD SECOND  CEILING  ticks   10000000 0    dateTimeResult          SET  ticks    ticks   10000000         -- Nanoseconds         SET  dateTimeResult   DATEADD NANOSECOND   ticks   100   dateTimeResult      END     ELSE     BEGIN         -- Hours         SET  dateTimeResult   DATEADD HOUR  FLOOR  ticks   36000000000 0    starting date          SET  ticks    ticks   36000000000         -- Seconds         SET  dateTimeResult   DATEADD SECOND  FLOOR  ticks   10000000 0    dateTimeResult          SET  ticks    ticks   10000000         -- Nanoseconds         SET  dateTimeResult   DATEADD NANOSECOND   ticks   100   dateTimeResult      END      RETURN  dateTimeResult END GO  --                                               -- Author       James Coe -- Create date  2011-05-23 -- Description   Gets the difference between two dates in 100 nanosecond ticks  --                                               CREATE FUNCTION  dbo   DateDiffTicks         starting date datetimeoffset        ending date datetimeoffset       RETURNS bigint AS BEGIN     DECLARE  ticks bigint     DECLARE  days bigint     DECLARE  hours bigint     DECLARE  minutes bigint     DECLARE  seconds bigint      SET  hours   DATEDIFF HOUR   starting date   ending date      SET  starting date   DATEADD HOUR   hours   starting date      SET  ticks    hours   36000000000     SET  seconds   DATEDIFF SECOND   starting date   ending date      SET  starting date   DATEADD SECOND   seconds   starting date      SET  ticks    ticks    seconds   10000000     SET  ticks    ticks   CONVERT bigint  DATEDIFF NANOSECOND   starting date   ending date     100      RETURN  ticks END GO  --- BEGIN Test Harness --- SET NOCOUNT ON  DECLARE  dateTimeOffsetMinValue datetimeoffset DECLARE  dateTimeOffsetMaxValue datetimeoffset DECLARE  timeSpanMinValueString varchar 26  DECLARE  timeSpanZeroString varchar 26  DECLARE  timeSpanMaxValueString varchar 26  DECLARE  timeSpanMinValueTicks bigint DECLARE  timeSpanZeroTicks bigint DECLARE  timeSpanMaxValueTicks bigint DECLARE  dateTimeOffsetMinMaxDiffTicks bigint DECLARE  dateTimeOffsetMaxMinDiffTicks bigint  SET  dateTimeOffsetMinValue    0001-01-01T00 00 00 0000000 00 00  SET  dateTimeOffsetMaxValue    9999-12-31T23 59 59 9999999 00 00  SET  timeSpanMinValueString    -10675199 02 48 05 4775808  SET  timeSpanZeroString    00 00 00  SET  timeSpanMaxValueString    10675199 02 48 05 4775807  SET  timeSpanMinValueTicks   -9223372036854775808 SET  timeSpanZeroTicks   0 SET  timeSpanMaxValueTicks   9223372036854775807 SET  dateTimeOffsetMinMaxDiffTicks   3155378975999999999 SET  dateTimeOffsetMaxMinDiffTicks   -3155378975999999999  -- TimeSpan Conversion Tests PRINT  Testing TimeSpan conversions      DECLARE  convertToTimeSpanStringMinTicksResult varchar 26  DECLARE  convertFromTimeSpanStringMinTimeSpanResult bigint DECLARE  convertToTimeSpanStringZeroTicksResult varchar 26  DECLARE  convertFromTimeSpanStringZeroTimeSpanResult bigint DECLARE  convertToTimeSpanStringMaxTicksResult varchar 26  DECLARE  convertFromTimeSpanStringMaxTimeSpanResult bigint  SET  convertToTimeSpanStringMinTicksResult   dbo ConvertToTimeSpanString  timeSpanMinValueTicks  SET  convertFromTimeSpanStringMinTimeSpanResult   dbo ConvertFromTimeSpanString  timeSpanMinValueString  SET  convertToTimeSpanStringZeroTicksResult   dbo ConvertToTimeSpanString  timeSpanZeroTicks  SET  convertFromTimeSpanStringZeroTimeSpanResult   dbo ConvertFromTimeSpanString  timeSpanZeroString  SET  convertToTimeSpanStringMaxTicksResult   dbo ConvertToTimeSpanString  timeSpanMaxValueTicks  SET  convertFromTimeSpanStringMaxTimeSpanResult   dbo ConvertFromTimeSpanString  timeSpanMaxValueString   -- Test Results SELECT  Convert to TimeSpan String from Ticks  Minimum   AS Test       CASE          WHEN  convertToTimeSpanStringMinTicksResult    timeSpanMinValueString             THEN  Pass          ELSE  Fail          END AS  Test Status         timeSpanMinValueTicks AS  Ticks        CONVERT varchar 26   NULL  AS  TimeSpan String        CONVERT varchar 26    convertToTimeSpanStringMinTicksResult  AS  Actual Result        CONVERT varchar 26    timeSpanMinValueString  AS  Expected Result  UNION ALL SELECT  Convert from TimeSpan String to Ticks  Minimum   AS Test       CASE          WHEN  convertFromTimeSpanStringMinTimeSpanResult    timeSpanMinValueTicks             THEN  Pass          ELSE  Fail          END AS  Test Status        NULL AS  Ticks         timeSpanMinValueString AS  TimeSpan String        CONVERT varchar 26    convertFromTimeSpanStringMinTimeSpanResult  AS  Actual Result        CONVERT varchar 26    timeSpanMinValueTicks  AS  Expected Result  UNION ALL SELECT  Convert to TimeSpan String from Ticks  Zero   AS Test       CASE          WHEN  convertToTimeSpanStringZeroTicksResult    timeSpanZeroString             THEN  Pass          ELSE  Fail          END AS  Test Status         timeSpanZeroTicks AS  Ticks        CONVERT varchar 26   NULL  AS  TimeSpan String        CONVERT varchar 26    convertToTimeSpanStringZeroTicksResult  AS  Actual Result        CONVERT varchar 26    timeSpanZeroString  AS  Expected Result  UNION ALL SELECT  Convert from TimeSpan String to Ticks  Zero   AS Test       CASE          WHEN  convertFromTimeSpanStringZeroTimeSpanResult    timeSpanZeroTicks             THEN  Pass          ELSE  Fail          END AS  Test Status        NULL AS  Ticks         timeSpanZeroString AS  TimeSpan String        CONVERT varchar 26    convertFromTimeSpanStringZeroTimeSpanResult  AS  Actual Result        CONVERT varchar 26    timeSpanZeroTicks  AS  Expected Result  UNION ALL SELECT  Convert to TimeSpan String from Ticks  Maximum   AS Test       CASE          WHEN  convertToTimeSpanStringMaxTicksResult    timeSpanMaxValueString             THEN  Pass          ELSE  Fail          END AS  Test Status         timeSpanMaxValueTicks AS  Ticks        CONVERT varchar 26   NULL  AS  TimeSpan String        CONVERT varchar 26    convertToTimeSpanStringMaxTicksResult  AS  Actual Result        CONVERT varchar 26    timeSpanMaxValueString  AS  Expected Result  UNION ALL SELECT  Convert from TimeSpan String to Ticks  Maximum   AS Test       CASE          WHEN  convertFromTimeSpanStringMaxTimeSpanResult    timeSpanMaxValueTicks             THEN  Pass          ELSE  Fail          END AS  Test Status        NULL AS  Ticks         timeSpanMaxValueString AS  TimeSpan String        CONVERT varchar 26    convertFromTimeSpanStringMaxTimeSpanResult  AS  Actual Result        CONVERT varchar 26    timeSpanMaxValueTicks  AS  Expected Result   -- Ticks Date Add Test PRINT  Testing DateAddTicks      DECLARE  DateAddTicksPositiveTicksResult datetimeoffset DECLARE  DateAddTicksZeroTicksResult datetimeoffset DECLARE  DateAddTicksNegativeTicksResult datetimeoffset  SET  DateAddTicksPositiveTicksResult   dbo DateAddTicks  dateTimeOffsetMinMaxDiffTicks   dateTimeOffsetMinValue  SET  DateAddTicksZeroTicksResult   dbo DateAddTicks  timeSpanZeroTicks   dateTimeOffsetMinValue  SET  DateAddTicksNegativeTicksResult   dbo DateAddTicks  dateTimeOffsetMaxMinDiffTicks   dateTimeOffsetMaxValue   -- Test Results SELECT  Date Add with Ticks Test  Positive   AS Test       CASE          WHEN  DateAddTicksPositiveTicksResult    dateTimeOffsetMaxValue             THEN  Pass          ELSE  Fail          END AS  Test Status         dateTimeOffsetMinMaxDiffTicks AS  Ticks         dateTimeOffsetMinValue AS  Starting Date         DateAddTicksPositiveTicksResult AS  Actual Result         dateTimeOffsetMaxValue AS  Expected Result  UNION ALL SELECT  Date Add with Ticks Test  Zero   AS Test       CASE          WHEN  DateAddTicksZeroTicksResult    dateTimeOffsetMinValue             THEN  Pass          ELSE  Fail          END AS  Test Status         timeSpanZeroTicks AS  Ticks         dateTimeOffsetMinValue AS  Starting Date         DateAddTicksZeroTicksResult AS  Actual Result         dateTimeOffsetMinValue AS  Expected Result  UNION ALL SELECT  Date Add with Ticks Test  Negative   AS Test       CASE          WHEN  DateAddTicksNegativeTicksResult    dateTimeOffsetMinValue             THEN  Pass          ELSE  Fail          END AS  Test Status         dateTimeOffsetMaxMinDiffTicks AS  Ticks         dateTimeOffsetMaxValue AS  Starting Date         DateAddTicksNegativeTicksResult AS  Actual Result         dateTimeOffsetMinValue AS  Expected Result   -- Ticks Date Diff Test PRINT  Testing Date Diff Ticks      DECLARE  dateDiffTicksMinMaxResult bigint DECLARE  dateDiffTicksMaxMinResult bigint  SET  dateDiffTicksMinMaxResult   dbo DateDiffTicks  dateTimeOffsetMinValue   dateTimeOffsetMaxValue  SET  dateDiffTicksMaxMinResult   dbo DateDiffTicks  dateTimeOffsetMaxValue   dateTimeOffsetMinValue   -- Test Results SELECT  Date Difference in Ticks Test  Min  Max   AS Test       CASE          WHEN  dateDiffTicksMinMaxResult    dateTimeOffsetMinMaxDiffTicks             THEN  Pass          ELSE  Fail          END AS  Test Status         dateTimeOffsetMinValue AS  Starting Date         dateTimeOffsetMaxValue AS  Ending Date         dateDiffTicksMinMaxResult AS  Actual Result         dateTimeOffsetMinMaxDiffTicks AS  Expected Result  UNION ALL SELECT  Date Difference in Ticks Test  Max  Min   AS Test       CASE          WHEN  dateDiffTicksMaxMinResult    dateTimeOffsetMaxMinDiffTicks             THEN  Pass          ELSE  Fail          END AS  Test Status         dateTimeOffsetMaxValue AS  Starting Date         dateTimeOffsetMinValue AS  Ending Date         dateDiffTicksMaxMinResult AS  Actual Result         dateTimeOffsetMaxMinDiffTicks AS  Expected Result   PRINT  Tests Complete   GO --- END Test Harness ---

User · Answer

If you don t have to store more than 24 hours you can just store time  since SQL Server 2008 and later the mapping is   time  SQL Server   lt - gt  TimeSpan  NET   No conversions needed if you only need to store 24 hours or less   Source  http   msdn microsoft com en-us library cc716729 v vs 110  aspx  But  if you want to store more than 24h  you are going to need to store it in ticks  retrieve the data and then convert to TimeSpan  For example  int timeData   yourContext yourTable FirstOrDefault    TimeSpan ts   TimeSpan FromMilliseconds timeData

User · Answer

I d store it in the database as a BIGINT and I d store the number of ticks  eg  TimeSpan Ticks property    That way  if I wanted to get a TimeSpan object when I retrieve it  I could just do TimeSpan FromTicks value  which would be easy

User · Answer

There isn t a direct equivalent  Just store it numerically  e g  number of seconds or something appropriate to your required accuracy

User · Answer

I know this is an old question  but I wanted to make sure a couple of other options are noted   Since you can t store a TimeSpan greater than 24 hours in a time sql datatype field  a couple of other options might be    Use a varchar xx  to store the ToString of the TimeSpan  The benefit of this is the precision doesn t have to be baked into the datatype or the calculation   seconds vs milliseconds vs days vs fortnights  All you need to to is use TimeSpan Parse TryParse  This is what I would do  Use a second date  datetime or datetimeoffset  that stores the result of first date   timespan  Reading from the db is a matter of TimeSpan x   SecondDate - FirstDate  Using this option will protect you for other non  NET data access libraries access the same data but not understanding TimeSpans  in case you have such an environment

User · Answer

Thanks for the advice  As there is no equivalent in SQL server  I simply created a 2nd field which converted the TimeSpan to ticks and stored that in the DB  I then prevented storing the TimeSpan  public Int64 ValidityPeriodTicks   get  set      NotMapped  public TimeSpan ValidityPeriod       get   return TimeSpan FromTicks ValidityPeriodTicks         set   ValidityPeriodTicks   value Ticks

User · Answer

I would store the timespan TotalSeconds in a float and then retrieve it using Timespan FromSeconds totalSeconds   Depending on the resolution you need you could use TotalMilliseconds  TotalMinutes  TotalDays  You could also adjust the precision of your float in the database  It s not an exact value    but the nice thing about this is that it s easy to read and calculate in simple queries

User · Answer

There are multiple ways how to present a timespan in the database    time  This datatype is supported since SQL Server 2008 and is the prefered way to store a TimeSpan  There is no mapping needed  It also works well with SQL code    public TimeSpan ValidityPeriod   get  set      However  as stated in the original question  this datatype is limited to 24 hours    datetimeoffset  The datetimeoffset datatype maps directly to System DateTimeOffset  It s used to express the offset between a datetime datetime2 to UTC  but you can also use it for TimeSpan    However  since the datatype suggests a very specific semantic  so you should also consider other options   datetime   datetime2  One approach might be to use the datetime or datetime2 types  This is best in scenarios where you need to process the values in the database directly  ie  for views  stored procedures  or reports  The drawback is that you need to substract the value DateTime 1900 01 01 00 00 00  from the date to get back the timespan in your business logic    public DateTime ValidityPeriod   get  set      NotMapped  public TimeSpan ValidityPeriodTimeSpan       get   return ValidityPeriod - DateTime 1900 01 01 00 00 00         set   ValidityPeriod   DateTime 1900 01 01 00 00 00    value        bigint  Another approach might be to convert the TimeSpan into ticks and use the bigint datatype  However  this approach has the drawback that it s cumbersome to use in SQL queries    public long ValidityPeriod   get  set      NotMapped  public TimeSpan ValidityPeriodTimeSpan       get   return TimeSpan FromTicks ValidityPeriod         set   ValidityPeriod   value Ticks        varchar N   This is best for cases where the value should be readable by humans  You might also use this format in SQL queries by utilizing the CONVERT datetime  ValidityPeriod  function  Dependent on the required precision  you will need between 8 and 25 characters    public string ValidityPeriod   get  set      NotMapped  public TimeSpan ValidityPeriodTimeSpan       get   return TimeSpan Parse ValidityPeriod         set   ValidityPeriod   value ToString  HH mm ss          Bonus  Period and Duration  Using a string  you can also store NodaTime datatypes  especially Duration and Period  The first is basically the same as a TimeSpan  while the later respects that some days and months are longer or shorter than others  ie  January has 31 days and February has 28 or 29  some days are longer or shorter because of daylight saving time   In such cases  using a TimeSpan is the wrong choice    You can use this code to convert Periods   using NodaTime  using NodaTime Serialization JsonNet   internal static class PeriodExtensions       public static Period ToPeriod this string input                var js   JsonSerializer Create new JsonSerializerSettings             js ConfigureForNodaTime DateTimeZoneProviders Tzdb           var quoted   string Concat        input                  return js Deserialize lt Period gt  new JsonTextReader new StringReader quoted               And then use it like  public string ValidityPeriod   get  set      NotMapped  public Period ValidityPeriodPeriod       get   gt  ValidityPeriod ToPeriod        set   gt  ValidityPeriod   value ToString        I really like NodaTime and it often saves me from tricky bugs and lots of headache  The drawback here is that you really can t use it in SQL queries and need to do calculations in-memory   CLR User-Defined Type  You also have the option to use a custom datatype and support a custom TimeSpan class directly  See CLR User-Defined Types for details    The drawback here is that the datatype might not behave well with SQL Reports  Also  some versions of SQL Server  Azure  Linux  Data Warehouse  are not supported    Value Conversions  Starting with EntityFramework Core 2 1  you have the option to use Value Conversions    However  when using this  EF will not be able to convert many queries into SQL  causing queries to run in-memory  potentially transfering lots and lots of data to your application    So at least for now  it might be better not to use it  and just map the query result with Automapper

User · Answer

Now  with EF Core you can convert data type transparently in your AppDbContext protected override void OnModelCreating ModelBuilder modelBuilder             i e  Store TimeSpan as string  custom        modelBuilder          Entity lt YourClass gt             Property x   gt  x YourTimeSpan           HasConversion              timeSpan   gt  timeSpan ToString       To DB             timeSpanString   gt  TimeSpan Parse timeSpanString     From DB                    i e  Store TimeSpan as string  using TimeSpanToStringConverter      modelBuilder          Entity lt YourClass gt             Property x   gt  x YourTimeSpan           HasConversion new TimeSpanToStringConverter               i e  Store TimeSpan as number of ticks  custom        modelBuilder          Entity lt YourClass gt             Property x   gt  x YourTimeSpan           HasConversion              timeSpan   gt  timeSpan Ticks     To DB             timeSpanString   gt  TimeSpan FromTicks timeSpanString     From DB                    i e  Store TimeSpan as number of ticks  using TimeSpanToTicksConverter      modelBuilder          Entity lt YourClass gt             Property x   gt  x YourTimeSpan           HasConversion new TimeSpanToTicksConverter

User · Answer

To be consistent with what is probably the most likely source of generating a time span  computing the difference of 2 times or date-times   you may want to store a  NET TimeSpan as a SQL Server DateTime Type     This is because in SQL Server  the difference of 2 DateTime s  Cast to Float s and then Cast back to a DateTime  is simply a DateTime relative to Jan  1  1900   Ex  A difference of  0 1 second would be January 1  1900 00 00 00 100 and -0 1 second  would be Dec  31  1899 23 59 59 900     To convert a  NET TimeSpan to a SQL Server DateTime Type  you would first convert it to a  NET DateTime Type by adding it to a DateTime of Jan  1  1900    Of course  when you read it into  NET from SQL Server  you would first read it into a  NET DateTime and then subtract Jan  1  1900 from it to convert it to a  NET TimeSpan   For use cases where the time spans are being generated from SQL Server DateTime s and within SQL Server  i e  via T-SQL  and SQL Server is prior to 2016  depending on your range and precision needs  it may not be practical to store them as milliseconds  not to mention Ticks  because the Int Type returned by DateDiff  vs  the BigInt from SS 2016  s DateDiff Big  overflows after  24 days worth of milliseconds and  67 yrs  of seconds   Whereas  this solution will handle time spans with precision down to 0 1 seconds and from -147 to  8 099 yrs    WARNINGS     This would only work if the difference relative to Jan  1  1900 would result in a value within the range of a SQL Server DateTime Type  Jan  1  1753 to Dec  31  9999 aka -147 to  8 099 yrs    We don t have to worry near as much on the  NET TimeSpan side  since it can hold  29 k to  29 k yrs   I didn t mention the SQL Server DateTime2 Type  whose range  on the negative side  is much greater than SQL Server DateTime s   because  a  it cannot be converted to a numeric via a simple Cast and b  DateTime s range should suffice for the vast majority of use cases  SQL Server DateTime differences computed via the Cast - to - Float - and - back method does not appear to be accurate beyond 0 1 seconds

[.net] What is the correct SQL type to store a .Net Timespan with values > 24:00:00?

Examples related to .net

Examples related to sql-server

Examples related to timespan