SQL server: How can I convert dates to base 36 annexed (3-digit)?

Question

1/1/2018 is default set as 000. Example provided: 31/1/2018 is converted to 00U.

The conversion MUST same as how "00U" is converted. Below is how I select the date from my_packing table:

select convert(varchar(10), shipDTime, 103) 
from my_packing (nolock) where packNo='123';

output: 31/01/2018

desired output: 00U

Sample data, desired results, and an appropriate database tag would all help. — Gordon Linoff, Jun 30 '20 at 10:45
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product and date/time functions are highly vendor specific. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) — , Jun 30 '20 at 12:34
edited the question. @GordonLinoff the sample is in the question, which I need to change any date after 1/1/2018 into base 36. 1/1/2018 is set as 000. then 31/1/2018 is converted into 00U using base36. — CSYKAIFA, Jul 01 '20 at 01:44
can you show some of your sample data that you are trying to convert? — Harry, Jul 01 '20 at 01:58
@Harry just dates, any dates after 1/1/2018. Ex:06/03/2020, 05/06/2019,04/08/2018... But it must be using the same concept as how 31/1/2018 is converted to 00U( this is the example conversion provided by the provider). — CSYKAIFA, Jul 01 '20 at 02:08

CSYKAIFA · Answer 1 · 2020-07-02T01:18:45.673

1

enter image description here

Tq @Fandango's answer, manage to get 00U at the 31th row using the code below:

WITH
  num AS (SELECT TOP 36 ROW_NUMBER() OVER(ORDER BY (SELECT 1)) i FROM rt_packing),
  chr AS (SELECT i,CASE WHEN i <= 10 THEN CHAR(i+47) ELSE CHAR (i+54) END c FROM num)
SELECT t2.c + t1.c + t0.c 
AS dateindex
FROM chr t2, chr t1, chr t0
ORDER BY  t2.i, t1.i, t0.i

Currently, still figuring out how to select the 31th row if my date in "rt_packing" table is 31/1/2018. Anyone know?

edited Jul 02 '20 at 01:18

answered Jul 01 '20 at 05:39

CSYKAIFA

17
4

Sorry to pester again, but you should not answer your own question, with another question. Please read up on SO guidelines before posting. But you're on the right track. – Fandango68 Jul 02 '20 at 01:46
Tq for your comment, I question because it's the steps to get the final answer of my question, since my question at the top is how to convert normal date to base 36 using sql server, not just how base 36 conversion work. @Fandango68 – CSYKAIFA Jul 03 '20 at 08:39

CSYKAIFA · Accepted Answer · 2020-07-06T03:47:31.493

Here is the final answer, I get help from my colleague to complete it. Use "View" to store the temporary data and " DATEDIFF" to get numbers of days. Tq stackoverflow community!

WITH num
AS (SELECT TOP 36 ROW_NUMBER() OVER(ORDER BY (SELECT 1)) i FROM my_packing),
  chr AS (SELECT i,CASE WHEN i <= 10 THEN CHAR(i+47) ELSE CHAR (i+54) END c FROM num)
SELECT ROW_NUMBER () OVER (ORDER BY t2.c + t1.c +t0.c) as gg, t2.c +t1.c +t0.c
AS dateindex
INTO #t
FROM chr t2, chr t1, chr t0
ORDER BY  t2.i, t1.i, t0.i

SELECT  dateindex from #t
where gg in (select DATEDIFF(DAY, '2016/12/31', printDTime) AS DateDiff from my_packing(nolock)
where prdNo = '1234')

Good job and thanks for understanding being new here. – Fandango68 Jul 07 '20 at 02:20 — Fandango68, Jul 07 '20 at 02:20

score 0 · Answer 3 · answered Jul 01 '20 at 02:06

First, you need to understand how to generate a Base36 number in SQL (SQL Server in this case):

Here is an example, thanks to a previous question.

WITH num
AS (SELECT TOP 36 ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS i
    FROM master.dbo.spt_values),
     chr
AS (SELECT i, CASE WHEN i <= 10 
                   THEN CHAR(i+47) 
                   ELSE CHAR(i+54) 
              END AS c
    FROM num)

SELECT t3.c + t2.c + t1.c + t0.c
FROM chr AS t3, chr AS t2, chr AS t1, chr AS t0
ORDER BY t3.i, t2.i, t1.i, t0.i

I won't explain the code, as I am getting my head around it as well. However, it uses a master table to get a sequence of numbers, starting from '1'. It's also using CTE.

Now you need to use this in a way, to convert datetime values to Base36. Think of your datetime as a value, which it is, like 4678.3783 for example. The integer part is the date component. The decimal part is the time component.

Tq for your answer, it really create 00U at the 31 number when I delete "t3". I just need to convert the date to base 36, the time is ignored. How? — CSYKAIFA, Jul 01 '20 at 05:12

SQL server: How can I convert dates to base 36 annexed (3-digit)?

3 Answers3