How to replicate c# datetime integer value in c#

Question

Is there a way to replicate this integer value in c#?

select cast(getdate() as int)
-->
41827

I tried this, but it is not consistent with the sql date:

public string SqlDate { 
    get 
    {
        double x = (DateTime.Now - DateTime.FromOADate(0)).TotalDays;
        return Math.Floor(x).ToString();
    } 
}

In what way is it not consistent, how many days is it off? – CodeCaster Jul 08 '14 at 21:47 — CodeCaster, Jul 08 '14 at 21:47
What database are you using? – Bob Jarvis - Слава Україні Jul 08 '14 at 21:49 — Bob Jarvis - Слава Україні, Jul 08 '14 at 21:49
What is the underlying problem you are trying to solve? – Pleun Jul 08 '14 at 21:56 — Pleun, Jul 08 '14 at 21:56

score 1 · Answer 1 · answered Jul 08 '14 at 21:54

Kind of random code, but this does it:

void Main()
{
    int n = NumberOfDays(DateTime.Now);
    Console.WriteLine(n);
}

static int NumberOfDays(DateTime date)
{
  DateTime start = DateTime.Parse("12/31/1899");
  TimeSpan t = date - start;
  return (int)t.TotalDays;
}

Bascially, casting to int seems to give the number of days from 12/31/1899.

As a side note, this does not seem like a good thing to depend on or use.

Nicholas Carey · Accepted Answer · 2014-07-08T23:12:18.013

A SQL Server datetime value is internally a tuple, holding 2 signed 32-bit integers:

The high order integer is the offset, in whole days, from the epoch (zero point) of the SQL Server calendar, which happens to be 1 Jan 1900 00:00:00.000.
The low order integer is the offset from start-of-day, not in milliseconds, but in "ticks" of approximately 1/300 of a second.

getdate() returns the current date and time of day as a datetime value. The expression cast( getdate() as int ) is exactly equivalent to

datediff(day,'1 Jan 1900 00:00:00.000',getdate())

This query

declare @epoch datetime = '4 July 2014 00:00:01.000'
select [raw]            = @epoch ,
       [cast]           = cast(@epoch as int) ,
       [datediff]       = datediff(day,'1 Jan 1900',@epoch) ,
       [highorder-word] = convert(int,substring( convert(varbinary(8),@epoch),1,4) ) ,
       [low-order-word] = convert(int,substring( convert(varbinary(8),@epoch),4,8) )

Produces the following results:

raw                     cast  datediff highorder-word low-order-word
----------------------- ----- -------- -------------- --------------
2014-07-04 00:00:01.000 41822 41822         41822           300

[You'll notice that 1 second is exactly 300 ticks. There are historic reasons for why SQL Server counts time-of-day such an odd way. As I understand it, it goes back to the days of Sybase SQL Server and the poor clock resolution on early Windows and OS/2 boxen.

Given all that, you can get the same count (days since 1900) like this in C#:

public string SqlDate
{
  get { return DaysSince1900( DateTime.Now ).ToString() ; }
}

private int DaysSince1900( DateTime now )
{
  TimeSpan period = now.Date - SqlServerEpoch ;
  return period.Days ;
}
static readonly DateTime SqlServerEpoch = new DateTime( 1900 , 1 , 1 ) ;

Hargrovm · Answer 3 · 2014-07-09T18:01:42.577

In t-sql GetDate() returns a datetime which stores the date as the number of days since 01/01/1753 the time is stored as the fractional part of a day.

Hence Select Cast(GetDate() as int) will return the number of days since 1/1/1900

In contrast the c# structure stores the datetime as the number of days since 01/01/0001 plus the time as a fractional part of a day.

So in order to translate between the two you just need to take into account the offset number of days between 01/01/0001 and 01/01/1900

Alternatively if you don't want to work with offsets and you are using SQL Server 2008 or later then you can replace the GETDATE() function with SYSDATETIME() which returns a datetime2 which also uses as a reference 01/01/0001 so the c# date and t-sql dates will be referenced to the same datum and so they give the same number for the same date when cast to an int.

The count you get is days since the **epoch** (1 Jan 1900), *not* since 1 January 1753. That date is just the smallest date that SQL Server can represent. While earlier dates could be represented, but due to the conversion from the Julian to Gregorian calendar, that date was picked as the cutoff. And even that's not accurate since different countries (and even cities!) changed over at vastly different times. For instance, Russia didn't convert until 1918 and Greece, not until 1924! Without knowing the locale, an *accurate* conversion from the Julian to Gregorian calendar isn't possible. — Nicholas Carey, Jul 08 '14 at 23:10

How to replicate c# datetime integer value in c#

3 Answers3