## Phil Factor's Phrenetic Phoughts: The SQL

### Silly Billiseconds

You have to admire Microsoft for sticking to their guns, even when being palpably stupid. I refer, of course to the SqlDateTime structure constructor in .NET 2 through to 4. See here

public SqlDateTime(

int year,

int month,

int day,

int hour,

int minute,

int second,

int bilisecond
)

Yes, you read that right: Bilisecond. This is odd since the structure only has an accuracy of 3.33 milliseconds. Since 2006, this has been a joke amongst .NET developers, and caused some bewilderment to Mono. There is, of course, no such thing as a bilisecond, however much the Microsoft people want to change the English Language. It isn’t a billionth of a second. An American billionth of a second is called a nanosecond (10-9), and a british billionth of a second is a picoseconds which is 10-12 of a second. (an american trillionth), and there is a microsecond which is equal to one millionth (10-6) of a second (1000 nanoseconds). We than have the millisecond, which is a thousandth (1/1,000) of a second. You will sometimes hear of the centisecond, which is 10 milliseconds (a hundredth of a second). Here is the whole list

ValueSymbolName
10-1dsdecisecond
10-2cscentisecond
10-3msmillisecond
10-6µsmicrosecond
10-9nsnanosecond
10-12pspicosecond
10-15fefemtosecond
10-18asattosecond
10-21zszeptosecond
10-24ysyoctosecond

In the case of the Billisecond, or bilisecond, we think it is a microsecond. The Dateime2 DataType of SQL Server is supposedly accurate to 100 nanoseconds, so it could be that a bilisecond was intended to be the same as 100 nanoseconds.

So the next question is this; How many seconds has this error been in the documentation unfixed? Here is our SQL Calculator.

DECLARE  @SecondsSinceBili BIGINT
SELECT @SecondsSinceBili=DATEDIFF(second,'07 Nov 2005' ,GETDATE())
SELECT @SecondsSinceBili/1000000.00 AS MegaSeconds

`MegaSeconds---------------------------------------163.6097190000`

##### Posted by Adam Machanic on 13 January 2011

I'm confused. Per your code, @MinutesSinceBili is the number of seconds since Bili. If you're going to throw stones... ;-)

##### Posted by Phil Factor on 13 January 2011

Well-spotted! I've fixed the source. I tried an approach using minutes because I thought I had an overflow problem, and forgot to change the name of the variable back when I discovered I hadn't.

##### Posted by Peter Lavelle-397610 on 16 January 2011

In practice no one has used the old British billion or trillion for about the last 40 years even though they may still be lingering in the literature.

Everyone in I.T., Finance and Science (at least) interprets them to be the same as the American definition.

##### Posted by gsimard on 27 January 2011

Everyone speaking english (US, UK or others) uses the short scale (Millionth, Billionth, Trillionth ...) but most other languages use the long scale (Millionth, Milliardth, Billionth, Billiardth ...).

But in neither case does the Billisecond exists. Good point.

##### Posted by Lawrenc450 on 26 January 2012

You're right! It seems that a "Billisecond" is a "100-nanosecond."

From Microsoft:

Active Directory stores date/time values as the number of 100-nanosecond intervals that have elapsed since the 0 hour on January 1, 1601 till the date/time that is being stored.

support.microsoft.com/.../555936

Furthermore, you can use something like this to SELECT these values in SQL Server:

WITH [Constants] AS

(

SELECT CAST('116444736000000000' AS BIGINT) AS [BaseInt], --Epoch based on 1/1/1970

CAST('10000000' AS DECIMAL) AS [ConvSecs] --Number of AD-Billiseconds / Second

)

SELECT

DATEADD(second, FLOOR(((CAST(ISNULL(128271382742968750, [BaseInt]) AS BIGINT) - [BaseInt]) / [ConvSecs])), '1/1/1970')

AS [LastLogonDateTest]

FROM [Constants]

##### Posted by Lawrenc450 on 26 January 2012

After further thought, I think I found out why the constructor says "biliseconds".

I wonder if the function converts the year, month, day, hour, minute, second to "billiseconds".

The function could already be using the variable "billiseconds", so the constructor would need to be different and thus have only one L.

What do you think?