SQL Server Migration Assistant (SSMA) Team's Blog

How-to articles, tips-and-tricks, and guidelines for migrating from Oracle/Sybase/MySQL/Access to SQL Server and SQL Azure

Converting MySQL STR_TO_DATE function

Converting MySQL STR_TO_DATE function

  • Comments 1

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

  • 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?

Page 1 of 1 (1 items)
Leave a Comment
  • Please add 5 and 1 and type the answer here:
  • Post