How to Cast today data as bigint to Data – 202104171830

All we need is an easy explanation of the problem, so here it is.

I have got today’s data as Bigint: 202104171830. How could I cast this Bigint value to today’s date ? Please help. MSSQL

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

if only date:

select convert(date, substring('202104171830',1,8))

Method 2

  1. First use CAST() to convert it to a string representing that same value
  2. Then construct a new string my using SUBSTRING() to pull out the parts and add punctuation as needed, i.e. SUBSTRING(@TimestampIntAsString, 1, 4) to pull out the year, so you end up with '2021-04-17 18:30'
  3. Then the CAST() function will recognise that as an ISO8601-ish datetime that it can convert to a proper DATETIME value.

Just make '2021-04-17' and cast to DATE if you don’t need the time portion.

IIRC none of the conversion functions in SQL Server will recognise a datetime without punctuation as a valid date representing string, as some systems will, so you can’t avoid the tedious mucking about with SUBSTRING().

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply