locked
Convert DateTime in C# into Date in SQL RRS feed

  • Question

  • User2074777333 posted

    Hello,

    I have DateTime values for the fields that I should insert from my webform into sql table. But the field in SQL table has Date type, not DateTime.

    Can anybody tell me please how to insert parameters which are of type DateTime into the sql table?

    Here is the code I have:

      SqlParameter nameParameter = new SqlParameter
                       ("@Date", SqlDbType.Date, 60);
                    nameParameter.Direction = ParameterDirection.Input;
                    nameParameter.Value = Convert.ToDateTime(row["Date"]);
    

    I don't know how to make Convert.ToDateTime(row["Date"]); like Date only, not DateTime.

    Thanks

    Tuesday, August 19, 2014 4:07 PM

Answers

  • User281315223 posted

    Have you consider just using the Parameters.AddWithValue() method and passing in a DateTime object :

    YourCommand.Parameters.AddWithValue("@Date",DateTime.Now);

    Depending on the format that your row["Date"] object is, you might have to convert it to a string and explicitly parse it in a specific format using the DateTime.ParseExact() method as seen below :

    // Build your parameter
    SqlParameter nameParameter = new SqlParameter("@Date", SqlDbType.Date, 60);
    nameParameter.Direction = ParameterDirection.Input;
    
    // Place a breakpoint here and see what dateFormat looks like
    var dateFormat = Convert.ToString(row["Date"]);
    
    // Using the format that you see, use DateTime.ParseExact() to properly read this value into a DateTime object
    DateTime date = DateTime.ParseExact(dateFormat,"yyyy-MM-dd", null);
    
    // Set the parameter to the value
    nameParameter.Value = date;

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 19, 2014 4:31 PM
  • User753101303 posted

    So in this case the input value is more likely a string. Check what is returned by row["datei].GetType() which will be likely System.String.

    The basic idea still hold true. The issue is that we don't have the same way to write down dates or even numbers depending on the country. But a computer doesn't care and a given "native" value is stored always the same way in memory or inside a db regarless of any format. The point is that when a human want to see this value it is formatted in some way but this is just a default and we could show the *same* value in many many other ways...

    So here you'll have to convert this value to a DateTime using DateTime.TryParse or TryParseExact as show earlier and then you'll transmit this date to SQL Server that will store it as a date without any problem (as the value is under a native *date* format).

    What do you expect in this CSV file for dates? dd/MM/yyyy, yyyy/MM/dd or something else ?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 27, 2014 4:49 PM

All replies

  • User-1360095595 posted

    Not sure but try:

    nameParameter.Value = Convert.ToDateTime(row["Date"]).ToString("d");

    Tuesday, August 19, 2014 4:25 PM
  • User281315223 posted

    Have you consider just using the Parameters.AddWithValue() method and passing in a DateTime object :

    YourCommand.Parameters.AddWithValue("@Date",DateTime.Now);

    Depending on the format that your row["Date"] object is, you might have to convert it to a string and explicitly parse it in a specific format using the DateTime.ParseExact() method as seen below :

    // Build your parameter
    SqlParameter nameParameter = new SqlParameter("@Date", SqlDbType.Date, 60);
    nameParameter.Direction = ParameterDirection.Input;
    
    // Place a breakpoint here and see what dateFormat looks like
    var dateFormat = Convert.ToString(row["Date"]);
    
    // Using the format that you see, use DateTime.ParseExact() to properly read this value into a DateTime object
    DateTime date = DateTime.ParseExact(dateFormat,"yyyy-MM-dd", null);
    
    // Set the parameter to the value
    nameParameter.Value = date;

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 19, 2014 4:31 PM
  • User2074777333 posted

    Thanks, but it seams to me easier to change the type of date and others first in the sql table.

    So I have this in sql now:

    CREATE TABLE [dbo].[TrafInc1] (
        [ID]                  INT             IDENTITY (1, 1) NOT NULL,
        [Datei]               DATETIME        NULL,
        [Dayi]                NVARCHAR (15)   NULL,
        [TimeFrom]            NVARCHAR (5)    NULL,
        [PoD1]                NVARCHAR (2)    NULL,
        [TimeTo]              NVARCHAR (5)    NULL,
        [PoD2]                NVARCHAR (2)    NULL,
        [Location]            NVARCHAR (60)   NULL,
        [IncidentType]        NVARCHAR (50)   NULL,
        [Direction]           NVARCHAR (60)   NULL,
        [LaneBlockage]        NVARCHAR (60)   NULL,
        [CCTV]                NVARCHAR (60)   NULL,
        [SignalTimingChanges] NVARCHAR (100)  NULL,
        [Benefits]            NVARCHAR (100)  NULL,
        [Latitude]            DECIMAL (18, 6) NULL,
        [Longitude]           DECIMAL (18, 6) NULL
    );
    
    

    and in the cs form I have

      SqlParameter nameParameter = new SqlParameter
                       ("@Date", SqlDbType.Date);
                    nameParameter.Direction = ParameterDirection.Input;
                               nameParameter.Value = Convert.ToDateTime(row["Date"]);
                                SqlParameter dayParameter = new SqlParameter
                       ("@Day", SqlDbType.NVarChar, 15);
                    dayParameter.Direction = ParameterDirection.Input;
                    dayParameter.Value = row["Day"];
    
                                  SqlParameter timefromParameter = new SqlParameter
                      ("@TimeFrom", SqlDbType.NVarChar,5);
                    timefromParameter.Direction = ParameterDirection.Input;
                             timefromParameter.Value = row["Time-from"];
    
                    SqlParameter pod1Parameter = new SqlParameter
                      ("@PoD1", SqlDbType.NVarChar, 2);
                    pod1Parameter.Direction = ParameterDirection.Input;
                    pod1Parameter.Value = row["PoD1"];
    
                                   SqlParameter timetoParameter = new SqlParameter
                      ("@TimeTo", SqlDbType.NVarChar, 5);
                    timetoParameter.Direction = ParameterDirection.Input;
                                  timetoParameter.Value = row["Time-to"];
    
    ...
    ..
      insertTable.Parameters.Add(nameParameter);
                    insertTable.Parameters.Add(dayParameter);
                    insertTable.Parameters.Add(timefromParameter);
                    insertTable.Parameters.Add(pod1Parameter);
                    insertTable.Parameters.Add(timetoParameter);

    but now the format for the date is not a valid format for sql. Since in my csv file it is for example 7/2/2014 (mm/dd/yyyy), and in the sql table I suppose yyyymmdd. What can I do now, can anybody help me please?

    Thanks

    Wednesday, August 20, 2014 10:33 AM
  • User-1360095595 posted

    Let's not suppose. What is the exact format in the database? I doubt yyyymmdd is valid for a database datetime column. 

    Wednesday, August 20, 2014 10:52 AM
  • User2074777333 posted

    This is what I get

    12/7/2014 12:00:00 AM

    if I use this in sql:

     exec insertincid1
      "12-07-2014",
      "Tuesday",
      "8:15",
     "AM",
     "8:45",
     "AM",
       "I-95 SB ",
       "hjgjg  ",
       "jhjgb",
       "jhj",
        "hjgjg  ",
       "jhjgb",
       "jhj",
       26.878686,
       -80.765246

    Wednesday, August 20, 2014 11:19 AM
  • User2074777333 posted

    Hi Rion,

    I tried your example but I get this error:


    Exception Details: System.FormatException: String was not recognized as a valid DateTime.

    Source Error: 

    Line 283:
    Line 284:                // Using the format that you see, use DateTime.ParseExact() to properly read this value into a DateTime object
    Line 285:                DateTime date = DateTime.ParseExact(dateFormat, "mm/dd/yyyy", null);
    Line 286:

    Do you have any idea what else to do?
    Thanks
    Thursday, August 21, 2014 9:38 PM
  • User281315223 posted

    It's important to note that the appearance or format of a SQL Date within a table doesn't accurately reflect how it would be presented in .NET. A DateTime object can be formatted in .NET however you would prefer easily using the ToString() method and passing in multiple arguments as seen below :

    DateTime.Now.ToString("yyyy-MM-dd"); // yields "2014-08-22"
    DateTime.Now.ToString("MM/dd/yyyy"); // yields "08/22/2014"

    So if you needed to parse a value that was in the format "yyyy-MM-dd", you could use the following to grab it :

    DateTime yourDateTime = DateTime.ParseExact(yourDateTimeString,"yyyy-MM-dd",null);

    Additionally, I've always found that it is much easier to use the Parameters.AddWithValue() method instead of manually building your parameters and then adding them. For example, you could replace the following :

    // Build your parameters
    SqlParameter nameParameter = new SqlParameter("@Date", SqlDbType.Date);
    nameParameter.Direction = ParameterDirection.Input;
    nameParameter.Value = Convert.ToDateTime(row["Date"]);
    
    SqlParameter dayParameter = new SqlParameter("@Day", SqlDbType.NVarChar, 15);
    dayParameter.Direction = ParameterDirection.Input;
    dayParameter.Value = row["Day"];
    
    SqlParameter timefromParameter = new SqlParameter("@TimeFrom", SqlDbType.NVarChar,5);
    timefromParameter.Direction = ParameterDirection.Input;
    timefromParameter.Value = row["Time-from"];
    
    SqlParameter pod1Parameter = new SqlParameter("@PoD1", SqlDbType.NVarChar, 2);
    pod1Parameter.Direction = ParameterDirection.Input;
    pod1Parameter.Value = row["PoD1"];
    
    SqlParameter timetoParameter = new SqlParameter("@TimeTo", SqlDbType.NVarChar, 5);
    timetoParameter.Direction = ParameterDirection.Input;
    timetoParameter.Value = row["Time-to"];
    
    // Add your parameters
    insertTable.Parameters.Add(nameParameter);
    insertTable.Parameters.Add(dayParameter);
    insertTable.Parameters.Add(timefromParameter);
    insertTable.Parameters.Add(pod1Parameter);
    insertTable.Parameters.Add(timetoParameter);

    with this :

    // Add your parameters with values
    insertTable.Parameters.AddWithValue("@Date",Convert.ToDateTime(row["Date"]));
    insertTable.Parameters.AddWithValue("@Day",row["Day"]);
    insertTable.Parameters.AddWithValue("@TimeFrom",row["Time-from"]);
    insertTable.Parameters.AddWithValue("@PoD1",row["PoD1"]);
    insertTable.Parameters.AddWithValue("@TimeTo",row["Time-to"]);

    Could you post exactly what your row["Date"] object looks like when formatting a string, as this would help resolve any issues with the conversion :

    // What is this equal to
    var x = row["Date"].ToString();

    Friday, August 22, 2014 9:56 AM
  • User2074777333 posted

    Hi Rion,

    First for var x = row["Date"].ToString();

    I get null. Also I changed the type for date to datetime again, since for string type I didn't get correct date.

    Then I tried your code for the date like

      DateTime.Now.ToString("MM/dd/yyyy");
                    DateTime dat = DateTime.ParseExact(Convert.ToString(row["Date"]), "MM/dd/yyyy", null);
                    SqlParameter nameParameter = new SqlParameter
                       ("@Date", SqlDbType.DateTime,10);
                    nameParameter.Direction = ParameterDirection.Input;
                    insertTable.Parameters.AddWithValue("@Date", Convert.ToDateTime(row["Date"]));

    but I get this error:


    Exception Details: System.FormatException: String was not recognized as a valid DateTime.

    Source Error: 

    Line 267:
    Line 268:                DateTime.Now.ToString("MM/dd/yyyy");
    Line 269:                DateTime dat = DateTime.ParseExact(Convert.ToString(row["Date"]), "MM/dd/yyyy", null);


    if I put  insertTable.Parameters.AddWithValue("@Date", dat);
    I get:
    Exception Details: System.FormatException: String was not recognized as a valid DateTime.

    Source Error: 

    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

    Stack Trace: 

    [FormatException: String was not recognized as a valid DateTime.]
     
    Monday, August 25, 2014 3:44 PM
  • User281315223 posted

    If you are receiving null for the following line, you'll want to figure out exactly what you want to insert into the Database :

    var x = row["Date"].ToString();

    If your row["Date"] object is null, then you aren't going to have much luck actually parsing it into a proper DateTime value. I would highly recommend considering the following approach instead :

    // Add your parameters with values
    
    // Check if your row["Date"] value is null
    if(row["Date"] != null || row["Date"] != DBNull.Value)
    {
         // Your Date Parameter is not null, attempt to parse it using the methods suggested earlier
         insertTable.Parameters.AddWithValue("@Date",Convert.ToDateTime(row["Date"]));
    }
    else
    {
         // Add logic (if necessary) to handle if your Date is null
         insertTable.Parameters.AddWithValue("@Date",null);
    }
    insertTable.Parameters.AddWithValue("@Day",row["Day"]);
    insertTable.Parameters.AddWithValue("@TimeFrom",row["Time-from"]);
    insertTable.Parameters.AddWithValue("@PoD1",row["PoD1"]);
    insertTable.Parameters.AddWithValue("@TimeTo",row["Time-to"]);

    Monday, August 25, 2014 3:59 PM
  • User2074777333 posted

    Now I get this error on:

    if (row["Date"] != null || row["Date"] != DBNull.Value)
                    {
                        // Your Date Parameter is not null, attempt to parse it using the methods suggested earlier
                        insertTable.Parameters.AddWithValue("@Date", Convert.ToDateTime(row["Date"]));
                    }
                    else
                    {
                        // Add logic (if necessary) to handle if your Date is null
                        insertTable.Parameters.AddWithValue("@Date", null);
                    }

    Monday, August 25, 2014 4:54 PM
  • User281315223 posted

    Well within that area, you'll need to try and properly parse your Date.

    The easiest way would be to use the DateTime.ParseExact() method and specify your format that you'll be recieving, however you'll likely need to know that your data looks like for that to happen :

    if (row["Date"] != null || row["Date"] != DBNull.Value)
    {
          DateTime yourDate;
    
          // Get your format here (use a breakpoint to see what your row["Date"] value looks like)
          var sample_for_format = Convert.ToString(row["Date"]);
    
          // You should be able to recognize the format your date is in (eg MM/dd/yyyy, yyyy-MM-dd, etc.) here
    
          // Once you figure out your format, simply parse your value using one (or more of those)
          if(DateTime.TryParseExact(sample_for_format,"MM/dd/yyyy", null, DateTimeStyles.None, out yourDate))
          {
                 // If this executes, your date will be stored in your yourDate variable
                 insertTable.Parameters.AddWithValue("@Date", yourDate);
          }
          else
          {
                 // If this executes, it means that your format was incorrect
          }  
    }

    Let me know what the following line looks like when it executes and it might make it much easier :

    var sample_for_format = Convert.ToString(row["Date"]);
    Monday, August 25, 2014 5:41 PM
  • User2074777333 posted

    Hi Rion,

    That line returns null for sample_for_format

    Tuesday, August 26, 2014 8:55 AM
  • User281315223 posted

    That line shouldn't be executing at all, as it should be wrapped within an if-statement that ensures such :

    if (row["Date"] != null || row["Date"] != DBNull.Value)
    {
          DateTime yourDate;
    
          // Get your format here (use a breakpoint to see what your row["Date"] value looks like)
          var sample_for_format = Convert.ToString(row["Date"]);
    
          // Other code here
    }

    Tuesday, August 26, 2014 9:35 AM
  • User2074777333 posted

     Hi Rion,

    First thank, that error is eliminated but there is a new problem.

    Now I since I have another loop further in the code where I use the Date for this line:

    nameParameter.Value =Convert.ToDateTime(dt.Rows[r]["Date"]);

    I get this error

    An exception of type 'System.FormatException' occurred in mscorlib.dll but was not handled in user code

    Additional information: String was not recognized as a valid DateTime.

    And it's strange that now besides the error, the table is filled with data and when I run it again the data is shown on the web site's table. But how to eliminate the error anyway? Can you help me please?

    Thanks

    P.S. Here is the second loop

      int r = 1;
               
                    while (r < (dt.Rows.Count))
                    {
                                           
                        nameParameter.Value =Convert.ToDateTime(dt.Rows[r]["Date"]);  
    dayParameter.Value = dt.Rows[r]["Day"]; timefromParameter.Value = dt.Rows[r]["Time-from"]; pod1Parameter.Value = dt.Rows[r]["PoD1"]; timetoParameter.Value = dt.Rows[r]["Time-to"]; pod2Parameter.Value = dt.Rows[r]["PoD2"]; nameParameter2.Value = dt.Rows[r]["Location"]; nameParameter3.Value = dt.Rows[r]["IncidentType"]; nameParameter4.Value = dt.Rows[r]["Direction"]; nameParameter5.Value = dt.Rows[r]["LaneBlockage"]; nameParameter6.Value = dt.Rows[r]["CCTV"]; nameParameter7.Value = dt.Rows[r]["SignalTimingChanges"]; nameParameter8.Value = dt.Rows[r]["Benefits"]; decimal.TryParse(dt.Rows[r]["Latitude"].ToString(), out latitude); nameParameter9.Value = latitude; decimal.TryParse(dt.Rows[r]["Longitude"].ToString(), out longitude); nameParameter10.Value = longitude; insertTable.ExecuteNonQuery(); r++; }

    Tuesday, August 26, 2014 1:39 PM
  • User1918509225 posted

    Hi nat06,

    please try the code below:

    DateTime date = DateTime.ParseExact(dt.Rows[r]["Date"],"yyyy-MM-dd", null);
    
    // Set the parameter to the value
    nameParameter.Value = date;

    Best Regards,

    Kevin Shen.

    Wednesday, August 27, 2014 3:01 AM
  • User2074777333 posted

    Hi kevin,

    First thatnks, but I get error again.

    I tried this, but first since dt.Rows[r]["Date"] was not converted into string I got error, and then on this

     DateTime date = DateTime.ParseExact(Convert.ToString(dt.Rows[r]["Date"]), "MM/dd/yyyy", null);

                                            nameParameter.Value = date;

    I got:

    An exception of type 'System.FormatException' occurred in mscorlib.dll but was not handled in user code

    Additional information: String was not recognized as a valid DateTime.

    But what is interesting is that if I leave it just like as it was:

     nameParameter.Value =Convert.ToDateTime(dt.Rows[r]["Date"]);

    I got the error only whan I run the first time the application, and even though I get the error, the table is filled. If I run it again after that the table in grid view appears filled with data. But if I use the change you've posted neither the first time when I run it, neither after that the table is filled.

    Wednesday, August 27, 2014 8:35 AM
  • User-760709272 posted

    I'm not going to go through the whole thread, but some general observations;

    Store the data in SQL as you need it.  If it has to be a Date, use a Date, don't use DateTime.  If you can't work out how to store a date (I don't see what the issue is, but I'll maybe cover it later) then fix that problem.  If you store the time, any queries that involve things like "get me the data for this day, or between these two days" gets more complicated.  Also you'll still have a time when your code gets to c# as .net doesn't have a Date type so you'll be having to use the right ToString converters on your pages anyway.

    My biggest observation about your code is similar to all the other people that have similar issues.  Stop treating everything as strings, stop converting everything to strings and then to other types, the .net framework is cleverer than you are :)

    If you want to store a param to a Date field you can use the Date property of a DateTime to "00:00:00" the time element;

    .AddWithValue("@Datei", DateTime.Now.Date);

    when you get items from Row[x] they have the correct type, stop converting everything to strings, it is the root of your problems.  Row["Datei"] is already of type DateTime

    DateTime dt = (DateTime)row["Datei"]

    same for your long and lat values, they are already of appropriate c# types.  One of the things ado.net does is map SQL types to the most appropriate .net type.  When it comes to dates, this is done by using unambiguous formats so that you don't get conversion errors, and when you tostring everything all of that exact data goes out and the window and you have all sorts of formatting and conversion problems.

    So convert all your code to be strongly typed, get rid of all of your ToStrings and go from there.

    Wednesday, August 27, 2014 9:14 AM
  • User2074777333 posted

    Thanks Aidy for your try to help. I read your recommendations and  now on

    DateTime dt = (DateTime)row["Datei"];

    I get

    System.InvalidCastException: Specified cast is not valid. at WebApplicationVDF_incidents_project.Incidents.btnImport_Click(Object sender, EventArgs e)

    Can you help me please what's wrong with the cast?

    Thanks

    P.S. I returned the type of the Datei field to be Date

    Wednesday, August 27, 2014 2:46 PM
  • User-760709272 posted

    What sql are you executing?

    Wednesday, August 27, 2014 2:53 PM
  • User2074777333 posted

    Here is the sql code

    CREATE procedure [insertIncid1]
    (
       @Date                DATE   ,
        @Day				 nvarchar (15),
    	@TimeFrom             NVARCHAR (5),
    	@PoD1				 nvarchar(2),
    	@TimeTo				  NVARCHAR (5),
    	@PoD2				  NVARCHAR (2),
        @Location            NVARCHAR (60)  ,
        @IncidentType        NVARCHAR (50)   ,
        @Direction           NVARCHAR (60)  ,
        @LaneBlockage        NVARCHAR (60)  ,
        @CCTV                NVARCHAR (60)   ,
        @SignalTimingChanges NVARCHAR (100) ,
        @Benefits            NVARCHAR (100)  ,
        @Latitude            DECIMAL (18, 6) ,
        @Longitude           DECIMAL (18, 6) 
    
    )
    as
    begin
    
    INSERT INTO [TrafInc1] 
    (
    Datei,
        Dayi,
    	TimeFrom,
    	PoD1,
    	TimeTo,
    	PoD2,
        Location,
       IncidentType,
        Direction,
        LaneBlockage,
        CCTV,
        SignalTimingChanges,
        Benefits,
        Latitude,
       Longitude
         )
    
    VALUES 
    (
        @Date,
        @Day,
    	@TimeFrom,
    	@PoD1,
    	@TimeTo,
    	@PoD2,
        @Location,
        @IncidentType,
        @Direction,
        @LaneBlockage,
        @CCTV,
        @SignalTimingChanges,
        @Benefits,
        @Latitude,
        @Longitude
    	)
    
       end

    Wednesday, August 27, 2014 3:41 PM
  • User-760709272 posted

    So where does

    DateTime dt = (DateTime)row["Datei"];

    come into the equation?  What is "row" and where is it coming from?  That SQL won't return any data, it doesn't SELECT anything.

    Wednesday, August 27, 2014 4:17 PM
  • User2074777333 posted

    that is from data table which contains data from csv file. Then this data are sent to sql

    Wednesday, August 27, 2014 4:28 PM
  • User753101303 posted

    So in this case the input value is more likely a string. Check what is returned by row["datei].GetType() which will be likely System.String.

    The basic idea still hold true. The issue is that we don't have the same way to write down dates or even numbers depending on the country. But a computer doesn't care and a given "native" value is stored always the same way in memory or inside a db regarless of any format. The point is that when a human want to see this value it is formatted in some way but this is just a default and we could show the *same* value in many many other ways...

    So here you'll have to convert this value to a DateTime using DateTime.TryParse or TryParseExact as show earlier and then you'll transmit this date to SQL Server that will store it as a date without any problem (as the value is under a native *date* format).

    What do you expect in this CSV file for dates? dd/MM/yyyy, yyyy/MM/dd or something else ?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 27, 2014 4:49 PM