Jump to content

convert string date with 3 char month


JCBond
 Share

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

  • 4 weeks later...

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

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...