Convert column of type Float to Date in sql-server

Question

My dates stored in a float column, I tried to change it to timestamp with no luck, now I want to calculate the age from that field, I have tried all sorts of combinations using CONVERT and CAST functions, but it does not seem to work.

DoB
-434419200000
606960000000
1332806400000
1395878400000
-87350400000
890956800000

I tried:

 SELECT DATEADD(ss, dob, '19700101')
   from tbl

   //Result
   Error overflow .. arithmetique for type int, value= -434419200000.000000

    SELECT DATEADD(ss, CONVERT(bigint, dob, 101), '19700101')
    from tbl

    //Result
     Error while conversion from expression to type int.

UPDATE

NOTE that the data here is from milliseconds from the date"1970/1/1 00:00:00"

Thank you.

What "date" does `-434419200000` represent..? All your code does above it convert from the `float` value `-434419200000` to a `varchar` (of unspecified length). It doesn't do any kind of age calculations. — Thom A, Feb 06 '20 at 11:11
It represent a date of birth in timestamp, but the column is of type float, age calculation is further step after I get the data as date. — TiyebM, Feb 06 '20 at 11:13
How did you get that value? If we don't know what date an apparently arbitrary number represents, we cannot help you. — HoneyBadger, Feb 06 '20 at 11:15
You'll have to figure out who or what is storing these values and what they mean. This approach to time stamps is not native to SQL Server; you can't get a negative number out of a date with any of the built-in types. It could be, for example, that `-434419200000` is a Unix date/time value in milliseconds, starting from 1970-01-01, so it would represent a DOB of 1956-03-27, but that's pure speculation without seeing the actual processed value. If it *is* a Unix timestamp, see [here](https://stackoverflow.com/q/2904256/4137916). — Jeroen Mostert, Feb 06 '20 at 11:15
*" age calculation is further step after I get the data as date."* hence why I ask, what date is `-434419200000`? If *you* don't know that, how can you ever hope to work out what someone's DOB is, let alone us? — Thom A, Feb 06 '20 at 11:21
It represent milliseconds from a specific date see the update, sorry that I thought it was a timestamp. — TiyebM, Feb 06 '20 at 11:24
Do these answer your question? [converting Epoch timestamp to sql server(human readable format)](https://stackoverflow.com/q/4787827/2029983) followed by [How to calculate age (in years) based on Date of Birth and getDate()](https://stackoverflow.com/q/1572110/2029983) — Thom A, Feb 06 '20 at 11:27
Really, @TiyebM ? The first question clearly shows you hiow to convert an epoch to a date, and the latter how to calculate age. how do they *not*? — Thom A, Feb 06 '20 at 11:37
@Larnu In the first one I tried `SELECT DATEADD(ss, dob, '19700101') from tbl`, it gives exception of incompatibity of int value `-434419200000.000000`, I tried to convert the float value to int, then I went through `Error while convert expression to int type`, I think the problem is related to the float data type of column. — TiyebM, Feb 06 '20 at 11:46
`-434419200000` is too small for an int, you need to use a `bigint`. — Thom A, Feb 06 '20 at 11:48
The problem is not `float` but the fact that `DATEADD` doesn't support anything but `INT` and there's no `DATEADD_BIG`. `SELECT DATEADD(SECOND, CONVERT(BIGINT, -434419200000) / 1000, '19700101')`. — Jeroen Mostert, Feb 06 '20 at 11:49

Salman A · Answer 1 · 2020-02-06T12:00:46.460

1

The correct datatype would be bigint instead of float. In fact I would rather import the timestamps in a date column.

Having said that, divide the number by 1000 to avoid overflow error:

select dateadd(second, -434419200000e0 / 1000, '1970-01-01')
-- 1956-03-27 00:00:00.000

edited Feb 06 '20 at 12:00

answered Feb 06 '20 at 11:51

Salman A

262,204
82
430
521

Thank you very much, now it works despite of the data type of column, could you please develop this to get the age from now, thank you – TiyebM Feb 06 '20 at 11:55

score 1 · Accepted Answer · answered Feb 06 '20 at 11:58

As I mention in the comments, this is a combined duplicate of the questions converting Epoch timestamp to sql server(human readable format) and How to calculate age (in years) based on Date of Birth and getDate().

Sample data:

CREATE TABLE dbo.YourTable (DoB float);
INSERT INTO dbo.YourTable (DoB)
VALUES (-434419200000),
       (606960000000),
       (1332806400000),
       (1395878400000),
       (-87350400000),
       (890956800000);

Then using the first link we get:

SELECT DATEADD(SECOND, DoB / 1000,'19700101') --As yours is milliseconds, not seconds.
FROM dbo.YourTable;

Then we can use the answer from the second link:

SELECT YT.DoB,
       V.DateOfBirth,
       CONVERT(int,DATEDIFF(yy, V.DateOfBirth, GETDATE()) + 
       CASE WHEN GETDATE() >= DATEFROMPARTS(DATEPART(yyyy, GETDATE()), DATEPART(m, V.DateOfBirth), DATEPART(d, V.DateOfBirth)) THEN (1.0 * DATEDIFF(DAY, DATEFROMPARTS(DATEPART(yyyy, GETDATE()), DATEPART(m, V.DateOfBirth), DATEPART(d, V.DateOfBirth)), GETDATE()) / DATEDIFF(DAY, DATEFROMPARTS(DATEPART(yyyy, GETDATE()), 1, 1), DATEFROMPARTS(DATEPART(yyyy, GETDATE()) + 1, 1, 1)))
            ELSE -1 * (-1.0 * DATEDIFF(DAY, DATEFROMPARTS(DATEPART(yyyy, GETDATE()), DATEPART(m, V.DateOfBirth), DATEPART(d, V.DateOfBirth)), GETDATE()) / DATEDIFF(DAY, DATEFROMPARTS(DATEPART(yyyy, GETDATE()), 1, 1), DATEFROMPARTS(DATEPART(yyyy, GETDATE()) + 1, 1, 1)))
       END) AS Age
FROM dbo.YourTable YT
     CROSS APPLY (VALUES (DATEADD(SECOND, YT.DoB / 1000, '19700101'))) V (DateOfBirth);

If this answer helps, I suggest upvoting the answers that I link above as well, as this is a combination of those said answers.

Right, thank you, I missed the division by 1000 mentioned by @SalmanA . — TiyebM, Feb 06 '20 at 12:01

Convert column of type Float to Date in sql-server

2 Answers2