convert string date with 3 char month

[JCBond]

Hi all
I have a varchar column in a DB with dates. I'm trying to convert the string (example: 'Apr-10-2009') (MMM-dd-yyyy) to a datetime but gives:
varchar data type to a datetime data type resulted in an out-of-range value.
I realize it has to be the 3 char month but not sure what convert method I need to do this.
Any suggestions?

[justsomeguy]

Your database should give you a function to convert a date where you tell it the format of the string to expect.  If you're trying to run it over the entire table then make sure there are no null values or other bad data.  A misspelled value in one row will cause it to break.

[JCBond]

Thanks for your response.
Yes, I was thinking about that all through the process of the Convert / Cast possibilities & attempts.
The one test I did through my SQL Server Management Studio with local SQL Express was to recreate a temp table from the Employee table, but change the one varchar field with that date string entry to a datetime field.
I then did an "Import" into the new temp table to see if it would throw an error with a misspelled 3 char month, or an invalid day-year value  -  there were no errors.
Of course, I did check for nulls in the original varchar field... none

[justsomeguy]

If you're using SQL Server, it looks like Mon-dd-yyyy is not one of the supported formats:

https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/

You would first need to convert that field to a supported format using the string functions, then you can use the convert function to convert it to a datetime.  e.g.:

convert(datetime, '30 Dec 2006', 106)

[shaili_shah]

Hello,
You can use the convert() function in SQL to convert your string date into date time object.

E.x.

SELECT CONVERT(varchar, 'yourstringcolumn', 113);

 

[ishan_shah]

Hello,
You can use the convert() function in SQL to convert your string date into date time object. You can use different date-time objects for displaying date and time in different format.

E.x.

SELECT CONVERT(varchar, 'yourstringcolumn', 113);