I have been asked to write a series of tests within SQL.
Many of them are to test if a certain DateTime columns held in the system is a valid date.
Given that the all the fields in question are Datetime column and any changes made to them by end-users are through a GUI front end that already has date validation tests, not to mention that the SQL connector would through up an error if an invalid was passed through anyway.
My question is that under what circumstances could a DateTime column end up holding an invalid date, i.e 20/40/2018.
We are running SQL Server 2008
Kind regards
Matt
Best Answer
SQL Server will not allow an invalid date. Internally, the date part of a DateTime column is just a number/four bytes that represents dates between January 1, 1753, through December 31, 9999. SQL Server does not know anything about and will not use invalid dates. An attempt to save an invalid date will result in an error.
If you want to confirm this, write some code that attempts to store all possible number combinations for the digit groups in a column.
It is possible, however, to inadvertently store the wrong date, due to SQL Server DateTime supports multiple formats, however that should be handled by your business logic layer. For example, someone may enter 07/06/2018, intending the date to be June 7. In most cases this can be avoided by using ISO 8601 DateTime format when a DateTime needs to be represented as a string.
https://en.wikipedia.org/wiki/ISO_8601