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 jQuery has already written in code the format that my sql is currently set, however at runtime it does not apply

<script type="text/javascript">
$(document).ready(function () {
    $("#<%=txtStart.ClientID %>").dynDateTime({
        showsTime: false,
        ifFormat: "%Y-%m-%d ",
        daFormat: "%l;%M %p, %e %m, %Y",
        align: "BR",
        electric: false,
        singleClick: false,
        displayArea: ".siblings('.dtcDisplayArea')",
        button: ".next()"
    });
});
</script>

Solution 1

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).

Solution 2

You have to remember that there is no format for DateTime.if you want convert some date string into DateTime and if you know the Date format upfront you can use DateTime.ParseExact()

Try This:

string strDate = "21/04/2014";
DateTime dt = DateTime.ParseExact(strDate,"dd/MM/yyyy",
                                                   CultureInfo.InvariantCulture);

now you can store the DateTime variable dt into database but if you want to display the DateTime in some format ex:yyyy-MM-dd you need to call ToString() as below

String datetime = dt.ToString("yyyy-MM-dd");

Comments

[user2552331]

Thanks i'll look into this once again as I've already tried .parseExact and it gives me a compile error. You have a point in retaining its dateformat

[user2552331]

Does this mean I have to replace my <script></script> information and use your coding? I just copied and couldnt understand from another source.

[Jon Skeet]

@user2552331: If it gives a compilation error (rather than an exception) then you probably don't have a string to start with... you should update your question with what you've tried and the exact error. Note that I've given code in C#, whereas your question uses VB... hopefully you can convert my C# into VB easily enough.

[Nayeem Mansoori]

When you are using datepicker in javascript so why are you don't use javascript date format.

[user2552331]

seems like i get a runtime error "String was not recognized as a valid DateTime."

[Sudhakar Tillapudi]

@user2552331: are you trying my code? can you show us your code?

[user2552331]

have tried and gave me an exception "String was not recognized as a valid DateTime." Dim dDate As DateTime = DateTime.ParseExact(Request.Form(txtStart.UniqueID).ToString‌​, "dd/MM/yyyy", CultureInfo.InvariantCulture)

[user2552331]

not sure if this has got to do with the fact that the way i'm obtaining the date time is by using a jquery datetimepicker

[Jon Skeet]

@user2552331: An exception isn't the same as a compile-time error, and we can't help without knowing what the value actually was. All of this information (including the part about the jquery datetimepicker, and the value which is failing) should be in the question, not in comments.

[user2552331]

I've already specified the format of date that i want, the problem is when the retrieved date is actually showing dd/MM/yyyy when it reaches the SQL query..

[Nayeem Mansoori]

Now are you happy :):):)

[user2552331]

sorry .. I tried the code but it seems like its for java and not applicable in asp.net