0

I have a calender field in a dynamic grid and I am getting that date field using jquery like this:

 DLD += $("[name=LDD]").datepicker('getDate');

After selecting the date , I am getting the date in below format

Wed Sep 30 2020 00:00:00 GMT+0500

and then I am inserting this column in sql server through code

I am getting this error

Conversion failed when converting date and/or time from character string.

How can I change my date in required format?

My insertion query is below:

 qry2 += "Insert into 
DLDs(Date) values " +
                            "(" + checkNull(inner_arr[2]) ")";

Either I have to change my format while getting the date or while I am performing insertion but how?

Unknown
  • 171
  • 3
  • 11
  • 2
    [For the 1024 time – DateTime has no format!](https://zoharpeled.wordpress.com/2019/12/19/for-the-1024-time-datetime-has-no-format/) – Zohar Peled Sep 23 '20 at 07:35
  • 2
    First of all, use [parametrized queries](https://stackoverflow.com/questions/4712037/what-is-parameterized-query). Second, what's the type of `checkNull(inner_arr[2])` (I guess `string`)? – SᴇM Sep 23 '20 at 07:37
  • @ZoharPeled not it does not. – Unknown Sep 23 '20 at 07:37
  • Look into `DateTime.TryParse` – VDWWD Sep 23 '20 at 07:37
  • I suggest changing the format in which you extract the date from JavaScript to a more easily parseable one: https://codehandbook.org/javascript-date-format/ . That will enable it to be sent to the server in a sensible format - that's the only point in the process where the date needs to be a string. Then parse it to a DateTime when it's received in the C# (you didn't show us how you actually send/receive it, unfortunately). For passing it from C# to SQL Server, use a DateTime object and parameterised queries. (You should always be using parameters anyway, for security reasons.) – ADyson Sep 23 '20 at 07:38
  • 1
    @ZoharPeled actually the problem here is most likely that `DateTime` isn't being used in c# in this code, and that the client side string is being passed.. probably a simple `DateTime.UTC.Parse` on `inner_arr[2]` is what should be done. but definitely this query building is problematic – Brett Caswell Sep 23 '20 at 07:39
  • 2
    @Unknown if you use `Date` values in jQuery and `Date.toISOFormat()` before posting to the server you won't have any problems. The query is another matter though that has nothing to do with jQuery - just *don't* use string concatenation to construct queries, use parameterized queries. string concatenation leaves you wide open to SQL injection attacks *and* conversion issues. If you use parameterized queries, there won't be any conversion problems – Panagiotis Kanavos Sep 23 '20 at 07:41
  • @PanagiotisKanavos how do i use it while inserting? – Unknown Sep 23 '20 at 07:43
  • 2
    @BrettCaswell the biggest problem is constructing SQL queries through string concatenation. That's begging for a SQL injection attack and has *no* chance of avoiding conversion issues – Panagiotis Kanavos Sep 23 '20 at 07:43
  • 1
    @BrettCaswell That's kinda my point here in the duplicate clause - passing around dates as strings between .net and SQL Server is wrong - that's why I've closed as a duplicate. However, Panagiotis Kanavos does have a very good point here, the bigger issue is, without a doubt, the [SQL Injection](https://zoharpeled.wordpress.com/2020/07/16/back-to-basics-sql-injection/) vulnerability of the code. – Zohar Peled Sep 23 '20 at 07:47
  • @Unknown _"how do i use it while inserting?"_ ...use what? Parameterised queries you mean? There are many, many examples, tutorials and documentation pages available which show you, especially if (as seems likely) you're using ADO.NET. You can easily find out how to do it. – ADyson Sep 23 '20 at 07:49
  • @ZoharPeled _"passing around dates as strings between .net and SQL Server is wrong"_ this is absolutely true. But Brett also has a good point - the root of the issue as well is that the date is being extracted from JS in a string format which isn't easily parseable by .NET's DateTime. So that needs fixing before the OP can hope to use DateTimes and parameters. I don't think your duplicate really covers that. – ADyson Sep 23 '20 at 07:52
  • @ADyson Do you have a better duplicate in mind or would you remove the duplicate vote altogether? This question (like many others) shows hints of many problems in the code, but very little code. Do you think you can answer it and provide a reasonable alternative code? I would happily revoke my duplicate vote if yes – Zohar Peled Sep 23 '20 at 08:00
  • @ZoharPeled no I don't think it's easily answerable with specifics - there's too much code missing as you rightly say. But since multiple duplicates can be set, I think adding another duplicate which covers converting a JS date to .NET DateTime would be appropriate. e.g. https://stackoverflow.com/questions/6702705/how-to-convert-javascript-datetime-to-c-sharp-datetime is the most obvious one. Then the OP is pointed to info which covers every part of the process (or at least, every part they have hinted at to us). – ADyson Sep 23 '20 at 08:02
  • 1
    @ADyson done. I agree, that's a better duplicate list :-) – Zohar Peled Sep 23 '20 at 08:06
  • 1
    @Unknown first of all, don't just append the date as-is in Javascript. Which you shouldn't have to do anyway - if you perform an AJAX call, you can send entire objects. You definitely have problems in your JavaScript code. If you ever have to convert dates to strings in Javascript, use [Date.toISOString](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/toISOString) – Panagiotis Kanavos Sep 23 '20 at 08:07
  • 1
    @Unknown on the server, you should receive strongly-typed objects. Not strings, not arrays. You haven't posted that code either. Even if you get strings, you should parse them into strongly typed ints, DateTimes etc. Finally, your query should use parameters, not string concatenation. It's actually *easier* to write parameterized queries that concatenate strings. The query could be eg `"insert into Tbl1 (Field1) values (@val1)"`. – Panagiotis Kanavos Sep 23 '20 at 08:10
  • In your ADO.NET code you should add parameters to your `SqlCommand` object. Or you could use a library like Dapper to simplify things, eg `connection.ExecuteQuery(sql,new {val1=someDate})` will match the property and parameter by name and execute the `INSERT` statement – Panagiotis Kanavos Sep 23 '20 at 08:11
  • 1
    There's too much code missing to reopen this question and post an answer. There are problems in the Javascript code, both when retrieving values and making an AJAX request. There are definitely problems in the server - why arrays? Which stack are you using? The only clear problem is the SQL query, but you need to fix the other problems too – Panagiotis Kanavos Sep 23 '20 at 08:13
  • 1
    BTW you could get rid of the jQuery datepicker entirely and use [=(https://developer.mozilla.org/en-US/docs/Web/HTML/Element/input/date). All current browsers support this. The `.Value` is already in `YYYY-MM-DD` form, no matter what the display format is – Panagiotis Kanavos Sep 23 '20 at 08:15
  • @PanagiotisKanavos I just needed to know this ``$('#yourid').datepicker({ dateFormat: 'dd-mm-yy' }).val(); `` that solved my problem. – Unknown Sep 23 '20 at 10:32
  • 1
    @Unknown that's a localized format that can easily lead to problems not just due to localiztion, but using two-digit years as well. There's a reason `YYYY-MM-DD` is the de-facto standard format. You don't need *any* of that code if you just use a date input either. jQuery was created to solve 2010's problems, when `input` types were limited. The browsers offer *better* support than jQuery now – Panagiotis Kanavos Sep 23 '20 at 10:34
  • 1
    Panagiotis is right. Ditch the jQuery (unless you're required to support I.E. still) and/or use an unambiguous date format such as the ISO standard `yyyy-mm-dd`. `dd-mm-yy` could be ambiguous when it reaches the server. e.g. how will the server know whether `01-03-20` is 1st March or 3rd Jan? Or whether it's 2020 or 1920 or 2120? Or even whether it's actually representing 20th March 2001, in fact? There's no way for it to tell. At that moment it's just a raw string of text. The server has no knowledge of the context in which the date was entered, or what the user intended. – ADyson Sep 23 '20 at 10:48
  • @ADyson unfortunately i am required to support I.E still – Unknown Sep 24 '20 at 02:33
  • Ok. But you can still send a sensible format to the server – ADyson Sep 24 '20 at 06:11

0 Answers0