Unable to insert default DateTime of C# to SQL Server 2008

Question

I'm working on a C# console application project. Trying to insert a DateTime value into SQL Server 2008 and this is working good. In case if my code fails I want to insert the default DateTime to the table in database.

When I tried to insert I got an error.

SqlTypeException was unhandled:
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

Below I have pasted the specific code.

try
{
  ...
  return DateTime.Now;
}

catch (WebException ex)
{
  ...
  return default(DateTime);
}

Code I used to insert details in the LOGS table:

connection.Open();
for (int i = 0; i < LogList.Count; i++)
    {
        string aprocessLogQuery = "INSERT INTO PROCESS_LOGS VALUES (@fileId, @startTime, @endTime, @transferredTime)";
        command = new SqlCommand(aprocessLogQuery, connection);
        command.Parameters.Add("fileId", SqlDbType.Int).Value = LogList[i].fileId;
        command.Parameters.Add("startTime", SqlDbType.DateTime).Value = LogList[i].fileGeneration_StartDateTime;
        command.Parameters.Add("endTime", SqlDbType.DateTime).Value = LogList[i].fileGeneration_EndDateTime;
        command.Parameters.Add("transferredTime", SqlDbType.DateTime).Value = LogList[i].fileTransferred_DateTime;
        dataReader.Close();
        dataReader = command.ExecuteReader();
    }
connection.Close();

The default value of DateTime is 01/01/0001 12:00:00 AM, therefore it is not working. Can anyone please suggest alternate working solution?

Thanks

you should use the [datetime2 datatype](http://stackoverflow.com/questions/1334143/sql-server-datetime2-vs-datetime) — V4Vendetta, Jun 06 '13 at 07:25
It would be a good idead to use SqlParameter instead of strings. Whenever I see a string query, I see the risk of SQL injection... — Kamil T, Jun 06 '13 at 07:27
Take a look at [this question](http://stackoverflow.com/questions/16692647/why-when-i-insert-a-datetime-null-i-have-0001-01-01-in-sql-server/16693888#16693888): it says that inserting `null` datetime give the default value in database. — Spaceman, Jun 06 '13 at 07:29
@V4Vendetta Thanks for introducing datetime2. But sorry I don't have access to change the dataType in SQL Server. — Praveen, Jun 06 '13 at 07:30
`DATETIME` in SQL Server doesn't support dates before the year 1753 - so if you cannot change your SQL Server datatype to `DATETIME2` (which would support `01/01/0001`), then you need to change your .NET code to return `01/01/1900` or something like that as a default — marc_s, Jun 06 '13 at 07:33
@user1671639 i guess your field is non nullable, so if you can't change the datatype you will have to pass in the min sql datetime value `1\1\1753` — V4Vendetta, Jun 06 '13 at 07:34

Toan Vo · Answer 1 · 2013-06-06T15:15:58.070

4

Another alternative solution is you can work around by build extension method look like as below:

public static class DateTimeExtension
{
    public static DateTime DefaultSqlDateTime(this DateTime dateTime)
    {
       return new DateTime(1753, 1, 1, 12, 0, 0);
    }
}

and instead of return default(DateTime) you can use variable dateTime return dateTime.DefaultSqlDateTime;

EDIT: How to use extension method:

E.g: return default(DateTime).GetDefaultSqlDateTime();

or

DateTime dateTime = ...
return dateTime.GetDefaultSqlDateTime();

edited Jun 06 '13 at 15:15

answered Jun 06 '13 at 07:34

Toan Vo

1,270
9
19

Thanks. But `return DateTime.DefaultSqlDateTime` is not working. Can you please explain? – Praveen Jun 06 '13 at 12:38
@user1671639 Ah, I see you wonder about how to use DefaultSqlDateTime extension method. Please see edit part below. – Toan Vo Jun 06 '13 at 14:55
Still I can't make through it. Following the error `Extension method must be defined in a non-generic static class` – Praveen Jun 06 '13 at 15:00
Yes. You can look at the link : http://www.codeproject.com/Articles/19963/AddBusinessDay-DateTime-Extension-Method to get more understand about extension method on DateTime type. I have applied many extensions method on DateTime when deal with SQL Server that will help your code readable and more elegant. – Toan Vo Jun 06 '13 at 15:05

score 3 · Answer 2 · answered Jun 06 '13 at 07:32

3

In the database, an unknown date is represented with NULL value. You can insert that like:

command.Parameters.AddWithValue("@startTime", DBNull.Value);

(As far as I know, it's required to prefix your parameters with @.)

answered Jun 06 '13 at 07:32

Andomar

232,371
49
380
404

2

The `@` prefix is optional there. – Martin Smith Jun 06 '13 at 07:52

score 0 · Answer 3 · answered Sep 02 '14 at 10:32

I am using this function in my Utilities instead of regular TryParse

public static bool TryParseSqlDateTime(string someval, DateTimeFormatInfo dateTimeFormats, out DateTime tryDate)
    {
        bool valid = false;
        tryDate = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue;
        System.Data.SqlTypes.SqlDateTime sdt;
        if (DateTime.TryParse(someval, dateTimeFormats, DateTimeStyles.None, out tryDate))
        {
            try
            {
                sdt = new System.Data.SqlTypes.SqlDateTime(tryDate);
                valid = true;
            }
            catch (System.Data.SqlTypes.SqlTypeException ex)
            {


            }
        }

        ret

score 0 · Answer 4 · answered Mar 24 '21 at 10:01

0

You can use this, this will return minimum date value that SQL will accept. This method return DateTime in .Net.

System.Data.SqlTypes.SqlDateTime.MinValue.Value

answered Mar 24 '21 at 10:01

Ariwibawa

627
11
23

Unable to insert default DateTime of C# to SQL Server 2008

4 Answers4