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?

This is definitely in the top 5 of complicated ways to safe a couple of bytes. At least, I assume that's the reason for storing a date so mindbogglingly. — HoneyBadger
– HoneyBadger, Commented Sep 30, 2019 at 22:52
Nope, its not to save bytes. I'm working with a db that is 20+ years old and need the id to correspond with old data while still being url friendly. The full id is longer, I just had problems solving the date part. — Don
– Don, Commented Oct 1, 2019 at 18:51

Joshua Robinson · Accepted Answer · 2019-09-30 22:21:52Z

2

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;

edited Sep 30, 2019 at 22:21

answered Sep 30, 2019 at 22:09

Joshua Robinson

3,6179 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Don Over a year ago

Joshua, thank you very much for that. You're right. I now just need to add the offset and it works.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related