Solved

MySQL zero date (0000-00-00) in .net C# 4.0 and Entity Framework

Posted on 2011-02-17
5
1,186 Views
Last Modified: 2012-05-11
Hi,

I wrote a a .net MVC application which is connected to a MySQL 5.09 database via EF4. I am writing some records where I would need to insert the special MySQL date of 0000-00-00 as many queries from other apps connected need this. C# does not accept it as a valid date and I can only pass DateTime.MinValue which is 0001 etc. How do I get 0000-00-00? I could make it nullable but they insist to have that special MySQL format?
0
Comment
Question by:nikomanek
  • 2
  • 2
5 Comments
 
LVL 38

Expert Comment

by:Aaron Tomosky
ID: 34922776
You should be able to format it as a string and mysql will take it.
0
 

Author Comment

by:nikomanek
ID: 34922835
That probably would work but then I can't really use this anymore in my application unless I cast it everywhere? Right now I have this in my connection string (Convert Zero Datetime = True) to being able to read these dates. I just can't write them. My entity classes assume these are date values. It would be great if I could keep it that way. Alternatively I thought that I just write a trigger like:

create trigger on xyz before insert for each row beginn set new.datecolumn='0000-00-00' end
but this MySQL database complains about wrong syntax no matter how I write it. Maybe not supportet in that version.
Unfortunately this problem is a true show stopper for my app as it is use less unless it can write zero date.  
0
 
LVL 38

Expert Comment

by:Aaron Tomosky
ID: 34922863
I personally use 1900/1/1 for fake dates. Everythif is happy with this...
What is special about 0000-00-00 in mysql?
0
 

Author Comment

by:nikomanek
ID: 34922922
There are a couple apps which where written by previous third parties which hard coded that particular zero date into their queries. We would have to change many applications if I can't write that date. The 0000-00-00 is a special MySQL zero date. Thats why they picked it (for reasons beyond me)
0
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 34940635
Why don't you create your own type which wraps a DateTime and also exposes the concept of "zero-date" (similar to a Nullable<> class). Here is an example of what I am referring to:


Data Structure
public class MyCustomDate
{
    private long _ticks;

#region Constructors

    public MyCustomDate()
    {
        this._ticks = 0;
    }

    public MyCustomDate(DateTime d)
    {
        this._ticks = d.Ticks;
    }

    public MyCustomDate(long ticks)
    {
        this._ticks = ticks;
    }

#endregion

#region Properties

    public bool IsSpecialDate
    {
        get { return (this._ticks == 0); }
    }

#endregion

#region Public Methods

    public override string ToString()
    {
        if (this._ticks == 0)
        {
            return "0000-00-00";
        }
        else
        {
            return (new DateTime(this._ticks)).ToString("yyyy-mm-dd");
        }
    }

#endregion

#region Operators

    public static implicit operator MyCustomDate(DateTime d)
    {
        return new MyCustomDate(d.Ticks);
    }

#endregion
}

Open in new window



Usage
MyCustomDate d = new MyCustomDate();

Console.WriteLine("Has value? {0}", (!d.IsSpecialDate).ToString());
Console.WriteLine(d);

d = new MyCustomDate(DateTime.Now.Ticks);

Console.WriteLine("Has value? {0}", (!d.IsSpecialDate).ToString());
Console.WriteLine(d);
Console.ReadKey();

d = DateTime.Now;

Console.WriteLine("Has value? {0}", (!d.IsSpecialDate).ToString());
Console.WriteLine(d);

Console.ReadKey();

Open in new window

0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question