| MySQL | T-SQL | |
| Strings | ||
| String Replace | REPLACE(haystack,needle,replacement) | REPLACE(haystack,needle,replacement) |
| String Position | INSTR(haystack, needle) LOCATE(needle, haystack [, offset]) | PATINDEX(%needle%, haystack) CHARINDEX(needle, haystack [, offset]) |
| String Concatenation | CONCAT(string1,string2[,stringN]) | string1 + string2 SS v2012+: CONCAT(string1, string2 [, stringN ]) |
| String Substring | SUBSTRING( string,start,length ) | SUBSTRING( string,start,length ) |
| String Length | LENGTH(string) BIT_LENGTH(string) CHAR_LENGTH(string) | LEN(string) DATALENGTH(string) |
| Dates / Times | ||
| Weekday Name | DAYNAME(now()) | DATENAME(dd, getdate()) |
| Weekday Number | DAYOFWEEK(now()) | DATEPART(dw, getdate()) |
| Month Name | MONTHNAME(now()) | DATENAME(mm, getdate()) |
| Month Number | MONTH(now()) | DATEPART(mm, getdate()) |
| European Date (dd/mm/yyyy) | DATE_FORMAT(now(), '%d/%m/%Y') | CONVERT(varchar, getdate(), 103) |
| Time to Seconds | TIME_TO_SEC(now()) | (DATEPART(hour, getdate()) * 3600) + (DATEPART(minute, getdate()) * 60) + (DATEPART(second, getdate())) |
| Seconds to Time | SEC_TO_TIME( seconds ) | CONVERT(varchar, DATEADD(ms, seconds * 1000, 0), 114) |
| Add/Subtract Date | DATE_ADD(date,INTERVAL number datepart) DATE_SUB(date,INTERVAL number datepart) | DATEADD(datepart, number, date) DATESUB(datepart, number, date) |
| Other | ||
| If-then-else | IF( expression,value_if_true,value_if_false ) | IIF( expression,value_if_true,value_if_false ) |
| If Null | IFNULL(column_name,value_if_null) | ISNULL(column_name,value_if_null) |
| Rank/Rownum |
(@rownum:=@rownum+1) AS Rownum LIMIT from_index, number_of_rows | ROW_NUMBER() OVER(ORDER BY column_name ASC) AS Rownum |
Source(s):
- MSDN Microsoft Library (DATEPART (Transact-SQL))
- MSDN Microsoft Library (DATENAME (Transact-SQL))
- MySQL Documentation - String Functions
- MySQL Documentation - Date and Time Functions
Category: Databases :: Article: 480
Add comment