Prevent Blank Excel Dates from Becoming 1900-01-01 in SQL Server
Question details
The user needs blank Excel date cells to be uploaded as NULL values in SQL Server.

- Product
- Microsoft Excel VBA, ADO, and SQL Server
- Device & OS
- not provided
- Scenario
- A VBA macro uploads Excel rows to a SQL Server table whose date column allows NULL.
- Observed behavior
- Blank date cells are stored as 1900-01-01 rather than SQL NULL.
Identify the VBA code that reads the date cell and assigns the ADO parameter, and confirm that the destination column allows NULL. Check whether 1900-01-01 can be a legitimate date before changing existing records.
Pass NULL for blank Excel dates in the VBA ADO upload
Use this approach to prevent future uploads from converting blank dates into a zero date.
In Excel, press Alt+F11 and open the module containing the ADO upload. Find the code that reads the date cell and assigns its value to the insert command.
Test the cell value before converting it to a date. For example, with cell referring to the source cell and dateParam referring to the ADO date parameter, use: If IsError(cell.Value) Then Err.Raise vbObjectError + 1, , "Invalid date cell" ElseIf Len(Trim$(CStr(cell.Value))) = 0 Then dateParam.Value = Null ElseIf IsDate(cell.Value) Then dateParam.Value = CDate(cell.Value) Else Err.Raise vbObjectError + 2, , "Invalid date value" End If
Run the macro with one blank date cell and one valid date cell. In SQL Server, query the inserted rows and confirm that the blank cell produced NULL and the valid date was preserved.

Correct unintended 1900-01-01 dates already in SQL Server
Use an UPDATE after the upload only when the affected values can be identified as unintended.
Work with Excel Spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility, making it easy to move everyday workbook work across. It does not fix the Excel VBA or SQL Server upload; check macro compatibility before migrating an automated workbook.

Frequently Asked Questions
Why does a blank Excel date become 1900-01-01 in SQL Server?
The upload may convert the blank cell into a zero or empty date value instead of passing a database NULL. Check the VBA code that reads the cell and assigns the ADO parameter.
How do I send a blank date as SQL NULL through ADO?
Before converting the Excel cell to a date, test whether it is blank. If it is, assign the VBA value Null to the ADO date parameter; the SQL Server column must allow NULL.
Can I change existing 1900-01-01 values to NULL?
Yes, if you can identify them as unintended and the column allows NULL. First review matching rows with SELECT, then use UPDATE with a filter that excludes any legitimate 1900-01-01 dates.
Will fixing existing SQL Server rows prevent the problem on the next upload?
No. An UPDATE corrects existing rows only. Modify the VBA upload to pass Null for blank date cells to prevent new occurrences.




