Jump to content
Sign in to follow this  
JCBond

convert string date with 3 char month

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?

Share this post


Link to post
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.

Share this post


Link to post
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

Share this post


Link to post
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)

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...