Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1547
  • Last Modified:

DateTime format problem when passing in as Sql Parameter.

OK, I have a stored procedure that accepts parameters and one of which is of SqlTYpe DateTime (default and unchangable size of 8). Now, I am passing in the value, via a function that I created and I have to do it like this as it is a requirement.
THe problem is that when i pass in the datetime as a string, when it converts it to string and executes the query, i get the following error:

String was not recognized as a valid DateTime.

I debugged the code and the value for the string is exactly what it shud be, i.e. 14/9/2004
For your convenience, here is the function that i use...!!

public void UpdateHasTaken(int moduleID, string PackDescription, string StudentNo, string ReservedOn)
            {
                  // Create Instance of Connection and Command Object
                  SqlConnection myConnection = PortalSettings.SqlConnectionString;
                  SqlCommand myCommand = new
                        SqlCommand("RMIT_DesignStore_UpdateHasTaken", myConnection);

                  // Mark the Command as a SPROC
                  myCommand.CommandType = CommandType.StoredProcedure;
                  // Add Parameters to SPROC
                  SqlParameter parameterModuleID = new SqlParameter("@ModuleID",
                                                                                                                 SqlDbType.Int, 4);
                  parameterModuleID.Value = moduleID;
                  myCommand.Parameters.Add(parameterModuleID);

                  SqlParameter parameterPackDescription = new SqlParameter("@PackDescription",
                                                                                               SqlDbType.NVarChar, 50);
                  parameterPackDescription.Value = PackDescription;
                  myCommand.Parameters.Add(parameterPackDescription);

                  SqlParameter parameterStudentNo = new SqlParameter("@StudentNo",
                                                                                                  SqlDbType.NVarChar, 8);
                  parameterStudentNo.Value = StudentNo;
                  myCommand.Parameters.Add(parameterStudentNo);

                  SqlParameter parameterReservedOn = new SqlParameter("@ReservedOn",                    
                                                                                                  SqlDbType.DateTime, 8);
                  parameterReservedOn.Value = ReservedOn;
                  myCommand.Parameters.Add(parameterReservedOn);

                  myConnection.Open();
                  myCommand.ExecuteNonQuery();
                  myConnection.Close();
            }

Is there a formatting problem ? The date passed in as a string is of format dd/mm/yyyy.
If you could help me with this, that would be great.

Thanks
0
saumathur
Asked:
saumathur
  • 21
  • 17
  • 3
  • +4
1 Solution
 
bramsquadCommented:
i think all SQL date data types have to be passed inbetween a set of pound signs

for example, instead of passing the string

14/9/2004

pass

#14/9/2004#

~b
0
 
bramsquadCommented:
it also could be that your system is expecting a date in the format mm/dd/yyyy
0
 
saumathurAuthor Commented:
so how do i make the system accept the date i nthe format dd/mm/yyyy?
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
YZlatCommented:
bramsquad # # will work with Access, not sql server.

saumathur , why don't you convert your date to mm/dd/yyyy format before the insertion?
0
 
YZlatCommented:
myDate.ToString("mm/dd/yyyy")
0
 
ihenryCommented:
With the following formattings SQL Server would take the date in correct format.
 
parameterReservedOn.ToString("yyyyMMdd")
or
parameterReservedOn.ToString("yyyy-MM-dd")
or
parameterReservedOn.ToString("u")     // u equal to yyyy-MM-dd hh:mm:ss
0
 
saumathurAuthor Commented:
I want them to bein the database in the format dd/mm/yyyy. That is a requirement of the project.
In the code, where i go
 SqlParameter parameterReservedOn = new SqlParameter("@ReservedOn",                    
                                                                                                  SqlDbType.DateTime, 8);
               parameterReservedOn.Value = ReservedOn;
               myCommand.Parameters.Add(parameterReservedOn);

