Is there any way to validate datetime
field before inserting it into appropriate table?
Trying to insert with try/catch block is not a way.
Thanks,
Not sure if I'm being overly pedantic there, but DateTime.TryParse will validate whether a value is a valid DateTime object. OP asked about verifying a value before inserting into SQL Server datetime. The range of acceptable values for a SQL Server datetime is "January 1, 1753, through December 31, 9999" That does not hold true for DateTime .NET objects. This script assigns a value of "1/1/0001 12:00:00 AM" to badDateTime and it successfully parses.
DateTime d = DateTime.MinValue;
string badDateTime = DateTime.MinValue.ToString();
Console.WriteLine(badDateTime);
DateTime.TryParse(badDateTime, out d);
However, if you attempted to store that into a datetime field, it would fail with "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."
A commenter asked why I used 997 for milliseconds, this is covered under SQL Server 2008 and milliseconds but saving you a click, 997 is the largest value you can store in a datetime datatype. 998 will be rounded up to 1 second with 000 milliseconds
/// <summary>
/// An initial pass at a method to verify whether a value is
/// kosher for SQL Server datetime
/// </summary>
/// <param name="someval">A date string that may parse</param>
/// <returns>true if the parameter is valid for SQL Sever datetime</returns>
static bool IsValidSqlDatetime(string someval)
{
bool valid = false;
DateTime testDate = DateTime.MinValue;
DateTime minDateTime = DateTime.MaxValue;
DateTime maxDateTime = DateTime.MinValue;
minDateTime = new DateTime(1753, 1, 1);
maxDateTime = new DateTime(9999, 12, 31, 23, 59, 59, 997);
if (DateTime.TryParse(someval, out testDate))
{
if (testDate >= minDateTime && testDate <= maxDateTime)
{
valid = true;
}
}
return valid;
}
This is probably a better approach as this will attempt to cast the DateTime object into an actual sql datetime data type
/// <summary>
/// An better method to verify whether a value is
/// kosher for SQL Server datetime. This uses the native library
/// for checking range values
/// </summary>
/// <param name="someval">A date string that may parse</param>
/// <returns>true if the parameter is valid for SQL Sever datetime</returns>
static bool IsValidSqlDateTimeNative(string someval)
{
bool valid = false;
DateTime testDate = DateTime.MinValue;
System.Data.SqlTypes.SqlDateTime sdt;
if (DateTime.TryParse(someval, out testDate))
{
try
{
// take advantage of the native conversion
sdt = new System.Data.SqlTypes.SqlDateTime(testDate);
valid = true;
}
catch (System.Data.SqlTypes.SqlTypeException ex)
{
// no need to do anything, this is the expected out of range error
}
}
return valid;
}