When you're working with SQL, you'll need to learn how to format dates properly.
This is because dates are an important aspect of any SQL and other software-related activities. You need to be able to work with dates to add timestamps to entries and keep track of when things happen, for example. Almost everything is dependent on a date.
In this article, I want to show you how to convert a date and datetime to a string in SQL with the CONVERT()
and STR_TO_DATE()
functions.
What We'll Cover
- How to Convert Date to String with the
CONVERT()
Function - How to Convert Date to String with the
STR_TO_DATE()
Function - How to use the
DATE_FORMAT()
to Change the Time Format - Conclusion
How to Convert Date to String with the CONVERT()
Function
The CONVERT()
function expects two arguments:
- the date – has to be a string, or with built-in date getters like
NOW()
orSYSDATE()
- the data type – the type of data you want to convert the date to.
Here's the CONVERT()
function in action:
SELECT CONVERT(NOW(), CHAR);
The query above used the NOW()
function to get the current date and time. The second argument, CHAR
, is the data type the date got converted to.
You can also use SYSDATE()
in its place if you want to:
SELECT CONVERT(SYSDATE(), CHAR);
There are many other functions you can use for working with dates. I wrote about them in this tutorial if you'd like to read more.
Functions are not the only parameter you can use as the first argument of the convert function. You can use a date written as a string, then specify DATE
as the data type you want to convert it to:
SELECT CONVERT("2023-01-10", DATE)
How to Convert Date to String with the STR_TO_DATE()
Function
The STR_TO_DATE()
function is another useful function for converting a date or date time. It accepts two parameters:
-
the date – it has to be a string. For example, '09-01-2023'
-
the format – the format you want the date to get converted to. For example
mm-dd-yyyy
. You specify the format like this%d-%m-%Y
.
Here's how the STR_TO_DATE()
function works:
SELECT STR_TO_DATE('09-01-2023', '%d-%m-%Y')
You can also use a slash (/
) to separate the date and the format:
SELECT STR_TO_DATE('09/01/2023', '%d/%m/%Y')
N.B.: If you don’t use the same separator for the date and format, you'll get null in return.
If you enter the day as the nth day for that date, you have to change the d
in the format to a capital letter:
SELECT STR_TO_DATE('9th-01-2023', '%D-%m-%Y')
And if you enter the month as the abbreviation for that month, you have to change the m
in the format to a capital letter:
SELECT STR_TO_DATE('9th-JAN-2023', '%D-%M-%Y')
Next, we'll look at how you can work with date formats with the DATE_FORMAT()
function.
How to Use the DATE_FORMAT()
to Change the Time Format
If you want the month as the full name of that month, change the m
in the format to a capital letter and use the DATE_FORMAT()
function:
SELECT DATE_FORMAT('2023-01-09', '%d-%M-%y')
If you want the day as the nth number of that day, change the d in the format to a capital letter:
SELECT DATE_FORMAT('2023-01-09', '%D-%M-%y')
And if you want the year in full, change the y to a capital letter:
SELECT DATE_FORMAT('2023-01-09', '%D-%M-%Y')
Conclusion
This article showed you how to convert a date to a string with the CONVERT()
and STR_TO_DATE()
functions. We also looked at how you can change the date format with the DATE_FORMAT()
function.
If you find this article helpful, don’t hesitate to share it with your friends on social media.