I’m surprised not to be able to find this question here already.
I have a date time var and I want to convert it to a string so that I can append it to another string. I want it in a format that can be converted easily back to a date time.
How can I do this?
(I want the date part and the time part.)
Содержание
- 9 Answers 9
- Convert date to string using CAST() function
- Convert date to string using TO_CHAR() function
- Introduction to CONVERT() and TRY_CONVERT() functions
- Converting a string in ANSI/ISO and US date format to a datetime
9 Answers 9
The following query will get the current datetime and convert into string. with the following format
yyyy-mm-dd hh:mm:ss(24h)
There are many different ways to convert a datetime to a string. Here is one way:
Here is a website that has a list of all of the conversions:
You can use the convert statement in Microsoft SQL Server to convert a date to a string. An example of the syntax used would be:
The above would return the current date and time in a string with the format of YYYY-MM-DD HH:MM:SS in 24 hour clock.
You can change the number at the end of the statement to one of many which will change the returned strings format. A list of these codes can be found on the MSDN in the CAST and CONVERT reference section.
There are 3 different methods depending on what I is my requirement and which version I am using.
Here are the methods..
1) Using Convert
2) Using Cast (SQL Server 2008 and beyond)
3) Using Fixed-length character data type
In addition to the CAST and CONVERT functions in the previous answers, if you are using SQL Server 2012 and above you use the FORMAT function to convert a DATETIME based type to a string.
To convert back, use the opposite PARSE or TRYPARSE functions.
The formatting styles are based on .NET (similar to the string formatting options of the ToString() method) and has the advantage of being culture aware. eg.
Check CAST and CONVERT syntax of t-sql:
This has been answered by a lot of people, but I feel like the simplest solution has been left out.
SQL SERVER (I believe its 2012+) has implicit string equivalents for DATETIME2 as shown here
Look at the section on «Supported string literal formats for datetime2»
To answer the OPs question explicitly:
Note: The first variable ( myVar ) is actually holding the value ‘2019-01-23 12:24:00.0000000’ as well. It just gets formatted to Jan 23 2019 12:24PM due to default formatting set for SQL SERVER that gets called on when you use PRINT . Don’t get tripped up here by that, the actual string in (myVer) = ‘2019-01-23 12:24:00.0000000’
Summary: in this tutorial, you will learn various functions to convert a date to a string in SQL.
Convert date to string using CAST() function
To convert a date to a string, you use the CAST() function as follows:
- The date can be a literal or an expression that evaluates to a DATE value.
- The string can be any character string data type such as VARCHAR or TEXT .
The CAST() function returns a string that represents the date.
The following statement returns the current date and time as a date and as a string:
The following shows the output:
Even though CAST() is a standard-SQL function, not so many database systems support it.
Convert date to string using TO_CHAR() function
The DB2, Oracle, MySQL and PostgreSQL provide a function named TO_CHAR() that has a similar feature to the CAST function. You can use the TO_CHAR() function to format a date as a string.
The following illustrates the syntax of the TO_CHAR() function:
The following example uses the TO_CHAR() function to format the current date using the YYYY-MM-DD format in Oracle:
Summary: in this tutorial, you will learn how to convert a string to a datetime in SQL Server using the CONVERT() and TRY_CONVERT() function.
Introduction to CONVERT() and TRY_CONVERT() functions
SQL Server provides the CONVERT() function that converts a value of one type to another:
Besides the CONVERT() function, you can also use the TRY_CONVERT() function:
The main difference between CONVERT() and TRY_CONVERT() is that in case of conversion fails, the CONVERT() function raises an error while the TRY_CONVERT() function returns NULL.
This example uses the CONVERT() function to convert a string in ANSI date format to a datetime:
Here is the output:
If the conversion fails, the CONVERT() function will raise an error:
The following is the error message:
The TRY_CONVERT() function, on the other hand, returns NULL instead of raising an error if the conversion fails:
Converting a string in ANSI/ISO and US date format to a datetime
Both CONVERT() and TRY_CONVERT() function can recognize ANSI/ISO and US formats with various delimiters by default so you don’t have to add the style parameter.
This example shows how to use the CONVERT() function to convert strings in ISO date format to datetime values:
Note that the CONVERT() function can also convert an ISO date string without delimiters to a date value as shown in the following example:
Источник: