Convert a string to smalldatetime

Question

I have a string arranged in a way that would match the format yyyy-dd-MM HH:mm:ss It might look like this 2010-20-12 13:30:00

I need to insert this into a smalldatetime column in SQL Server. The format of the column is yyyy-MM-dd HH:mm:ss

I need the string to look like this 2010-12-20 13:30:00 or else SQL Server will get the month and day confused.

Thanks for your thoughts

Solution 1

Don't send your data to SQL server using a string to start with. Instead, use a parameterized SQL statement, and get the driver to do the work for you after you specify a DateTime. (See the SqlCommand.Parameters documentation for an example.)

This is how you should deal with pretty much all values - especially those entered by users. As well as not having to worry about formatting, this will prevent SQL injection attacks.

So that just leaves the task of parsing your input string as a DateTime, which is best done with DateTime.ParseExact or DateTime.TryParseExact, depending on whether you want the result of a parsing failure to be an exception or not.

Solution 2

you can try something like this:

//First convert string to DateTime
DateTime dt;
dt = Convert.ToDateTime("2010-20-12 13:30:00");

//Second Convert DateTime to formatted string
string t_time;
t_time = dt.ToString("yyyy-dd-MM hh:mm tt");

HTH.

Solution 3

You shouldn't need to convert to a string. Use a parameterized sql query or a stored procedure and just provide a datetime object as the parameter. The sql command will handle the insert just fine.

If you only have the string, you can turn it into a datetime using DateTime.Parse and a DateTimeFormatInfo (see this documentation for assistance with creating the format info).

Comments

[SubniC]

It is not the efficient way :)

[SubniC]

The OP ask literally "I need the string to look like this 2010-12-20 13:30:00" i don't think my answer is that bad to down-vote :(

[Louis Waweru]

Thanks for the reminder about parameters. I used them on the web app. This is for a mobile device that needs to sync some sqlite data with the web app, and I didn't really consider injection.