I tried to say parameterReservedOn.Value = Convert.ToDateTime(ReservedOn);
THis gave me the same error.
Any thoughts ?
Thanks
0
 
YZlatCommented:
pass the data to sql server in the locale independent format- yyyy-mm-dd as was mentioned by ihenry
0
 
DotNetLover_BaanCommented:
Use Globalization. Start a new thread with new culture. (this is in VB format, change it to C#)

Imports System.Globalization
Imports System.Threading

Thread.CurrentThread.CurrentCulture = New CultureInfo("en-GB")
TextBox1.Text = Now.ToString("dd/mm/yyyy hh:mm tt")

Try this link:   http://www.microsoft.com/globaldev/getWR/steps/wrg_date.mspx
0
 
123654789987Commented:
I think the problem u are facing is since the default dateTime is 8, u can not give a 10 chars input like - dd/mm/yyyy

So change the statement to

SqlParameter parameterReservedOn = new SqlParameter("@ReservedOn",                    
                                                                                                  SqlDbType.DateTime); //Don't give the length


 parameterReservedOn.Value =(DateTime) ReservedOn.ToString("dd/mm/yyyy");


0
 
AvonWyssCommented:
"I want them to bein the database in the format dd/mm/yyyy. That is a requirement of the project."

Note that the dates are always stored in binary format in the database, which is completely independent of the actual textual format you may be using. You can format a datetime to just any format you need after retrieving them from the database.
0
 
saumathurAuthor Commented:
User  123654789987 i tried what u said with no size given to the argument and this statement

 parameterReservedOn.Value =(DateTime) ReservedOn.ToString("dd/mm/yyyy");

this did not even compile, as it says that this dd/mm/yyyy is not a valid System.IFormatProvider provider.

i tried to put in .ToString("d") and (d) still got the same compile error
0
 
123654789987Commented:
Try the following

parameterReservedOn.Value = Convert.ToDateTime(ReservedOn.ToString("d"));

I am not getting any compile error
0
 
saumathurAuthor Commented:
The thing is, that in the Database Table, the value is stored in the format
 dd/mm/yyyy

on the DataGrid, that is displayed on the webusercontrol it is in the form
9/13/2004 12:00:00 AM, which is mm/dd/yy hh:mm:ss AM/PM

The value extracted from the datagrid cell after getting rid og the hours and secnds is
string res which is "9/13/2004"

Now, this is the value that is being fed into the function mentioned above as ReservedOn

So, maybe this is creating havoc somewhere...!!! I tried to use the statement
parameterReservedOn.Value = (DateTIme)ReservedOn.Tostring("culture"); where culture is of the  CultureInfo and has been specified to "en-AU" format
THis gives me an error saying can convert string to type datetime.

Can someone tell me how to fix this..its eating my brain.!!!
Thanks
0
 
saumathurAuthor Commented:
" Try the following

parameterReservedOn.Value = Convert.ToDateTime(ReservedOn.ToString("d"));

I am not getting any compile error"

I tried that, no compile error, but still the same error on the webpage
String was not recognised as valid DateTime
0
 
AvonWyssCommented:
"The thing is, that in the Database Table, the value is stored in the format
 dd/mm/yyyy"

No it is NOT. It's just DISPLAYED like that. It's stored as binary if it is a DateTime field in the database.
0
 
saumathurAuthor Commented:
oh ok...fair enough..!!

But how do i get round this .... i still cant figure it out.!
0
 
AvonWyssCommented:
SqlParameter parameterReservedOn = new SqlParameter("@ReservedOn",                    
                                                                                                  SqlDbType.DateTime, 8);
               parameterReservedOn.Value = DateTime.Parse(ReservedOn);
               myCommand.Parameters.Add(parameterReservedOn);

(Assuming that ReservedOn is a string containing the date)
0
 
saumathurAuthor Commented:
Still the same error:
THis is in the debug window:

      ReservedOn      "9/13/2004 "      string
and the error is:
String was not recognized as a valid DateTime.
error on this line: Line 454:      parameterReservedOn.Value = DateTime.Parse(ReservedOn);

earlier it was in the myCOmmand.ExecuteNonQuery();

0
 
AvonWyssCommented:
Okay. In this case, try this:
parameterReservedOn.Value = DateTime.Parse(ReservedOn, "MM/dd/yyyy", DateTimeFormatInfo.InvariantInfo);
(note that System.Globalization must be in the using clauses)
0
 
AvonWyssCommented:
Note that this differs from what you wrote in the original question:
14/9/2004 must be dd/MM/yyyy
but
9/13/2004 must be MM/dd/yyyy
0
 
saumathurAuthor Commented:
C:\Share\rmit01\Rainbow\DesktopModules\RMIT\Four\DesignStore\CDesignStoreDB.cs(454): The best overloaded method match for 'System.DateTime.Parse(string, System.IFormatProvider, System.Globalization.DateTimeStyles)' has some invalid arguments

THis is the error im getting.

Regardin ur previous comment, yes ur right, but the thing is that in the database table, when i view it, it is like 13/09/2004 but when it is displayed on the datagrid, its of the form 9/13/2004 12.00.00 AM Thats what i meant.
0
 
AvonWyssCommented:
Um, try ParseExact() instead of Parse()
0
 
saumathurAuthor Commented:
Damn..now i get the error
String was not recognized as valid DateTime in the line
DateTime.ParseExact(ReservedOn, "MM/dd/yyyy", DateTimeFormatInfo.InvariantInfo);
0
 
AvonWyssCommented:
So. What is the format you're trying to parse here? Set a breakpoint and tell us: is the first part the day or the month?
0
 
saumathurAuthor Commented:
i set a break point, and the value in reservedOn before this line is executed is
      ReservedOn      "9/13/2004 "      string
Now, I want to get this into the database as type Sql type datetime. i would like to get it to be like dd/mm/yyyy and not mm/dd/yyyy

So should i try to make it DateTime.ParseExact(ReservedOn, "dd/mm/yyyy",....etc.
0
 
saumathurAuthor Commented:
Tried to make it dd/mm/yyyy, still the same problem
0
 
AvonWyssCommented:
Note: mm stays for minutes, MM is months!
0
 
saumathurAuthor Commented:
yeah i made that change, still the same error..!!! DUnno how to get rid of this..!!
0
 
AvonWyssCommented:
Try "M/d/yyyy".
0
 
AvonWyssCommented:
And if that doesn't help, maybe try ReservedOn.Trim() (this will get rid of any whitespace before or after the date in the string).
0
 
saumathurAuthor Commented:
You wont believe this...NONE of those worked..!!!! boohoo!!

This is the debug window for ParameterReservedOn:

+      _value      "9/13/2004"      System.Object
      string      "9/13/2004"      string
      _version      Current      System.Data.DataRowVersion
      ActualSize      <error: an exception of type: {System.FormatException} occurred>      int
      CoercedValue      <error: an exception of type: {System.FormatException} occurred>      System.Object

So, the string inside is alright, but why does it not get changed to proper format
0
 
AvonWyssCommented:
Did you already try "d/M/yyyy"?
0
 
saumathurAuthor Commented:
yes i did. i tried d/M/yyyy and M/d/yyyy
0
 
AvonWyssCommented:
     public static void Main() {
            string reservedOn="9/13/2004";
            DateTime dt=DateTime.ParseExact(reservedOn, "M/d/yyyy", DateTimeFormatInfo.InvariantInfo);
            Console.WriteLine(dt);
      }

This does work for me (compiles and outputs the wanted date).
0
 
saumathurAuthor Commented:
I did the following:

SqlParameter parameterReservedOn = new SqlParameter("@ReservedOn", SqlDbType.DateTime, 8);
      string resOn = ReservedOn;
      DateTime dt = DateTime.ParseExact(resOn, "M/d/yyyy", DateTimeFormatInfo.InvariantInfo);
      parameterReservedOn.Value = dt;
      myCommand.Parameters.Add(parameterReservedOn);

And got this:
String was not recognized as a valid DateTime.
in line 453:
 DateTime dt = DateTime.ParseExact(resOn, "M/d/yyyy", DateTimeFormatInfo.InvariantInfo);

when the value goes into the database, thats when something is happening to it i reckon, if only i knew what
0
 
saumathurAuthor Commented:
oh and i tried M/d/yyyy, d/M/yyyy, dd/MM/yyyy, MM/dd/yyyy, same result
0
 
AvonWyssCommented:
Well, I'm pretty certain that your string contents is not correct. Does it work if you substitute it with a string literal just as I did?
0
 
saumathurAuthor Commented:
string resOn = "9/13/2004";
parameterReservedOn.Value = resOn;
myCommand.Parameters.Add(parameterReservedOn);
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();

String was not recognized as a valid DateTime.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

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

Source Error:

Line 460:
Line 461:                  myConnection.Open();
Line 462:                  myCommand.ExecuteNonQuery(); //here is the error
Line 463:                  myConnection.Close();
Line 464:            }
 
beats the hell out of me..!!!!
0
 
AvonWyssCommented:
Yeah, of course, you're not longer parsing the DateTime yourself. What happens if you ***ONLY*** replace the resOn with the listeral as I asked?

string resOn = "9/13/2004";
DateTime dt = DateTime.ParseExact(resOn, "M/d/yyyy", DateTimeFormatInfo.InvariantInfo);
parameterReservedOn.Value = dt;
myCommand.Parameters.Add(parameterReservedOn);
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
0
 
saumathurAuthor Commented:
Ok, I did what u said and it worked..!!!!

So, why wasnt it working before..!!! I was chekcing the watches and the values before were exactly the same, then why didnt it work..?? and how do i make it work now..!!
0
 
AvonWyssCommented:
Your string contains illegal data. Just as I suggested a very long time ago.

parameterReservedOn.Value=DateTime.ParseExact(ReservedOn.Trim(), "M/d/yyyy", DateTimeFormatInfo.InvariantInfo);

Does it work with this?
0
 
saumathurAuthor Commented:
ths function is being called so:
oDB.UpdateHasTaken(10239, PackDescr, StudNo, res); //res is the value fed in as ReservedOn

just to let u know where the value is coming there from

String ReservOn = dgi.Cells[4].Text; //this is the value in the datagrid and dgi is DataGridItems
string res = ReservOn.Substring(0, 10); //this i did to get rid of the hh:mm:ss from the data in the datagrid, cos as mentioned before, in the datagrid on the page it is displayed as MM/dd/yyyy hh:mmm:ss
0
 
AvonWyssCommented:
Well, the problem is, Substring(0,10) will only work if the month an day both use two digits. But in this case, the month only has one digit, right? So this breaks your code.

Try this:
string res = ReservOn.Substring(0, ReservOn.IndexOf(' '));
0
 
saumathurAuthor Commented:
YES YES YES YES !!!!!!! It works now....
parameterReservedOn.Value=DateTime.ParseExact(ReservedOn.Trim(), "M/d/yyyy", DateTimeFormatInfo.InvariantInfo);  worked perfectly

Thank you sooo much AvonWyss..!!! seriously..thanks a lot, will give u excellent grade dude.. thanks a lot....thanks a lot..

really appreciate you helping me for the past soooo many hours..!!
thanks so much
0
 
AvonWyssCommented:
Good if it works....
But PLEASE also change the Substring() like I suggested in my previous comment, because if you don't do this, it will break on dates where the day and the month are just one digit (like "9/1/2004")!
0
 
saumathurAuthor Commented:
ok...thanks
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 21
  • 17
  • 3
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now