I'm trying to get the date format dd/MM/YYYY or anything readable. actually I have the date data with this format stocked in a string field "1620305130000" I have tried the following

CONVERT(float, floor( CreationDate ) , 101)

convert(varchar, CreationDate ,101)

CONVERT(datetime, floor(CreationDate))

CONVERT(datetime, CreationDate)

but it didn't work

Thank you


2 Answers 2


First, let's understand what a Unix Timestamp is - it's the amount of time that has passed from 1970-01-01. This means that we need to add your CreationDate value to this starting date and we'll get the result.

We need to use the DATEADD function and here we encounter the biggest problem - your data type is mismatched: the function only accepts integers and your data provides a string that is a number that's bigger than the largest integer accepted by the SQL Server running in the back-end. Clearly that's not going to fly, so we need to work around this:

  1. We convert this string to a decimal value that allows us to to store longer numbers: Convert(decimal(13,0), CreationDate)
  2. Then we divide the number by 1000 (since the last three characters of you CreationDate represent miliseconds): Convert(decimal(13,0), CreationDate) / 1000
  3. Next we convert this to an integer required by the DATEADD function: Convert(int, Convert(decimal(13,0), CreationDate) / 1000)
  4. Finally, we can just add our new integer as a number of seconds (s) to the starting date: DateAdd(s, Convert(int, Convert(decimal(13,0), CreationDate) / 1000), '1970-01-01')

This should already output something that's readable to us, humans, but if you want to format it further, better include this transformation in a subquery and later just CONCAT different DATEPARTS

  • thank you for your explication it was helpful, but added Convert(float,CreationDate) like this select DateAdd(s, Convert(int, Convert(decimal(13,0), Convert(float,CreationDate)) / 1000), '1970-01-01') to get the date in the correct format
    – ben-man
    Commented Oct 27, 2021 at 15:26
  • Thumbs up for the explanation but, unfortunately, that will fail just one second after the date/time of “2038-01-19 03:14:07”. It's know as the "2038" or "Y2K38" problem.
    – Jeff Moden
    Commented Jul 29, 2022 at 2:06
  • Wow, @JeffModen, thank you for pointing that out, I learned something new thanks to you Commented Jul 29, 2022 at 6:01
  • @RafałWolsztyniak - Thank you for the feedback. Have a look at the post I made below for how to get around that.
    – Jeff Moden
    Commented Jul 29, 2022 at 19:49

You've got to be really careful here. Most answers that use "/1000" will get you out of the woods for an immediate need but they fall apart because of the INT limit on DATEADD just 1 second after the date of “2038-01-19 03:14:07”. It's know as the "2038 problem" or, more affectionately by us old timers, the Y2K38 problem.

If we count the number of milliseconds in a day as 246060*1000, we end up with the very stable and "integer friendly" number of 86400000 and that's less than an integer.

If we take the difference in days between the UNIX Epoch of '1970-01-01' or just '1970' for short and the maximum date in SQL Server for a DATETIME2(3) ...

SELECT DATEDIFF(dd,'1970','9999-12-31 23:59:59.999')

We get the answer of 2932896 days, which is also "integer friendly".

We also have to overcome the fact that DATEDIFF() really likes to convert string representations of dates to DATETIME and we really need our answer to be in terms of the DATETIME2(3) datatype because DATETIME has the nasty habit of rounding 9 milliseconds up to the next temporal unit. Dates before the UNIX Epoch use negative timestamp values that also work in a similar but reverse manner)

So, that means that we have to do a little bit more that working with whole seconds and milliseconds in our conversion formula. The problem formula for BIGINT UNIX Timestamps that are based on milliseconds is...

SELECT  DATEADD(ms,@msUnixTS%86400000,DATEADD(dd,@msUnixTS/86400000,CONVERT(DATETIME2(3),'1970')))

The division (/) is integer math (in this case because both parts are integers of one type or another) that calculates the number of whole days as the quotient and the modulus operator (%) calculates the remainder as the number of remaining milliseconds not included in a whole day, which is the time of day.

Since you have the added complicity of the UNIX Timestamp being string based instead of being a BIGINT, we need to do an extra conversion. Adding all of that together with a variable for the original parameter that you provided, we get the following...

DECLARE  @msUnixTS VARCHAR(20) = '1620305130000'
;        --         |<---------- The TIME ---------->| |<------------------------------ The DATE -------------------------------->|

And that's pretty much bullet proof for just about any form of a millisecond based UNIX Timestamp and doesn't suffer from the Y2K38 problem that other formulas based on seconds/milliseconds has.

It won't work for braille renditions, though. :D

  • You can, of course, "DRY" out the code using a CROSS APPLY with "Table Valued Constructors" but I wanted to show the "full monty" as a single formula including the conversion from string to BIGINT.
    – Jeff Moden
    Commented Jul 29, 2022 at 2:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .