Inserting datetime as string C# SQL-server

Question

I have a SQL-server and a column called CitizenshipDate. That column is of type datetime in the SQL.

However, the problem is that it can have the value '0' which is NOT a datetime value but rather a string value.

So what I'm trying to do is to handle it as a string in C# when inserting it values but get the error:

Conversion failed when converting datetime from character string.

I get that error because I'm trying to insert string into a datetime in the SQL-server.

Here is my code:

 class Person {
       public string PersonalIdentityNumber { get; set; }
       public string SpecialIdentityNumber { get; set; }
       public string FirstName { get; set; }
       public string LastName { get; set; }
       public string CitizenshipDate { get; set; }

}



  List<FolkbokforingspostTYPE> deserializedList = new List<FolkbokforingspostTYPE>();
            deserializedList = Deserialize<List<FolkbokforingspostTYPE>>();


            var myPersons = Deserialize<List<FolkbokforingspostTYPE>>()
                .Select(x => new Person
                {
                    PersonalIdentityNumber = x.Personpost.PersonId.PersonNr,
                    SpecialIdentityNumber = x.Personpost.PersonId.Tilltalsnamnsmarkering != null ? x.Personpost.PersonId.Tilltalsnamnsmarkering : null, 
                    LastName = x.Personpost.Namn.Efternamn,
                    FirstName = x.Personpost.Namn.Fornamn,
                    CitizenshipDate = x.Personpost.Medborgarskap != null ? x.Personpost.Medborgarskap.Medborgarskapsdatum : null             

                });



            string connetionString = null;


            SqlDataAdapter adpter = new SqlDataAdapter();
            DataSet ds = new DataSet();
            XmlReader xmlFile;



            connetionString = "Data Source=tsrv2062;Initial Catalog=Bums;User ID=BumsUser;Password=2tusen7Bums";


            xmlFile = XmlReader.Create("navetout.xml", new XmlReaderSettings());
            ds.ReadXml(xmlFile);


            using (var connection = new SqlConnection(connetionString))
            {
                connection.Open();
                DateTime datum = DateTime.Now;
                string LastChangedBy = "System";

                foreach (Person p in myPersons)
                {

                    SqlCommand command1 = Avreg(p.UnregistrationReason, p.GivenNameNumber,p.ProtectedIdentity, p.CitizenshipDate, connection);


                    command1.Parameters.AddWithValue("@PersonalIdentityNumber", string.Format("{0}{1}", p.PersonalIdentityNumber, p.SpecialIdentityNumber));
                    command1.Parameters.AddWithValue("@FirstName", p.FirstName);
                    command1.Parameters.AddWithValue("@LastName", p.LastName);
                    command1.ExecuteNonQuery();

                    Console.WriteLine(string.Format("{0}{1}", p.PersonalIdentityNumber, p.SpecialIdentityNumber));


                }
            }

            Console.WriteLine("Done");


           // }// Put a break-point here, then mouse-over PersonalIdentityNumber...  deserializedList contains everything if you need it
           //catch (Exception)
           // {

           //     throw;
           // }
            Console.ReadKey();
        }

        public static SqlCommand Avreg(string s, string t, string p, string c, SqlConnection connection)
        {
            var query = "UPDATE Seamen SET FirstName = @FirstName, "+
                "LastName = @LastName, "+

            SqlCommand command1;


//Here is the `CitizenshipDate` 
            if (c == "0")
            {
                query += ", CitizenshipDate = '0'";
                command1 = new SqlCommand(query, connection);
                command1.Parameters.Clear();



            }
            else
            {
                query += ", CitizenshipDate = @CitizenshipDate";
                command1 = new SqlCommand(query, connection);
                command1.Parameters.Clear();
                command1.Parameters.AddWithValue("@CitizenshipDate", c ?? DBNull.Value.ToString());

            }


//Ignore these if statements
             if ((!string.IsNullOrEmpty(s)) && !string.IsNullOrEmpty(t))
            {

            }
            else
            {

                    query += ", GivenNameNumber = @GivenNameNumber WHERE PersonalIdentityNumber = @PersonalIdentityNumber";
                    t = "00";
                    command1 = new SqlCommand(query, connection);
                    command1.Parameters.Clear();
                    command1.Parameters.AddWithValue("@GivenNameNumber", t ?? DBNull.Value.ToString());

                    return command1;

            }
             return command1;
        }

Please note that I cannot change the type in the database. I somehow need a way to insert the value from String. Can someone help ?

in this case you have to decide what date you need to enter when it's value is '0' — user5308950
– user5308950, Commented May 9, 2016 at 12:13
0 is not a proper value for datetime. Try CitizenshipDate = NULL — Wudge
– Wudge, Commented May 9, 2016 at 12:26

Brian Mains · Accepted Answer · 2016-05-09 12:57:05Z

1

If the column does not allow nulls, then you can use NULL to represent "0", and then in code, when loading the record, replace NULL with "0" in the UI. Or, if you do allow NULLS and need another value to represent "0", you can use an arbitrary date you wouldn't normally use like '1/1/1753' (the lowest value for SQL datetime) or '1/1/1900' (lowest for smalldatetime) or something like that. Any date that is these dates represents "0". So the conversion to "0" happens within the app, not stored in the database.

answered May 9, 2016 at 12:57

Brian Mains

50.8k35 gold badges155 silver badges260 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Carra · Accepted Answer · 2016-05-09 13:40:14Z

1

If your column supports null you can fix it like this:

        if (c == "0")
        {
            query += ", CitizenshipDate = NULL--instead of '0'";

If it does not support NULL values you will have to insert a default value such as DateTime.MinValue.

answered May 9, 2016 at 13:40

Carra

18k8 gold badges66 silver badges75 bronze badges

Collectives™ on Stack Overflow

Inserting datetime as string C# SQL-server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related