I’m facing an issue with date conversion after transferring data from Airtable to BigQuery. While the datetime values look correct in Airtable, they turn into decimal representations in BigQuery.
Here are some examples:
Decimal Date
Correct DateTime
44493.61666667
2021-10-24T14:48:00
44522.775
2021-11-22T18:36:00
44493.67916667
2021-10-24T16:18:00
44522.73958333
2021-11-22T17:45:00
It looks like BigQuery misinterprets my date information as decimal values. The name of my table is Airtable. Could anyone provide a SQL query suggestion to correctly convert these decimals back into standard timestamps? Thanks in advance for your help!
You’re dealing with Excel serial dates - they start from January 1, 1900. Hit this same issue last year migrating spreadsheet data to BigQuery. The decimal part is time of day as a fraction of 24 hours. Here’s the SQL that worked for me:
SELECT
decimal_date,
DATETIME_ADD(
DATETIME('1900-01-01 00:00:00'),
INTERVAL CAST((decimal_date - 2) * 24 * 60 * 60 AS INT64) SECOND
) AS converted_datetime
FROM `your_project.your_dataset.Airtable`
The minus 2 fixes Excel’s leap year bug and accounts for the difference between Excel’s epoch and standard date calculations. Should get you the right datetime format.
Had this same issue migrating legacy systems to BigQuery. Those decimals are days since December 30, 1899 - it’s the OLE Automation Date format Airtable uses internally. The whole number is the date offset, the decimal part is time.
SELECT
decimal_date,
DATETIME_ADD(
DATETIME('1899-12-30 00:00:00'),
INTERVAL CAST(decimal_date * 86400 AS INT64) SECOND
) AS proper_datetime
FROM `your_project.your_dataset.Airtable`
Multiplying by 86400 converts decimal days straight to seconds - BigQuery handles this way better than splitting day/time calculations. Eliminates the rounding errors I ran into with other methods and matches your expected output perfectly.