Convert SQL query to C# (SQL query converts base36 code to DateTime)

Question

I'd like to be able to encode \ decode id's containing the datetime in a 7 digit \ base36 configuration, but despite having a SQL query that decodes Id's, so far have had no luck.

I have a SQL query that is able to convert the code to a date time.

Using the following ids, I'm hoping to get the corresponding datetimes.

    id          Date Time
    ------------------------------------
    A7LXZMM     2004-02-02 09:34:47.000
    KWZKXEX     2018-11-09 11:15:46.000
    LIZTMR9     2019-09-13 11:49:46.000

Query:

DECLARE @xdate DATETIME, @offset INT
DECLARE @recid VARCHAR(20)

SET @recid = 'KWZKXEX'
SET @offset = (SELECT DATEDIFF(ss, GETUTCDATE(), GETDATE())) /************* Number of hours offset from GMT ************/

SELECT 
    DATEADD(ss, @offset +
                (POWER(CAST(36 AS BIGINT), 6) *
                 CASE
                    WHEN (SELECT ISNUMERIC(SUBSTRING(@recid, 1, 1))) = 0
                       THEN (SELECT ASCII(SUBSTRING(@recid, 1, 1))) - 55
                       ELSE (SELECT ASCII(SUBSTRING(@recid, 1, 1))) - 48
                 END +
                 POWER(CAST(36 AS BIGINT), 5) *
    case
    when(select isnumeric(substring(@recid,2,1))) = 0
    then(select ascii(substring(@recid,2,1))) - 55
    else (select ascii(substring(@recid,2,1))) - 48
    End
    +
    POWER(cast(36 as bigint),4) *
    case
    when(select isnumeric(substring(@recid,3,1))) = 0
    then(select ascii(substring(@recid,3,1))) - 55
    else (select ascii(substring(@recid,3,1))) - 48
    End
    +
    POWER(cast(36 as bigint),3) *
    case
    when(select isnumeric(substring(@recid,4,1))) = 0
    then(select ascii(substring(@recid,4,1))) - 55
    else (select ascii(substring(@recid,4,1))) - 48
    End
    +
    POWER(cast(36 as bigint),2) *
    case
    when(select isnumeric(substring(@recid,5,1))) = 0
    then(select ascii(substring(@recid,5,1))) - 55
    else (select ascii(substring(@recid,5,1))) - 48
    End
    +
    POWER(cast(36 as bigint),1) *
    case
    when(select isnumeric(substring(@recid,6,1))) = 0
    then(select ascii(substring(@recid,6,1))) - 55
    else (select ascii(substring(@recid,6,1))) - 48
    End
    +
    POWER(cast(36 as bigint),0) *
    case
    when(select isnumeric(substring(@recid,7,1))) = 0
    then(select ascii(substring(@recid,7,1))) - 55
    else (select ascii(substring(@recid,7,1))) - 48
    End
    )
    /50
    ,'1/1/1990')

using System;
using System.Globalization;         
using System.Text;
using System.Numerics;

public class Program
{
    public static void Main()
    {

        string sRecid = "A7LXZMM";
        char c0 = sRecid[0];
        char c1 = sRecid[1];
        char c2 = sRecid[2];
        char c3 = sRecid[3];
        char c4 = sRecid[4];
        char c5 = sRecid[5];
        char c6 = sRecid[6];

        double d6, d5, d4, d3, d2, d1, d0, dsecs;

        Console.WriteLine("c0 = " + c0.ToString());
Console.WriteLine();

        d6 = Math.Pow(36, 6) * ((Char.IsNumber(c0)) ? (byte)c0 - 55 : (byte)c0 - 48);
        d5 = Math.Pow(36, 5) * ((Char.IsNumber(c1)) ? (byte)c1 - 55 : (byte)c1 - 48);
        d4 = Math.Pow(36, 4) * ((Char.IsNumber(c2)) ? (byte)c2 - 55 : (byte)c2 - 48);
        d3 = Math.Pow(36, 3) * ((Char.IsNumber(c3)) ? (byte)c3 - 55 : (byte)c3 - 48);
        d2 = Math.Pow(36, 2) * ((Char.IsNumber(c4)) ? (byte)c4 - 55 : (byte)c4 - 48);
        d1 = Math.Pow(36, 1) * ((Char.IsNumber(c5)) ? (byte)c5 - 55 : (byte)c5 - 48);
        d0 = Math.Pow(36, 0) * ((Char.IsNumber(c6)) ? (byte)c6 - 55 : (byte)c6 - 48);


        dsecs = d6 + d5 + d4 +  d3 +  d2 +  d1 +  d0 / 50;

        DateTime dt = new DateTime(1990, 1, 1, 0, 0, 0,0, System.DateTimeKind.Utc);
        dt =  dt.AddSeconds( dsecs ).ToLocalTime();

    Console.WriteLine("d6 = " + d6.ToString());
    Console.WriteLine("d5 = " + d5.ToString());
    Console.WriteLine("d4 = " + d4.ToString());
    Console.WriteLine("d3 = " + d3.ToString());
    Console.WriteLine("d2 = " + d2.ToString());
    Console.WriteLine("d1 = " + d1.ToString());
    Console.WriteLine("d0 = " + d0.ToString());
    Console.WriteLine("dsecs = " + dsecs.ToString());
    Console.WriteLine("dt = " + dt.ToString());
    }
}

When I use the following Ids in SQL, I get the following dates.

    id          Date Time
    ------------------------------------
    A7LXZMM     2004-02-02 09:34:47.000
    KWZKXEX     2018-11-09 11:15:46.000
    LIZTMR9     2019-09-13 11:49:46.000

Unfortunately my C# "conversion" is wildly inaccurate.

Any suggestions as to where I'm going wrong?

Answer 1

you have the Char.IsNumber... checks flipped in your C# code compared to your SQL script.

In your SQL, you're subtracting 55 if the character is not a number, and 48 otherwise.

In your C# code you're subtracting 55 if the character is a number, and 48 otherwise.

You're also not calculating dsecs correctly I don't think. You need to add d6 through d0 then divide by 50. The way you have it now, you'll divide d0 by 50 then add all the other dn variables.

In other words...

dsecs = d6 + d5 + d4 + d3 + d2 + d1 + d0 / 50;

Should be

dsecs = (d6 + d5 + d4 + d3 + d2 + d1 + d0) / 50;