Solved

DateTime format problem when passing in as Sql Parameter.

Posted on 2004-09-13
47
1,360 Views
Last Modified: 2012-06-27
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
Comment
Question by:saumathur
  • 21
  • 17
  • 3
  • +4
47 Comments
 
LVL 8

Expert Comment

by:bramsquad
ID: 12044826
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
 
LVL 8

Expert Comment

by:bramsquad
ID: 12044837
it also could be that your system is expecting a date in the format mm/dd/yyyy
0
 

Author Comment

by:saumathur
ID: 12045085
so how do i make the system accept the date i nthe format dd/mm/yyyy?
0
 
LVL 35

Expert Comment

by:YZlat
ID: 12045141
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
 
LVL 35

Expert Comment

by:YZlat
ID: 12045231
myDate.ToString("mm/dd/yyyy")
0
 
LVL 20

Expert Comment

by:ihenry
ID: 12045281
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
 

Author Comment

by:saumathur
ID: 12045283
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
 
LVL 35

Expert Comment

by:YZlat
ID: 12045982
pass the data to sql server in the locale independent format- yyyy-mm-dd as was mentioned by ihenry
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 12050966
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
 
LVL 10

Expert Comment

by:123654789987
ID: 12051603
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
 
LVL 14

Expert Comment

by:AvonWyss
ID: 12051954
"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
 

Author Comment

by:saumathur
ID: 12052427
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
 
LVL 10

Expert Comment

by:123654789987
ID: 12052656
Try the following

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

I am not getting any compile error
0
 

Author Comment

by:saumathur
ID: 12052739
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
 

Author Comment

by:saumathur
ID: 12052746
" 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
 
LVL 14

Expert Comment

by:AvonWyss
ID: 12053113
"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
 

Author Comment

by:saumathur
ID: 12053221
oh ok...fair enough..!!

But how do i get round this .... i still cant figure it out.!
0
 
LVL 14

Expert Comment

by:AvonWyss
ID: 12053429
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
 

Author Comment

by:saumathur
ID: 12053642
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
 
LVL 14

Expert Comment

by:AvonWyss
ID: 12053684
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
 
LVL 14

Expert Comment

by:AvonWyss
ID: 12053701
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
 

Author Comment

by:saumathur
ID: 12053785
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
 
LVL 14

Expert Comment

by:AvonWyss
ID: 12053802
Um, try ParseExact() instead of Parse()
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:saumathur
ID: 12053898
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
 
LVL 14

Expert Comment

by:AvonWyss
ID: 12053917
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
 

Author Comment

by:saumathur
ID: 12053977
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
 

Author Comment

by:saumathur
ID: 12054011
Tried to make it dd/mm/yyyy, still the same problem
0
 
LVL 14

Expert Comment

by:AvonWyss
ID: 12054059
Note: mm stays for minutes, MM is months!
0
 

Author Comment

by:saumathur
ID: 12054111
yeah i made that change, still the same error..!!! DUnno how to get rid of this..!!
0
 
LVL 14

Expert Comment

by:AvonWyss
ID: 12054267
Try "M/d/yyyy".
0
 
LVL 14

Expert Comment

by:AvonWyss
ID: 12054293
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
 

Author Comment

by:saumathur
ID: 12054468
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
 
LVL 14

Expert Comment

by:AvonWyss
ID: 12054518
Did you already try "d/M/yyyy"?
0
 

Author Comment

by:saumathur
ID: 12054542
yes i did. i tried d/M/yyyy and M/d/yyyy
0
 
LVL 14

Expert Comment

by:AvonWyss
ID: 12054619
     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
 

Author Comment

by:saumathur
ID: 12054739
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
 

Author Comment

by:saumathur
ID: 12054861
oh and i tried M/d/yyyy, d/M/yyyy, dd/MM/yyyy, MM/dd/yyyy, same result
0
 
LVL 14

Expert Comment

by:AvonWyss
ID: 12054911
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
 

Author Comment

by:saumathur
ID: 12054987
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
 
LVL 14

Expert Comment

by:AvonWyss
ID: 12055028
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
 

Author Comment

by:saumathur
ID: 12055139
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
 
LVL 14

Accepted Solution

by:
AvonWyss earned 400 total points
ID: 12055158
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
 

Author Comment

by:saumathur
ID: 12055180
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
 
LVL 14

Expert Comment

by:AvonWyss
ID: 12055227
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
 

Author Comment

by:saumathur
ID: 12055297
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
 
LVL 14

Expert Comment

by:AvonWyss
ID: 12055324
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
 

Author Comment

by:saumathur
ID: 12055351
ok...thanks
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now