Converting MySQL STR_TO_DATE function
MySQL provides STR_TO_DATE() standard function which convert string into date type. SSMA does not support conversion of STR_TO_DATE() function and when it encounters call to the function, SSMA generates a conversion error message "M2SS0201: MySQL standard function STR_TO_DATE is not supported in current SSMA version"
You can use SQL Server's CONVERT function to replace STR_TO_DATE. Consider the following example:
| MySQL | SQL Server |
| SELECT STR_TO_DATE('06/15/2011', '%m/%d/%Y'); | SELECT CONVERT(datetime, '06/15/2011', 101) |
CONVERT function uses three arguments, the first is the datatype we are converting the string to, second argument is the string value, and the last argument is style. Refer to the table below to determine the style value to use:
| Date Format With century (yyyy) | Style Value | Date Format Without century (yy) | Style Value |
mm/dd/yyyy |
101 | mm/dd/yy |
1 |
yyyy.mm.dd |
102 | yy.mm.dd |
2 |
dd/mm/yyyy |
103 | dd/mm/yy |
3 |
dd.mm.yyyy |
104 | dd.mm.yy |
4 |
dd-mm-yyyy |
105 | dd-mm-yy |
5 |
dd mon yyyy |
106 | dd mon yy |
6 |
Mon dd, yyyy |
107 | Mon dd, yy |
7 |
mm-dd-yyyy |
110 | mm-dd-yy |
10 |
yyyy/mm/dd |
111 | yy/mm/dd |
11 |
yyyymmdd |
112 | yymmdd |
12 |
yyyy-mm-dd hh:mi:ss(24h) |
120 | ||
yyyy-mm-dd hh:mi:ss.mmm(24h) |
121 | ||
yyyy-mm-dd Thh:mi:ss.mmm(no spaces) |
126 | ||
dd mon yyyy hh:mi:ss:mmmAM |
130 | ||
dd/mm/yy hh:mi:ss:mmmAM |
131 | ||
mon dd yyyy hh:mi:ss:mmmAM (or PM) |
109 | ||
dd mon yyyy hh:mi:ss:mmm(24h) |
113 | ||
mon dd yyyy hh:miAM (or PM) |
100 | ||
hh:mi:ss |
108 |
Limitation
CONVERT function requires the value to be a valid date. For example:
SELECT CONVERT(datetime, '00/00/0000', 101)
--Error Message: “The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.”
SELECT convert(datetime, 'wrong value', 101)
--Msg 241, Level 16, State 1, Line 1. “Conversion failed when converting date and/or time from character string.”
Additional error handling or input validation may be required for such situation.
Reference
Comments
Anonymous
June 23, 2014
SELECT STR_TO_DATE('2013/14/12 12:15:07 AM' ,'%Y/%d/%m'); when i excute the Above query "12/14/2013 12:00:00 AM" this result came.... why time stamp is not formatted?Anonymous
June 11, 2015
The SQL Concat function is utilized to associate two strings to outline a particular string. www.inoracle.com/sql-character-functions-with-examples