I need help converting this date value returned by WMI-CodePudding

I run this powershell script to query for certificate bound to SQL server:

Get-WmiObject -Namespace 'ROOT\Microsoft\SqlServer\ComputerManagement14' -Class SecurityCertificate | select name,expirationdate

It properly returns the certificate's subject and expiration date as:

name                        expirationdate
----                        --------------
servername.domain.com       31052014

However, I don't know what format that date is in as the certificate shows expiration as ‎Thursday, ‎August ‎17, ‎2023 2:34:27 AM

I've googled but found no results for the appropriate type of date that's being returned and how to convert to [datetime]. Please help me to understand this so that I can convert it and use it for comparisons.

CodePudding user response：

It looks like high 32 bit word of

The certificate I generated has the following fields (as shown in Certificates.msc and certutil:

NotBefore:
- 2022-10-07 18:59:44
- 30988985
NotAfter:
- 2023-04-07 19:09:44
- 31025599

The difference between those two integer values is 36614.

The difference between those two dates is 182 days, 0 hours, 10 minutes.

Or 182d 0h (10/1440)m or 182.00694 days.

36614 / 182.00694 == 201.168, therefore 1 day (i.e. 24 hours) == 201.168 mystery-units.

Now let's find the epoch:

30988985 / 201.168 == 154,045.3
i.e. 30988985 is 154,045 days after the epoch.
As 30988985 is 2022-10-07 18:59:44, the epoch is therefore that date time, minus 154,045.3 days.
- Which is sometime around 1601-01-02 11:47:44.
- heeeeyyy that date looks kinda familiar... 1601-01-01 00:00 is the Win32 clock epoch! - so let's attribute the extra day to rounding error.
Also, 182.00694 / 36614 == 0.00497.
- i.e. Each incremental integer value in expirationdate corresponds to 0.00497 days - or 430 seconds (approx).

Therefore the conversion function (in JavaScript) is:

function convertSqlServerCertificateExpirationToDateTime( value ) {
     if( typeof value !== 'number' || value < 0 ) throw new Error( "Invalid `value` argument. Should be an integer number somewhere around ~30000000." );

     const epoch = new Date( /*y:*/ 1601, /*m:*/ 0, /*d:*/ 1, /*h:*/ 0, /*m:*/ 0, /*s:*/ 0, /*ms:*/ 0 ); // JS uses 0 for January instead of 1.

     const unitsPerDay   = 0.004970966;
     const days          = value * unitsPerDay;
     const secondsPerDay = 86400;
     const addSeconds    = days * secondsPerDay;

     const t = new Date( epoch.getTime() );
     t.setSeconds( t.getSeconds()   addSeconds );
     return t;
}

Pain....

TL;DR: run this in an elevated PowerShell locally on the box running SQL Server 2022:

PS C:\Users\Administrator> $selfSignedRootCA = New-SelfSignedCertificate -DnsName sql2022.corp.example.com -notafter (Get-Date).AddMonths(6) -CertStoreLocation Cert:\LocalMachine\My\ -KeyExportPolicy Exportable -KeyUsage CertSign,CRLSign,DigitalSignature -KeySpec KeyExchange -KeyLength 2048 -KeyUsageProperty All -KeyAlgorithm 'RSA' -HashAlgorithm 'SHA256' -Provider 'Microsoft Enhanced RSA and AES Cryptographic Provider'

The -KeySpec KeyExchange option is very important.
Use -DnsName not -Subject, and use the FQDN name.