CODEWITHSUNDEEP
asp.netsql-servervb.net
Chidi Okeh
Chidi Okeh

Reputation: 1557

How do I convert string date format to datetime date format?

Our users are storing date values in the following format:

Mon. DD, YYYY

Example:

Sept. 23, 2013

Notice the period after Mon.

We would like to convert this format to:

MM/dd/yyyy

Example:

9/23/2013

I have tried this:

SELECT CONVERT(VARCHAR(10), TrainingDates, 101) AS TrainingDates
FROM tblTrainingDates

but I am getting Sept. 23,

As you can see, not only am I not getting the format I want, the year part is not even rendering

Any thoughts how to handle this?

Thank you in advance.

Upvotes: 0

Views: 1093

Answers (2)

IvanH
IvanH

Reputation: 5159

Date decoding can be done before updating a database

Dim inp As String = "Sept. 23, 2013"
Dim fi As Globalization.DateTimeFormatInfo
fi = New System.Globalization.DateTimeFormatInfo
fi.LongDatePattern = "MMM. dd, yyyy"
fi.AbbreviatedMonthNames = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sept", "Oct", "Nov", "Dec", ""}
Dim d As Date = DateTime.ParseExact(inp, "D", fi)
Dim result as String =  d.ToString("MM\/dd\/yyyy")

The key point is using DateTimeFormatInfo Class, which allows complete customization of formats. Please note that in AbbreviatedMonthNames is Sep replaced by Sept.

Instead of ParseExact it is possible to use TryParseExact to avoid exception when input string is not in correct format.

Upvotes: 0

mata
mata

Reputation: 361

This should do it:

SELECT CONVERT(DATETIME, REPLACE('Sept. 23, 2013','.',''), 107)

This removes dot in string and converts to datetime format 107.

And this returns the string you wanted (MM/DD/YYYY):

SELECT CONVERT(VARCHAR(10), CONVERT(DATETIME, REPLACE('Sept. 23, 2013','.',''), 107), 101)

For other format types see: http://www.sqlusa.com/bestpractices/datetimeconversion/ and http://www.sql-server-helper.com/tips/date-formats.aspx

Edit:
It turns out it works for me because of Slovenian locale (Sept.). This one should work in English locale:

SELECT CONVERT(VARCHAR(10), CONVERT(DATETIME, SUBSTRING('Sept. 23, 2013', 0, 4) + ' ' + SUBSTRING('Sept. 23, 2013',6,9), 107), 101)

As a function.

Upvotes: 2

Related Questions