CODEWITHSUNDEEP
mysqlsqlcastingnavicat
Cris
Cris

Reputation: 3

Casting negative integer to DATE in SQL

I'm having a problem working with a Navicat Database. I got a column in SQL called fechaNacimiento (Birthdate) that should be a Date type, but instead it's stored as integers (most negative integers):

SELECT fechaNacimiento FROM Registrados

And I'm getting:

fechaNacimiento
-1451678400
-2082829392
-1798746192
-1199221200
-1356984000
-694299600
-1483214400
-1924976592
-1830368592
-2019670992
-1678909392
239252400
1451617200
-879541200

I don't know how this dates where loaded, I just know that inside that negative integer there's a date, and nobody here have any clue about how to spell SQL, so I have nobodoy to ask. If I just cast it to DATETIME, I get all of them as NULL values. Any idea in how to convert this data to Date type?

Upvotes: 0

Views: 1692

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Numbers like that make me think of Unix times, number of seconds since 1970. If so, you might be able to do:

select dateadd(second, <column>, '1970-01-01')

This would put the negative values sometime before 1970 (for instance, -1678909392 is 1916-10-19). If you have older dates, then that might be the format being used.

These might also be represented as milliseconds. If so:

select dateadd(second, <column>/1000, '1970-01-01')

In this case, -1678909392 represents 1969-12-12.

In MySQL, you would use:

select '1970-01-01' + interval floor(column / 1000) second

Upvotes: 1

Related Questions