Convert SQL BigInt as 1811011439 to date time format

user_128618 0
Viewed 1313

My Datetime row is look something like this format

1811011439
1811011439
1811011439
1811011439
1811011439
1811011439

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.

tsql sql-server sql
3 Answers

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
user_38408400

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;
user_108145030

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

user_64266920
Related