Validate DateTime before inserting it into SQL Server database

Question

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,

Answer 1

Try this without hardcoding sql dateTime value:

public bool IsValidSqlDateTime(DateTime? dateTime)
{
    if (dateTime == null) return true;
    
    DateTime minValue = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue;
    DateTime maxValue = (DateTime)System.Data.SqlTypes.SqlDateTime.MaxValue;

    if (minValue > dateTime.Value || maxValue < dateTime.Value)
        return false;

    return true;
}

Answer 2

Here is a class with an extension method to allow a check such as if(myDateTime.IsValidSqlDateTime()) { ... }:

public static class DateTimeExtensionMethods
{
    public static bool IsValidSqlDateTime(this DateTime dateTime)
    {
        return !(dateTime < (DateTime) SqlDateTime.MinValue ||
                 dateTime > (DateTime) SqlDateTime.MaxValue);
    }
}

Answer 3

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;
    }

Answer 4

<asp:RangeValidator runat="server" ID="rgvalDate" ControlToValidate="txtDate" Text="[Invalid]" Type="Date" MinimumValue="1/1/1753" MaximumValue="12/31/9999" />

OR

custom validator:

    protected void cvalDOB_ServerValidate(object sender, ServerValidateEventArgs e)
    {
        e.IsValid = IsValidSqlDateTime(e.Value);
    }

    public static bool IsValidSqlDateTime(object Date)
    {
        try
        {
            System.Data.SqlTypes.SqlDateTime.Parse(Date.ToString());
            return true;
        }
        catch
        {
            return false;
        }
    }

Answer 5

This is another take on billinkc's answer. However, in this method the .Value property of the min/max is used to avoid parsing and try/catch. Someone mentioned they wanted to ensure they are inserting a valid date into SQL Server. So, I took the approach of returning a date that is valid for SQL Server. This could easily be changed to a boolean method that checks to see if the dateToVerify is a valid SQL Server date.

protected DateTime EnsureValidDatabaseDate(DateTime dateToVerify)
{
    if (dateToVerify < System.Data.SqlTypes.SqlDateTime.MinValue.**Value**)
    {
        return System.Data.SqlTypes.SqlDateTime.MinValue.Value;
    }
    else if (dateToVerify > System.Data.SqlTypes.SqlDateTime.MaxValue.**Value**)
    {
        return System.Data.SqlTypes.SqlDateTime.MaxValue.Value;
    }
    else
    {
        return dateToVerify;
    }
}

Answer 6

Could you provide a bt more information on where the datetime value is coming from; a web form? You could simply add a CompareValidator as follows

<asp:CompareValidator ID="CompareValidator1" runat="server" 
            ControlToValidate="txtDate" 
            Type="Date" 
            ErrorMessage="CompareValidator">
</asp:CompareValidator>

Answer 7

DateTime.TryParse is the best validator

DateTime temp;
if(DateTime.TryParse(txtDate.Text, out temp))
//Works
else
// Doesnt work

Answer 8

If you are mentioning about server side validation of your DateTime field, use DateTime.TryParse. A quick and dirty example will be

DateTime dateValue;
string dateString = "05/01/2009 14:57:32.8";
if (DateTime.TryParse(dateString, out dateValue))
{
    // valid date comes here.
    // use dateValue for this
}
else
{
    // valid date comes here
}