How to Convert DateTime to VarChar in SQL Server
Last Updated :
26 Sep, 2024
In SQL Server, date and time values are often stored in the DATETIME
or DATE
data types. However, there are situations where we may want to convert these values into a different format such as VARCHAR
to display the date in a specific format or for further manipulation.
In this article, we will explore how to convert DATETIME
to VARCHAR
in SQL Server by using various approaches. We will also explain how to use built-in functions CONVERT()
and FORMAT()
to format them DATETIME
into a variety of styles.
How to Convert DateTime to VarChar in SQL Server?
In SQL Server, converting DATETIME
values to VARCHAR
is essential for formatting dates for display or further manipulation. Two primary methods are commonly used for this conversion: the CONVERT()
function and the FORMAT()
function.
- Using the
CONVERT()
Function in SQL Server
- Using the
FORMAT()
Function (SQL Server 2012+)
1. Using the CONVERT()
Function in SQL Server
The CONVERT()
function in SQL Server allows the user to convert a DATETIME
value to a string (VARCHAR
) with a specific format. The style code defines how the date will be displayed. For example, 101
displays the date as MM/DD/YYYY
while 103
shows it as DD/MM/YYYY
.
This function takes three arguments:
- The target data type (
VARCHAR
in this case).
- The source
DATETIME
value.
- A style code that specifies the date format.
Syntax:
CONVERT(VARCHAR, datetime_value, style_code)
Example 1: Converting DATETIME
to VARCHAR
(MM/DD/YYYY)
The query uses the CONVERT
function to change the current date (retrieved using GETDATE()
) into a string (VARCHAR
) in the format MM/DD/YYYY
. Style code 101
is specified for this date format.
Query:
SELECT CONVERT(VARCHAR, GETDATE(), 101) AS ConvertedDate;
Output:
ConvertedDateExplanation: Here, we use style code 101 to convert the current date (retrieved by GETDATE()
) into the MM/DD/YYYY
format. The output will display the current date formatted as MM/DD/YYYY
, for example: 09/22/2024
.
Example 2: Converting DATETIME
to VARCHAR
with Time (YYYY-MM-DD HH:MI)
The query converts the current date and time (from GETDATE()
) into a VARCHAR
string using style code 120
which formats the result as YYYY-MM-DD HH:MI
in a 24-hour time format.
Query:
SELECT CONVERT(VARCHAR, GETDATE(), 120) AS ConvertedDateTime;
Output:
ConvertedDateTimeExplanation: In this example, the 120 style code formats the date in YYYY-MM-DD HH:MI
format, including the time portion in a 24-hour format. This format is often used in international systems and ISO standards.
2. Using the FORMAT()
Function (SQL Server 2012+)
The FORMAT()
function is more flexible than CONVERT()
as it allows us to use custom date and time formats. It takes two arguments:
- The value to format (a
DATETIME
value).
- The format string, which specifies how the date should appear.
Syntax:
FORMAT(datetime_value, 'format_string')
Example 1: Converting DATETIME
to Custom VARCHAR
Format (DD-MMM-YYYY)
The query uses the FORMAT
function to convert the current date (GETDATE()
) into a custom string format of DD-MMM-YYYY
. Here, dd
represents the day, MMM
is the abbreviated month name, and yyyy
is the year.
Query:
SELECT FORMAT(GETDATE(), 'dd-MMM-yyyy') AS FormattedDate;
Output:
FormattedDateExplanation: Here, we use the FORMAT()
function to format the date in a custom format of DD-MMM-YYYY, where the month is abbreviated (e.g., Jan, Feb). This format is widely used in reports.
Example 2: Converting DATETIME
to VARCHAR
with Time in 24-Hour Format
This query uses the FORMAT
function to convert the current date and time (GETDATE()
) into a VARCHAR
string in the format yyyy-MM-dd HH:mm:ss
, displaying both the date and time in a 24-hour format with seconds included.
Query:
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss') AS FormattedDateTime;
Output:
FormattedDateTimeExplanation: This example formats the date as YYYY-MM-DD HH:MI in a 24-hour clock format, including the seconds. This format is common in logs and time-sensitive applications.
Conclusion
Converting DATETIME
to VARCHAR
in SQL Server is a common task, especially when we need to display dates in a specific format. The CONVERT()
function offers predefined styles, while the FORMAT()
function allows for custom formats. Depending on our SQL Server version and formatting needs, we can choose the appropriate function.
Similar Reads
SQL Query to Convert VARCHAR to INT
In SQL, converting data types is a common operation, and one of the most frequently required conversions is from VARCHAR to INT. This conversion is necessary when we need to perform mathematical operations or comparisons on numeric values stored as strings. SQL Server provides several methods to ach
4 min read
How to convert DateTime to integer in Python
Python provides a module called DateTime to perform all the operations related to date and time. It has a rich set of functions used to perform almost all the operations that deal with time. It needs to be imported first to use the functions and it comes along with python, so no need to install it s
2 min read
SQL Query to Convert Datetime to Epoch
Converting a datetime value to Epoch time is a common operation in SQL, particularly when working with timestamps in various applications. In this article, We will learn a step-by-step process of creating a SQL database, inserting datetime values and converting those values into Epoch time using SQL
4 min read
How to Convert Timestamp to Datetime in MySQL?
In this article, we are going to learn how to convert Timestamp to Datetime in MySQL.To execute these queries, we need to first add integer data(written in timestamp format) and then use the FROM_UNIXTIME() function to convert it into "Datetime" Data Type. FROM_UNIXTIME(): This function in MySQL ret
2 min read
How to Convert Date to Numeric in R?
In this article, we will discuss how to convert date to numeric in R Programming Language. Method 1: Using as.numeric() This function is used to convert date into numeric Syntax: as.numeric(date) where the date is the input date. Example: C/C++ Code data = as.POSIXct("1/1/2021 1:05:00 AM",
2 min read
How to Convert Datetime to Date in Pandas ?
DateTime is a collection of dates and times in the format of "yyyy-mm-dd HH:MM:SS" where yyyy-mm-dd is referred to as the date and HH:MM:SS is referred to as Time. In this article, we are going to discuss converting DateTime to date in pandas. For that, we will extract the only date from DateTime us
4 min read
How to Convert Numbers to Dates in R?
In this article, we will discuss how to convert Numbers to Dates in R programming language. Method 1: Convert Integer to Date Using as.Date() & as.character() Functions Here we have to consider an integer and then first we have to convert that integer into a character using as.character() functi
2 min read
How to Convert Integer to Datetime in Pandas DataFrame?
Let's discuss how to convert an Integer to Datetime in it. Now to convert Integers to Datetime in Pandas DataFrame. Syntax of pd.to_datetimedf['DataFrame Column'] = pd.to_datetime(df['DataFrame Column'], format=specify your format)Create the DataFrame to Convert Integer to Datetime in Pandas Check d
2 min read
SQL Query to Convert Date to Datetime
In this article, we will look at how to convert Date to Datetime. We can convert the Date into Datetime in two ways. Using CONVERT() function: Convert means to change the form or value of something. The CONVERT() function in the SQL server is used to convert a value of one type to another type.Conve
1 min read
How to Convert Epoch Time to Date in SQL?
To convert Epoch Time to Date in SQL use the DATEADD() function. DATEADD() function in SQL Server is used to add a time or date interval to a specified date and return the modified date. Some features of the SQL DATEADD() function are: This function is used to sum up a time or a date interval to a d
2 min read