Convert date to number data type

Question

I am trying to convert date to number like below, not sure which function works better.

Database used is SQL Server.

Table details

create table test 
(
    id varchar(255),
    call_date varchar(255)
);

insert into test('26203', '14-Aug-2020');

I need output as 4405726203 -- its concatenation of date (14-Aug-2014) + id (26203)

Maure

Go to the SQL Server documentation and check out the functions available. – Tonettetoney

And where does 44057 come from? – Busman

I tried to use CAST(CONVERT(datetime,call_date) as bigint) - it returns 44055, when concatenation with id, it sums up the values. – Maure

@GordonLinoff 44057 should be the numeric or other conversion of date. – Maure

Convert them to strings before concating them. – Tonettetoney

Answer 1

This is too long for a comment.

SQL Server allows you to convert a datetime to a float. That would be:

select cast(dte as float)
from (values (convert(datetime, '14-Aug-2020'))) v(dte)

However, the corresponding floating point value is 44055 (https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=d142a64db0872e7572eb4fbd6d5d5fe7). It is a bit of mystery what your intention is.

You could subtract 2, but that seems arbitrary. You could calculate the number of days since 1899-12-30. But that also seems arbitrary.

In any case, once you figure out how to convert the date to the number you want, just use concat() to combine the values.

Busman

Answer 2

Under the hood, a SQL Server DateTime is a tuple of 2 32-bit integers:

• The first integer is a count of days since since the epoch, which for SQL Server is 1 January 1900
• The second integer is a count of milliseconds since start of day (00:00:00.000). Except that the count ticks up in 3- or 4-milliscond increments. Microsoft only knows why that decision was made.

You can get the count of days since the epoch with

convert( int, convert( date, t.call_date ) )

[wrap that in convert(varchar, ... ) to turn it into a string]

Looks like your id is already a varchar, so you can say:

select compound_key = convert(varchar,
                        convert(int,
                          convert(date,
                            call_date
                          )
                        )
                      )
                    + t.id
from test t

I would suggest padding both fields with leading zeros to a fixed length so as to avoid possible collisions (assuming you're trying to generate a key here). Signed 32-bit integer overflows a 2.1 billion-ish, so 9 digits for each field is sufficient.

Felicitasfelicitate

Answer 3

I have found the solution:

convert(varchar,CAST(CONVERT(datetime,call_date) as bigint)) + id

Maure

Always specify length for datatypes.It can lead to issues, difficult to troubleshoot. For VARCHAR, It takes default length as 30 for CAST, CONVERT. but, we should not depend on database to define length. learn.microsoft.com/en-us/sql/t-sql/data-types/… – Screak

What @VenkataramanR means is: You should always put the length in brackets behind any varchar. See Why should you always write "varchar" with the length in brackets behind it? Often, you get the right outcome without doing so and remarks under Convert date yyyy-mm-dd to integer YYYYMM. – Inextricable

Even if you wrote a style number like for example 105 or 112, the output of that would not be a fixed length, but would default to 30 as that is the database default for SQL Server. And one should not rely on such a database default. – Inextricable

Answer 4

This works

select concat(datediff(d, 0, cast(call_date as date)), id)
from 
  (values ('26203','14-Aug-2020')) v(id, call_date);

Results

4405526203

Anisette