The "44562" Date Problem Explained
You use a tool like our Develop Box Online Converters to Convert Excel to SQL INSERT statements online and see INSERT INTO events (date) VALUES ('44562');. What happened?
Excel does not store dates as "2022-01-01". It stores them as a serial number representing the number of days since December 30, 1899 (often called the "1900 date system"). The number 44562 is literally 44,562 days after the epoch.
SQL databases (MySQL, PostgreSQL) expect ISO 8601 strings ('YYYY-MM-DD'). If you insert the raw number, your database will either throw an error or interpret it as a completely different date.
The O'Connor Problem (Special Characters)
Names with apostrophes are the nemesis of SQL. VALUES ('O'Connor') breaks the SQL syntax because the database thinks the string ends after the 'O'.
You must escape special characters. However, the rules for escaping differ by database engine.
SQL Dialect Escaping Rules
| Database | Single Quote Handling | Double Quote Handling |
|---|---|---|
| Standard SQL (ANSI) | Double it: 'O''Connor' |
Used for identifiers: "TableName" |
| MySQL / MariaDB | Backslash: 'O\'Connor' OR Double it |
Often treated as string literals depending on mode |
| PostgreSQL | Double it: 'O''Connor' |
Strictly for identifiers |
| SQL Server (T-SQL) | Double it: 'O''Connor' |
Uses Brackets [TableName] usually |
Handling Emojis and Encoding
Another common issue is encoding. If your Excel file contains Emojis (π) or non-Latin characters (δΈζ), and your database is not set to utf8mb4 (in MySQL), your data will be truncated or turned into ????.
The Checklist for Developer Toolbox Online Users
- Database Charset: Ensure your table is created with
utf8mb4(MySQL) orUTF8(Postgres). - Connection Charset: Your client must strictly specify UTF-8 encoding.
- Formatting: Use a tool to Format MySQL and T-SQL queries online to verify that special characters are preserved in the SQL output.
- CSV Import: If saving as CSV from Excel, verify it is "CSV UTF-8 (Comma delimited)". Standard CSV in older Excel versions uses ANSI encoding, which corrupts special characters.
Frequently Asked Questions
Why do my Excel dates turn into numbers like "44562"?
Excel stores dates as serial numbers (days since Dec 30, 1899). You need to convert this number to a standard date format (YYYY-MM-DD) for SQL.
How do I handle single quotes in names like "O'Connor"?
You must escape single quotes by doubling them ('O''Connor') in Standard SQL, Postgres, and SQL Server. MySQL also supports backslash escaping ('O\'Connor').
Why are my emojis turning into "????" in the database?
This is an encoding issue. Ensure your database table uses utf8mb4 (MySQL) or UTF8 (Postgres) and your connection is also set to UTF-8.