Converting dd/MM/yyyy input to yyyy-MM-dd in date format not varchar/datetime

Question

After searching for a full day, I'm unable to find a solution, facing multiple conversion errors. Main objective is to convert a `dd/MM/yyyy` to `yyyy-MM-dd` "date" format to fit in SQL Server. I have a SQL Server 2012 database with datatype `date` as a column in my table. I'm using a jQuery datepicker with jquery-1.4.1-vsdoc.js on my project website that outputs `dd/MM/yyyy` which is obtained using ``` Dim dDate As DateTime = DateTime.Parse(Request.Form(txtStart.UniqueID)) ``` Then I'm attempting to do an insert SQL using the date obtained but the database date format is `yyyy-MM-dd`. Have tried this but it doesn't work: ``` SELECT CONVERT(date, CONVERT(DATETIME, '14/10/2011', 0), 120) ``` Tried this and it works but it's of `varchar` data type which is not what I need ``` SELECT REPLACE(CONVERT(VARCHAR(10), '15/4/2014', 111), '/', '-') AS [YYYY-MM-DD] ``` I really need a code either on my website VB/C# code or SQL statement whichever to fulfill this please help My script using the j…

Accepted Answer

Main objective is to convert a dd/MM/yyyy to yyyy-MM-dd "date" format to fit in SQL

Don't! You should avoid string conversions as far as you possibly can. Keep your data in its natural representation for as much of the time as you can.

You may well need to parse the date from `dd/MM/yyyy` to `DateTime` to start with (although if you can avoid even this, do so - we don't know where your data comes from), but then you should leave it as a `DateTime`. Use parameterized SQL instead, passing the value of the parameter to the database as a `DateTime`. Of course, you should also make sure that the fields in your database are using the appropriate type to start with (not `varchar`). Ensuring that your schema matches your data is crucial to running a sane system.

The parsing part should be relatively simple. For example:

```
DateTime date = DateTime.ParseExact(text, "dd/MM/yyyy",
                                    CultureInfo.InvariantCulture);
```

You may want to use a user's culture instead (depending on where the data has come from) and you may want to use `DateTime.TryParseExact` instead (if this is user-entered data rather than something from machine-to-machine communication).

Converting dd/MM/yyyy input to yyyy-MM-dd in date format not varchar/datetime

Solution

Problem