How to convert this date value returned by WMI

Question

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]. How can I understand this so that I can convert it and use it for comparisons?

Answer 1

A value of 31052014 represents the date+time 2023-08-15T19:08:23.

TL;DR: Run this interactive JS snippet to convert WMI SecurityCertificate uint32 values to human-readable date-times:

1. Click "Show code snippet" below.
2. Scroll-down to the big blue "Run code snippet" button and click it.
3. Then enter the number (e.g. 31052014) into the input-box that appears and click the button to convert it.

function convertSqlServerCertificateExpirationToDateTime( value ) {

     //debugger;

      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 secondsSinceEpoch = value * 430;

     const t = new Date( epoch.getTime() );
     t.setSeconds( t.getSeconds() + addSeconds );
     
     console.log( "Value %o corresponds to %o", value, t );
     
     return t;
 }
 
 function doConvert() {
 
     const inputValue = document.getElementById('wmiDateTime').valueAsNumber;
     
     console.log( 'inputValue: %o', inputValue );
     
     const dt = convertSqlServerCertificateExpirationToDateTime( inputValue );
     
     document.getElementById('output1').textContent = dt.toLocaleString();
     document.getElementById('output2').textContent = dt.toISOString();
 }

output {
    font-weight: bold;
    color: purple;
    display: block;
    margin: 1em 0; }

label { display: block; }

<fieldset>
    <legend>WMI uint32 datetime converter</legend>
    
    <label>
        <span>Integer value:</span>
        <input type="number" min="0" id="wmiDateTime" />
    </label>
    
    <div>
        <button type="button" onclick="doConvert()">Convert</button>
    </div>
    
    <output id="output1"></output>
    <output id="output2"></output>

</fieldset>

---

Backstory

This interested me, so I created a new self-signed certificate and jumped through the hoops to get it working with SQL Server 2022....

• SQL Server Configuration Manager for SQL Server 2022 does now show the expiration date (but not the time, and it's local-time not UTC, gah).

• However, using a WMI CIM browser tool, such as wmiexplorer shows the uint32 values that you're describing:

  • 
  • 

• 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....

• SSL Certificate missing from dropdown in SQL Server Configuration Manager
• How to set key spec or KEYEXCHANGE property when generating a self-signed certificate using openssl
• CertUtil Import pfx failed: NTE_NOT_SUPPORTED
• New-SelfSignedCertificate on Win2012 r2 has less parameters

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.

---

Answer 2

It looks like high 32 bit word of 64 bit win32 filetime

Here is sample js to convert from it to normal values:

function fromHighW32(value) {
  let bv = BigInt(value);
  bv = bv << 32n;
  bv = bv - 116444736000000000n; // start of unix epoch
  bv = bv / 10000n; // convert from 100n to millis
  return new Date(parseInt(bv.toString(), 10));
}

console.log(fromHighW32(31052014).toUTCString());
console.log(fromHighW32(30988985).toUTCString());
console.log(fromHighW32(31025599).toUTCString());

though exact numbers are a little bit different from your output and output from @Dai answer, it is because we don't see low word, so the values are +/- 429.5 seconds (~7 minutes)