How to Convert VARCHAR to DATETIME and Customize the Format in SQL Server?

Converting Varchar to Datetime in SQL Server

When working with databases, it often becomes necessary to convert data from one format to another, especially when dealing with dates and times. One common challenge is converting varchar data, which represents text or character strings, into a datetime format.

Suppose you have a varchar value in the format '2011-09-28 18:01:00' and need to convert it to datetime in the format '28-09-2011 18:01:00'. To achieve this in SQL Server, you can use the CONVERT function.

Converting Varchar to Datetime

The syntax for converting varchar to datetime using the CONVERT function is as follows:

CONVERT(datetime, <varchar_value>, format)

In your case, the format code would be 120, which converts the varchar value to a datetime with the specified format.

SELECT CONVERT(datetime, '2011-09-28 18:01:00', 120);

Custom Datetime Format

If you want to convert the datetime to a specific format, such as '28-09-2011 18:01:00', you can use the CONVERT function along with a custom format string.

SELECT CONVERT(VARCHAR(30), @date, 105) + ' ' + SELECT CONVERT(VARCHAR(30), @date, 108);

In this example, the first CONVERT function converts the datetime value to a varchar with the format 'dd-MM-yyyy', and the second CONVERT function converts it to a varchar with the format 'hh:mm:ss'.

By combining the results of these two functions, you obtain the desired custom datetime format.

The above is the detailed content of How to Convert VARCHAR to DATETIME and Customize the Format in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!