Click here to Skip to main content
15,351,641 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Comments
RickZeeland 13-Jan-21 13:37pm
   
What database are you using, SQL Server, PostgreSQL, MySQL ... ?

It's actually 15.04.2019 09:49:30

924169770 is the number of seconds since 1990.01.01 00:00:00
   
Comments
Maciej Los 14-Jan-21 2:27am
   
5ed!

Are you wizard, Gerry?

:D
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.
   
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)))
   
Comments
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'))

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900