Convert SQL BigInt as 1811011439 to date time format

Question

My Datetime row is look something like this format

It can read like this

Y. 18
M. 11
D. 01

at the first line as you see

Could any please guide me how can I convert it to date readable format.

That sure looks like a very strange "datetime" value - what is the logic behind it? — , Jan 14 '19 at 07:08
Possible duplicate of [Convert From Bigint to datetime value](https://stackoverflow.com/questions/3650320/convert-from-bigint-to-datetime-value) — Prashant Pimpale, Jan 14 '19 at 07:10
The `BIGINT` number here represent what? milliseconds? seconds? what's the logic here? Also what did you try to solve it? — Ilyes, Jan 14 '19 at 07:16
The correct thing to do is to change how that data is stored - if it's a datetime value it should be stored as a DateTime2, not as anything else. — Zohar Peled, Jan 14 '19 at 08:48

Sebastian Brosch · Accepted Answer · 2019-01-14T07:31:52.690

You want to convert a string represantation of the datetime value (stored as bigint) to a readable format. In this case you can CAST the date part of the string / bigint to a DATE value:

SELECT CAST(LEFT(CAST(1811011439 AS VARCHAR), 6) AS DATE)
-- 2018-11-01

I recommend to use a DATETIME column instead of a BIGINT column to store values likes this. With the following SELECT you get all the DATETIME values:

SELECT CAST(SUBSTRING(CAST(1811011439 AS VARCHAR), 1, 6) + ' ' + SUBSTRING(CAST(1811011439 AS VARCHAR), 7, 2) + ':' + SUBSTRING(CAST(1811011439 AS VARCHAR), 9, 2) AS DATETIME)
-- 2018-11-01 14:39:00.000

score 2 · Answer 2 · answered Jan 14 '19 at 07:22

As far as I understand the request, your date in VARCHAR format as 'YYMMDDHHmm', so, the easiest way is:

DECLARE @TextDate AS NVARCHAR(20) = '1811011439';
SELECT CAST(SUBSTRING(@TextDate, 1, 6) AS DATE);

Or if that's in a field in one table:

SELECT CAST(SUBSTRING(YourField, 1, 6) AS DATE) FROM YourTable;

Ilyes · Answer 3 · 2019-01-14T09:00:56.387

I think your column isn't a BIGINT datatype, it's VARCHAR and you want to split the date from it

SELECT CAST(LEFT('1811011439', 6) AS DATE)

If it's really a BIGINT column, just convert it to VARCHAR, split it and then CAST as DATE

SELECT CAST(LEFT(CAST(YourColumn AS VARCHAR), 6) AS DATE)
SELECT CAST(LEFT(YourColumn, 6) AS DATE) --It works too

Still, the correct answer to this is to use the right datatype which is DATETIME

Convert SQL BigInt as 1811011439 to date time format

3 Answers3