TO_DATE — Convert String to Datetime — Oracle to SQL Server Migration
In Oracle, TO_DATE function converts a string value to DATE data type value using the specified format. In SQL Server, you can use CONVERT or TRY_CONVERT function with an appropriate datetime style.
Oracle:
TO_DATE Conversion Overview
| Oracle | SQL Server | |
| Syntax | TO_DATE(string, format) | CONVERT(DATETIME, string, style) |
| TRY_CONVERT(DATETIME, string, style) | ||
| Default Format | Specified by NLS_DATE_FORMAT | Recognizes many formats |
Note that TRY_CONVERT function is available since SQL Server 2012. 
TO_DATE Format Specifiers
Oracle TO_DATE supports the following format specifiers:
| Oracle TO_DATE | Format Specifier |
| YYYY | 4-digit year |
| YY | 2-digit year |
| RRRR | 4-digit or 2-digit year, 20th century used for years 00-49, otherwise 19th |
| MON | Abbreviated month (Jan — Dec) |
| MONTH | Month name (January — December) |
| MM | Month (1 — 12) |
| DY | Abbreviated day (Sun — Sat) |
| DD | Day (1 — 31) |
| HH24 | Hour (0 — 23) |
| HH or HH12 | Hour (1 — 12) |
| MI | Minutes (0 — 59) |
| SS | Seconds (0 — 59) |
Converting Oracle TO_DATE to SQL Server
Unlike Oracle TO_DATE function that allows you to build any format string using format specifiers (YYYY and MM i.e.), in SQL Server, you have to use a datetime style that defines the format for the entire datetime string.
Fortunately, most applications use typical datetime formats in Oracle that can be easily mapped to a datetime format style in SQL Server.
Difference Between CONVERT and TRY_CONVERT in SQL Server
You can use both CONVERT and TRY_CONVERT functions to convert a string to a datetime value.
CONVERT raises an error when it cannot recognize the format, while TRY_CONVERT returns NULL in this case:
MySQL STR_TO_DATE() – Convert Date String to MySQL Date
In this tutorial, we will study the MySQL STR_TO_DATE() function. Suppose you have a table of details of a few people. One of the columns in the table is DOB which specifies the date of birth of each individual as a string.
An example of the date string format is – ‘August 29, 2020’.
You have been tasked with converting this string value to a date value so that you can perform some date operations on it. This is where we use the MySQL STR_TO_DATE() function.
The STR_TO_DATE() function returns a date/datetime value based on a string and a format. Let us understand this better with the syntax and a few examples.
Syntax of MySQL STR_TO_DATE()
Where, ‘string’ is the string that has to be formatted to a date value and
‘format’ is the format to use while formatting the string to a date value.
The ‘format’ can be one or a combination of the following –
- %a – Abbreviated weekday name (Sun to Sat)
- %b – Abbreviated month name (Jan to Dec)
- %c – Numeric month name (0 to 12)
- %D – Day of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, …)
- %d – Day of the month as a numeric value (01 to 31)
- %e – Day of the month as a numeric value (0 to 31)
- %f – Microseconds (000000 to 999999)
- %H – Hour (00 to 23)
- %h – Hour (00 to 12)
- %I – Hour (00 to 12)
- %i – Minutes (00 to 59)
- %j – Day of the year (001 to 366)
- %k – Hour (0 to 23)
- %l – Hour (1 to 12)
- %M – Month name in full (January to December)
- %m – Month name as a numeric value (00 to 12)
- %p – AM or PM
- %r – Time in 12 hour AM or PM format (hh:mm:ss AM/PM)
- %S – Seconds (00 to 59)
- %s – Seconds (00 to 59)
- %T – Time in 24 hour format (hh:mm:ss)
- %U – Week where Sunday is the first day of the week (00 to 53)
- %u – Week where Monday is the first day of the week (00 to 53)
- %V – Week where Sunday is the first day of the week (01 to 53). Used with %X
- %v – Week where Monday is the first day of the week (01 to 53). Used with %x
- %W – Weekday name in full (Sunday to Saturday)
- %w – Day of the week where Sunday=0 and Saturday=6
- %X – Year for the week where Sunday is the first day of the week. Used with %V
- %x – Year for the week where Monday is the first day of the week. Used with %v
- %Y – Year as a numeric, 4-digit value
- %y – Year as a numeric, 2-digit value
Examples of MySQL STR_TO_DATE()
Let us kick things off with a couple of basic examples. Suppose you have the string – ‘15 02 2021’ where 15 is the day, 02 is the month and 2021 represents the year.
Let us convert this to a date value. We will use the SELECT statement and an alias called ‘Result’.
And the output is –
The format tells the MySQL STR_TO_DATE() function in what way should the string be formatted.
Let us look at another example. This time we have the string – ‘May 5, 2020’. To convert this to a date value, we will use the format – ‘%M %d, %Y’. Yes, you need to mention that comma in the format as it is in the string. The query is –
And the output is –
Using the Wrong Date Format
Building on the earlier example, what if we forgot to mention the comma in the format? Consider the string – ‘15,02,2021’. What if we use the format – ‘%M %d %Y’?
This format is incorrect as the numbers in the string are separated by a comma rather than a whitespace. Let us see the query for this.
And the output is –
When we use a wrong ‘format’ parameter value for the STR_TO_DATE() function, we get NULL as our output. Let us see another example of this.
And the output is –
MySQL STR_TO_DATE() with Datetime Values
If you have a date and a time value in a string, you can convert it into a datetime data type value. Let us see a couple of examples of this. Consider the query –
And the output is –
Let us look at another example. We have the string – ‘February 10 2021 6:25:05 AM’. Let us convert it into a datetime value. The query is –
And the output is –
MySQL STR_TO_DATE() With the GET_FORMAT() function
Another application of the GET_FORMAT() function is with the STR_TO_DATE() function. The GET_FORMAT() function can be used in the ‘format’ parameter. Let us see a couple of examples of this. Consider the queries –
And the output is –
Working With Tables
Consider the ‘Persons’ table.
Persons Table
Now let us come back to the problem I mentioned in the beginning. Suppose you have a table of details of a few people. One of the columns in the table is DOB which specifies the date of birth of each individual as a string. An example of the date string format is – ‘August 29, 2020’. You have been tasked with converting this string value to a date value so that you can perform some date operations on it. Let us use the STR_TO_DATE() function for this with the UPDATE statement. The query is –
And the output is –
Conclusion
Formatting date and time values is a very important operation in MySQL and is widely used in a lot of use cases – especially when we have to make the date and time value presentable for the user. I would highly encourage you to practice a couple of examples of this function.
Learn how to convert data with SQL CAST and SQL CONVERT
When working with data, conversion from one data type to another is a common use case. For example, you imported data from Excel and all data is stored as text. However, some columns are of the numeric or datetime format and you want to convert the data to their corresponding data types. In this tutorial, we’ll show you how you can convert between different data types in Microsoft SQL Server.
Solution
The T-SQL language offers two functions to convert data from one data type to a target data type: CAST and CONVERT. In many ways, they both do the exact same thing in a SELECT statement or stored procedure, but the SQL Server CONVERT function has an extra parameter to express style.
The syntax is as follows:
Let’s illustrate with an example. Suppose we have the character string ‘123’ stored as text, but we want to convert it to an integer. Using the two functions, we get the following Transact-SQL statements:
Both return the exact same output:
With CONVERT, we can do a bit more than with SQL Server CAST. Let’s say we want to convert a date to a string in the format of YYYY-MM-DD. We can do this with the following expression:
The number 23 is the style and it tells SQL Server how we want the string to be formatted. For example, if we use 110 we get a completely different formatting:
If you want to use custom formatting strings instead of these pre-defined styles, check out the FORMAT function. You can use FORMAT for converting dates and numerical values to strings, but for other data type conversions you need to stick with CAST or CONVERT. You can learn more about the FORMAT function in the tip Format SQL Server Dates with FORMAT Function.
If you want to learn more about the CONVERT function and different date and time formats check out this article Date and Time Conversions Using SQL Server.
Which to use CAST function or CONVERT function for a SQL query?
When you don’t need a style, CAST and CONVERT are the same. So when would you use one or the other? It boils down to personal preference, but it’s good to know CAST is ANSI SQL (meaning it’s part of the overall SQL standard) while CONVERT isn’t. Migrating SQL code between different database systems is a bit easier if you stick with ANSI standard SQL.
Convert to String using CONVERT or CAST
About any data type can be converted to a string. There are a couple of notable exceptions:
- The image data type cannot be converted to string. It can only be converted to binary or varbinary. This data type is deprecated.
- The timestamp data type cannot be converted to Unicode strings. Despite it’s name, it has nothing to do with dates or time, but is actually some sort of row version. It’s also deprecated.
When converting to string, you need to specify a length, but this is not mandatory. For example, when we convert the following string to varchar data type, we do the following:
However, if we don’t specify the length, we see that a piece of the string is truncated:
Where did the last piece go? When you don’t specify a length, SQL Server will assume a default length of 30 which is shorter than our string. However, this is only when we do this in CAST or CONVERT. When you assign a variable for example, SQL Server uses a default of 1.
A best practice is to always specify a length when declaring data types. When converting a date, you also might run into issues when you don’t specify a style. Let’s take the following example:
Because the style was left out and SQL Server had to choose a default style, the result is truncated:
This can be fixed by choosing an appropriate style (or by making the string larger of course):
When converting numerical data to string data types, you’ll get «unexpected results» if the data is too long to fit in the string. For example:
In the case of Unicode data types an error is returned instead:
Convert to Integer Value using CONVERT or CAST
It’s common when importing data from Excel or flat files, that all data is imported as text. In this case, you’ll want to convert actual numbers to the correct data type. For example:
Converting a decimal number will result in an error if integer is the destination data type:
However, it’s no problem if the source data type is numeric (or decimal):
In this case, the numeric is truncated after the decimal point.
Convert to Numeric using CONVERT or CAST
For clean data, this is the same as converting to an integer.
If the scale is too big (the part before the decimal point), you’ll get an arithmetic overflow:
If the precision is too big (the part after the decimal point), the data is rounded:
If the data is in another culture, for example in Belgium they use a comma instead of a decimal point, you’ll get an error:
This can be resolved by using the REPLACE function:
However, if there are also thousand separators present, this will result again in an issue:
This is because the intermediate result is 123.456.123 which is not a correct number. We can solve this by either removing the thousand separator first and then replacing the comma.
Keep in mind that when you’re converting an entire column of data, one bad row will cause the entire query to fail:
Some rows might be returned though:
To deal with this, you might be tempted to use the ISNUMERIC function. The problem is that this function returns 1 for many examples we might not consider as actually numeric. Even in our example, ‘943,45’ is considered as numeric and the error persists:
To learn more about ISNUMERIC, check out the tip Validate Integer and Decimal Values in SQL Server. However, it’s best to ignore this function and rather use TRY_CONVERT.
Another option is TRY_PARSE, where we can specify a culture. In this case the culture BE-nl is used, which is for the Dutch speaking part of Belgium.
Combining the two functions, we can get to a fully working solution:
Convert to Date using CONVERT or CAST
The most common use case is to convert a string containing some sort of representation of a date to an actual date data type. In many cases, SQL Server will correctly convert the expression, even if no style is specified:
However, it’s considered best practice to always specify a style to avoid confusion. For example, is 01/02/2021 the first of February (like in most parts of the world), or is it the second of January 2021 (like in one stubborn country)? Depending on the result you need, you select the corresponding style (they are all listed in the documentation):
Converting to a time or datetime is similar as converting to a date; you have to select the appropriate style. You can also convert numbers to a datetime (not a date or datetime2 though). Converting 0 gives you the start date, which is 1900-01-01:
The earliest datetime you can get is 1753-01-01. Going back further will result in an error.
The biggest datetime is 9999-12-31. If you use a decimal number, you also change the time portion:
If you want to convert a «date-like» number to a date, such as the integer 20210805, you’ll need to convert it to a string first:
More info can be found in the tip SQL Server function to convert integer date to datetime format, or the tip SQL Convert Date to YYYYMMDD for the other way around.
Next Steps
- The cast and convert documentation has a ton of information about all the different types of conversion using CAST or CONVERT in a SQL database. It has a nice compatibility matrix and lists many of the edge cases.
- Other tips that might interest you:
- SQL CAST Function for Data Type Conversions
- DDate and Time Value Conversions Using SQL Server
- Performance Comparison of the SQL Server PARSE, CAST, CONVERT and TRY_PARSE, TRY_CAST, TRY_CONVERT Functions
- SQL Date FORMAT Function
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.MySQL функция STR_TO_DATE
MySQL функция STR_TO_DATE преобразует строку в дату, определенного формата.
Синтаксис
Синтаксис MySQL функции STR_TO_DATE:
Параметры или аргументы
string — строковое значение, которое нужно форматировать как дату.
format_mask — формат, применяемый к строке. Ниже приведен список параметров для параметра format_mask. Эти параметры могут использоваться во многих комбинациях.
Значение Описание %a Сокращенное название дня недели (Sun до Sat) %b Сокращенное название месяца (Jan до Dec) %c Числовое значение месяца (0 до 12) %D День месяца в виде числового значения, за которым следует суффикс (1st, 2nd, 3rd, . ) %d День месяца в виде числового значения (от 01 до 31) %e День месяца в виде числового значения (от 0 до 31) %f Микросекунды (от 000000 до 999999) %f доступны начиная с MySQL 4.1.1 %H Час (от 00 до 23) %h Час (от 00 до 12) %I Час (от 00 до 12) %i Минуты (от 00 до 59) %j День года (001 — 366) %k Час (от 00 до 23) %l Час (от 1 до 12) %M Название месяца полностью (January до December) %m Название месяца в виде числового значения (от 00 до 12) %p До или после полудня AM или PM %r Время в 12-часовом формате AM или PM (hh:mm:ss AM/PM) %S Секунды (от 00 до 59) %s Секунды (от 00 до 59) %T Время в 24-часовом формате (hh:mm:ss) %U Неделя, где Sunday — первый день недели (от 00 до 53) %u Неделя, где Monday — это первый день недели (от 00 до 53) %V Неделя, где Sunday — первый день недели (от 01 до 53). Доступно начиная с версии MySQL 3.23.8 и используется с %X %v Неделя, где Monday — первый день недели (от 01 до 53). Доступно начиная с версии MySQL 3.23.8 и используется с %X %W Имя дня недели полностью (Sunday до Saturday) %w День недели, где Sunday=0 and Saturday=6 %X Год недели, где Sunday — первый день недели. Доступно начиная с версии MySQL 3.23.8 и используется с% V %x Год недели, где Monday — первый день недели. Доступно начиная с MySQL 3.23.8 и используется с %v %Y Год в виде числового значения из 4 цифр %y Год в виде числового значения из 2 цифр Примечание
- Функция STR_TO_DATE будет возвращать значение datetime , если строка содержит действительные части даты и времени.
- Функция STR_TO_DATE возвращает значение даты, если строка содержит только действительные части даты.
- Функция STR_TO_DATE возвращает значение времени, если строка содержит только действительные части времени.
- Функция STR_TO_DATE вернет значение NULL, если она не сможет извлечь действительные части даты и времени, используя format_mask .
Применение
Функция STR_TO_DATE может использоваться в следующих версиях MySQL:
- MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23
Пример
Рассмотрим примеры MySQL функции STR_TO_DATE, чтобы понять, как использовать функцию STR_TO_DATE в MySQL.
Например: