15,884,635 members
See more:
Hi,
I need to select and convert column from integer to date?

In table: 830819228 I know that is 29.04.2016 23:07 but how I can convert it?
830858675 that is 30.04.2016 10:04
924169770 that is 15.04.2019 09:49

What I have tried:

I try everything but nothing work.
Posted
Updated 20-Jan-21 20:32pm
RickZeeland 13-Jan-21 13:37pm
What database are you using, SQL Server, PostgreSQL, MySQL ... ?

## Solution 1

It's actually 15.04.2019 09:49:30

924169770 is the number of seconds since 1990.01.01 00:00:00

Maciej Los 14-Jan-21 2:27am
5ed!

Are you wizard, Gerry?

:D

## Solution 2

I would start by looking at the code that generates the value - and by hunting down the developer who thought it was a good idea - and working out how a date is converted to that in the first place.

It looks like it's a number of seconds since a fixed time in the past which would be sensible if a DateTime in SQL wasn't stored as a number of ticks since 1753-01-01 00:00.0000 and a tick wasn't 0.00333 seconds. Sort of - it's stored as two separate counts in two 4 byte integers, one signed for the days offset and the other unsigned for the time offset.

If you can't find the code, then work out what the start date and time is, and go from there.

## Solution 3

```29.04.2016 23:07 is equal to 42487.9631944444
The query is converting float to datetime is
SELECT CAST(42487.9631944444 as dateTime)
and converting datetime to float is
Select (CONVERT(float, CAST('04-29-2016 23:07 ' AS DATETIME)))```

CHill60 21-Jan-21 3:49am
The OP does not have float values, they have bigint. This code produces an exception
```Msg 8115, Level 16, State 2, Line 5
Arithmetic overflow error converting expression to data type datetime.```

I applaud your desire to be helpful, but this solution is off-topic for this problem.
Arbaz-Ahmed7 21-Jan-21 5:03am
Try this
SELECT CONVERT(datetime, dateadd(ss, 830858675 , '1990.01.01 00:00:00'))