次の方法で共有


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