JCBond 0 Posted May 9, 2019 Report Share Posted May 9, 2019 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? Quote Link to post Share on other sites
justsomeguy 1,135 Posted May 10, 2019 Report Share Posted May 10, 2019 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. Quote Link to post Share on other sites
JCBond 0 Posted May 10, 2019 Author Report Share Posted May 10, 2019 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 Quote Link to post Share on other sites
justsomeguy 1,135 Posted May 10, 2019 Report Share Posted May 10, 2019 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) Quote Link to post Share on other sites
shaili_shah 2 Posted June 5, 2019 Report Share Posted June 5, 2019 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); Quote Link to post Share on other sites
ishan_shah 2 Posted June 5, 2019 Report Share Posted June 5, 2019 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); Quote Link to post Share on other sites
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.