Error converting data type nvarchar to datetime, any suggestion
-
hi, cmd.CommandType = CommandType.StoredProcedure; string filldate = GridView2.Rows[e.RowIndex].Cells[2].Text; string freedate = GridView2.Rows[e.RowIndex].Cells[3].Text; cmd.Parameters.AddWithValue("@filleddate",filldate); cmd.Parameters.AddWithValue("@freedate", freedate); cmd.Parameters.AddWithValue("@nameofcustomer", GridView2.Rows[e.RowIndex].Cells[6].Text); try { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); } and stored procedure is: Insert into PresidentialSuite(DateWhenFilled,DateWhenFree,NameOfCustomer) values (@filleddate,@freedate,@nameofcustomer); SET @filleddate = Convert(datetime,@filleddate); SET @freedate = Convert(datetime,@freedate); Iam not converting any nvarchar to datetime. why iam getting this error. i tried number of ways of converting string to datetime in my C# code, but failed. so i used stored procedure. thanks in advance
-
hi, cmd.CommandType = CommandType.StoredProcedure; string filldate = GridView2.Rows[e.RowIndex].Cells[2].Text; string freedate = GridView2.Rows[e.RowIndex].Cells[3].Text; cmd.Parameters.AddWithValue("@filleddate",filldate); cmd.Parameters.AddWithValue("@freedate", freedate); cmd.Parameters.AddWithValue("@nameofcustomer", GridView2.Rows[e.RowIndex].Cells[6].Text); try { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); } and stored procedure is: Insert into PresidentialSuite(DateWhenFilled,DateWhenFree,NameOfCustomer) values (@filleddate,@freedate,@nameofcustomer); SET @filleddate = Convert(datetime,@filleddate); SET @freedate = Convert(datetime,@freedate); Iam not converting any nvarchar to datetime. why iam getting this error. i tried number of ways of converting string to datetime in my C# code, but failed. so i used stored procedure. thanks in advance
-
I suspect your date format is not what is expected by sql server, usually because one is using mm/dd/yyyy and the other is using dd/mm/yyyy.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
Hi, thanks for reply, due to those reasons i left the conversion to sqlserver, i used the 'convert' in stored procedure. bec i tried almost all ways of converting in my C# code
-
hi, cmd.CommandType = CommandType.StoredProcedure; string filldate = GridView2.Rows[e.RowIndex].Cells[2].Text; string freedate = GridView2.Rows[e.RowIndex].Cells[3].Text; cmd.Parameters.AddWithValue("@filleddate",filldate); cmd.Parameters.AddWithValue("@freedate", freedate); cmd.Parameters.AddWithValue("@nameofcustomer", GridView2.Rows[e.RowIndex].Cells[6].Text); try { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); } and stored procedure is: Insert into PresidentialSuite(DateWhenFilled,DateWhenFree,NameOfCustomer) values (@filleddate,@freedate,@nameofcustomer); SET @filleddate = Convert(datetime,@filleddate); SET @freedate = Convert(datetime,@freedate); Iam not converting any nvarchar to datetime. why iam getting this error. i tried number of ways of converting string to datetime in my C# code, but failed. so i used stored procedure. thanks in advance
suni_dotnet wrote:
i tried number of ways of converting string to datetime in my C# code, but failed.
You tried DateTime.Parse(...) and it failed? :confused: Obviously the input needs to be validated before even getting to your database.
only two letters away from being an asset
-
hi, cmd.CommandType = CommandType.StoredProcedure; string filldate = GridView2.Rows[e.RowIndex].Cells[2].Text; string freedate = GridView2.Rows[e.RowIndex].Cells[3].Text; cmd.Parameters.AddWithValue("@filleddate",filldate); cmd.Parameters.AddWithValue("@freedate", freedate); cmd.Parameters.AddWithValue("@nameofcustomer", GridView2.Rows[e.RowIndex].Cells[6].Text); try { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); } and stored procedure is: Insert into PresidentialSuite(DateWhenFilled,DateWhenFree,NameOfCustomer) values (@filleddate,@freedate,@nameofcustomer); SET @filleddate = Convert(datetime,@filleddate); SET @freedate = Convert(datetime,@freedate); Iam not converting any nvarchar to datetime. why iam getting this error. i tried number of ways of converting string to datetime in my C# code, but failed. so i used stored procedure. thanks in advance
what format is the original date in? and what format is the database storing dates? it should be fairly simple to convert to required date in c# not good to use stored procedure if original date and stored date formats are different cuz it cant handle the days and month being switched round i.e. dd/MM/yyyy and MM/dd/yyyy
-
what format is the original date in? and what format is the database storing dates? it should be fairly simple to convert to required date in c# not good to use stored procedure if original date and stored date formats are different cuz it cant handle the days and month being switched round i.e. dd/MM/yyyy and MM/dd/yyyy
i need to input values through gridview (cell),into db(sqlserver, datatype is datetime) i didnot specify any format, my code takes the text values and converts to datetime(stored procedure) and stores. i tried: Convert.ToDatetime DateTime.Parse using IFormatterProvider--Globilization...
-
i need to input values through gridview (cell),into db(sqlserver, datatype is datetime) i didnot specify any format, my code takes the text values and converts to datetime(stored procedure) and stores. i tried: Convert.ToDatetime DateTime.Parse using IFormatterProvider--Globilization...
where does the nvarchar come into it then? are you passing the datetime value into the database via an sql statement? if the error is that the database is receiving an nvarchar when it wants a datetime, you may have a issue with your sql statement. Datetime needs to be inclosed with the hash symbols (I Think) i.e. #01/01/2000#
-
suni_dotnet wrote:
i tried number of ways of converting string to datetime in my C# code, but failed.
You tried DateTime.Parse(...) and it failed? :confused: Obviously the input needs to be validated before even getting to your database.
only two letters away from being an asset
hi, how can i validate. can use DateTime.TryParse(); ?
-
hi, cmd.CommandType = CommandType.StoredProcedure; string filldate = GridView2.Rows[e.RowIndex].Cells[2].Text; string freedate = GridView2.Rows[e.RowIndex].Cells[3].Text; cmd.Parameters.AddWithValue("@filleddate",filldate); cmd.Parameters.AddWithValue("@freedate", freedate); cmd.Parameters.AddWithValue("@nameofcustomer", GridView2.Rows[e.RowIndex].Cells[6].Text); try { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); } and stored procedure is: Insert into PresidentialSuite(DateWhenFilled,DateWhenFree,NameOfCustomer) values (@filleddate,@freedate,@nameofcustomer); SET @filleddate = Convert(datetime,@filleddate); SET @freedate = Convert(datetime,@freedate); Iam not converting any nvarchar to datetime. why iam getting this error. i tried number of ways of converting string to datetime in my C# code, but failed. so i used stored procedure. thanks in advance
Don't convert in the database. You should convert in C# first, because .NET handles times in a completely different way than SQL Server. SQL Server requires times input as strings to follow a specific format. If you convert in C# first, ADO.NET will take care of the rest:
string freedateraw = Gridview2.Rows[e.RowIndex].Cells[3].Text;
DateTime? freedate = (freedateraw == null ? (DateTime?)null : Convert.ToDateTime(freedateraw));cmd.Parameters.AddWithValue("@freedate", freedate);
-
hi, how can i validate. can use DateTime.TryParse(); ?
suni_dotnet wrote:
how can i validate.
:rolleyes: Maybe something like a validation control, regex, using a DateTime picker, using your brain would be the first step.
only two letters away from being an asset
-
where does the nvarchar come into it then? are you passing the datetime value into the database via an sql statement? if the error is that the database is receiving an nvarchar when it wants a datetime, you may have a issue with your sql statement. Datetime needs to be inclosed with the hash symbols (I Think) i.e. #01/01/2000#
hi, i tried with #date#, its not working. my stored procedure(present in my main question)i didnot use any nvarchar. i declared variables as datetime. i used IFormatProvider theCultureInfo = new System.Globalization.CultureInfo("en-US", true); DateTime theDateTime = DateTime.ParseExact(filldate, "mm/dd/yyyy HH:mm:ss", theCultureInfo); i entered the value in same format in cell. i got the error: String was not recognized as a valid DateTime.
-
Don't convert in the database. You should convert in C# first, because .NET handles times in a completely different way than SQL Server. SQL Server requires times input as strings to follow a specific format. If you convert in C# first, ADO.NET will take care of the rest:
string freedateraw = Gridview2.Rows[e.RowIndex].Cells[3].Text;
DateTime? freedate = (freedateraw == null ? (DateTime?)null : Convert.ToDateTime(freedateraw));cmd.Parameters.AddWithValue("@freedate", freedate);
What if freedateraw is an empty string? freedateraw == null won't work and Convert.ToDateTime(freedateraw) throw an exception. If freedateraw is not null or empty the convert may still throw an exception if it isn't in a valid format. There is no point to this cast;(DateTime?)null
only two letters away from being an asset
-
suni_dotnet wrote:
how can i validate.
:rolleyes: Maybe something like a validation control, regex, using a DateTime picker, using your brain would be the first step.
only two letters away from being an asset
hi i tried alot before posting this question. searched google (1 week)... but couldnot find any solution.. i used my brain by all means but couldnot find solution. iam a learner so does everyone. if everyone knows everything.. there wont be anything... iam here to learn... if possible plz guide me rather than ..
-
Don't convert in the database. You should convert in C# first, because .NET handles times in a completely different way than SQL Server. SQL Server requires times input as strings to follow a specific format. If you convert in C# first, ADO.NET will take care of the rest:
string freedateraw = Gridview2.Rows[e.RowIndex].Cells[3].Text;
DateTime? freedate = (freedateraw == null ? (DateTime?)null : Convert.ToDateTime(freedateraw));cmd.Parameters.AddWithValue("@freedate", freedate);
i tried i got error, string not recognised as valid datetime. i used many formatings "mm/dd/yyyy",... same error
-
hi i tried alot before posting this question. searched google (1 week)... but couldnot find any solution.. i used my brain by all means but couldnot find solution. iam a learner so does everyone. if everyone knows everything.. there wont be anything... iam here to learn... if possible plz guide me rather than ..
In your weeks worth of searching you didn't come across anything like this :omg: :rolleyes: http://msdn.microsoft.com/en-us/library/7kh55542.aspx[^][^]
only two letters away from being an asset
-
In your weeks worth of searching you didn't come across anything like this :omg: :rolleyes: http://msdn.microsoft.com/en-us/library/7kh55542.aspx[^][^]
only two letters away from being an asset
that i know, its getting into argument.. can u help me with little coding. else no problem. thank you
-
that i know, its getting into argument.. can u help me with little coding. else no problem. thank you
What help do you need?!? You have had multiple answers, its time for you to apply it.
only two letters away from being an asset
-
What if freedateraw is an empty string? freedateraw == null won't work and Convert.ToDateTime(freedateraw) throw an exception. If freedateraw is not null or empty the convert may still throw an exception if it isn't in a valid format. There is no point to this cast;(DateTime?)null
only two letters away from being an asset
Actually, there absolutely is a point to the cast. Test it for yourself: string raw = null; DateTime? dt = (String.IsNullOrEmpty(raw) ? null : Convert.ToDateTime(raw)); You will get the following compilation error: Type of conditional expression cannot be determined because there is no implicit conversion between '<null>' and 'System.DateTime' Yes, you are correct, checking for null is not sufficient, checking String.IsNullOrEmpty() is correct. That wasn't the real point of my response, though. But check that ternary statement...there absolutely is a point to that cast, its reqired to compile.
-
i tried i got error, string not recognised as valid datetime. i used many formatings "mm/dd/yyyy",... same error
You need to change your stored proc, too. If your stored proc is like this: CREATE PROCEDURE SomeProc ( @filldate varchar(10), @freedate varchar(10), @name varchar(50) ) Then you need to change that to: CREATE PROCEDURE SomeProc ( @filldate datetime, @freedate datetime, @name varchar(50) ) And get rid of your conversion in the stored proc. You can't convert in both places...either convert in C#, or convert in the stored proc. If you convert in the stored proc, you will have to manually reformat the freedate and filldate strings to conform to the datetime format required by SQL Server to pass them in and convert them in the proc. Converting to DateTime in C# will be simpler, and a more stable implementation, than reformatting and converting in the proc.
-
You need to change your stored proc, too. If your stored proc is like this: CREATE PROCEDURE SomeProc ( @filldate varchar(10), @freedate varchar(10), @name varchar(50) ) Then you need to change that to: CREATE PROCEDURE SomeProc ( @filldate datetime, @freedate datetime, @name varchar(50) ) And get rid of your conversion in the stored proc. You can't convert in both places...either convert in C#, or convert in the stored proc. If you convert in the stored proc, you will have to manually reformat the freedate and filldate strings to conform to the datetime format required by SQL Server to pass them in and convert them in the proc. Converting to DateTime in C# will be simpler, and a more stable implementation, than reformatting and converting in the proc.
hi, i declared as datetime in stored procedure. yeah iam trying in C#. ALTER PROCEDURE InsertSuite @filleddate datetime, @freedate datetime, @nameofcustomer varchar(50) AS BEGIN SET NOCOUNT ON Insert into PresidentialSuite(DateWhenFilled,DateWhenFree,NameOfCustomer) values (@filleddate,@freedate,@nameofcustomer); END dont know why iam getting such error. thanks