SQL Datetime casting into string format
As many other programming languages, SQL - structural query language - contains data types that used to store differents data types in the database system.
One of those data types is the date type, which used to store date and time in the database.
Data from these data types can be converted from one data type into the other, and this process is known as type casting.
In this article, we are going to discuss how you can change date data type into string data type from three database system vendors: MS ACCESS/SQL server, OracleDB, and MYSQL/MARIADB.
Datetime casting to a string format: OracleDB
In oracle we can use the function TO_CHAR(date_object, fmt) to convert date into a string using the specified format, which is passed as the second argument of the function.
Also this function can be used to convert any data type in OracleDB into a string equivalent.
SELECT TO_CHAR(EMP_DOB, "YYYY/MM/DD") FROM employee;
This statement above is going to return the date of birth from the employee table. The return value is a string value representation of the date in the specified format: 2017/06/02.
Also we can convert string date representation into a valid Oracle database date object. This is achieved by using TO_DATE(date_string, fmt). This function takes two arguments, which are the date representation in string format and the format that represent how the string date is formatted.
SELECT TO_DATE('2017/06/02', 'YYYY/MM/DD') FROM employee;
This statement is going to return a valid OracleDB date object that is a representation of the specified date string.
Valid format string that can be used in TO_CHAR and TO_DATE
- YYYY: this will return a four digit year like 2017
- YY: this will return a two digit year like 17
- MONTH: this will return a full month name like JUNE
- MON: this will return a three character month name like JUN
- MM: this will return a two digit month like 06
- D: this will return a number representation of the day of the week
- DAY: this will return the name of the day in a week
- DD: this will return the day of the month in two digits like 02
These can be used in both functions: TO_CHAR and TO_DATE.
Datetime casting to a string format: MS ACCESS/SQL SERVER
In MS ACCESS/SQL SERVER we use these two functions to convert (casting) from one data type into the other.
CAST (numeric AS varchar(length)):
This function is used to convert(casting) a numeric data type into a string equivalent.
CONVERT (varchar(length), numeric):
This function is used to convert(casting) a string data type into a numeric equivalent.
These functions can be used to convert dates from string to date object and vice versa.
Datetime casting to a string format: MYSQL/MARIADB
Another popular SQL database systems are MYSQL and MARIADB, which are open source database systems.
To return a string from a date object in MySQL/MariaDB databases, we need to use the DATE_FORMAT(date_object, fmt) function.
This function takes two arguments. The first argument is the data object and the second argument is the formatting string - used to format the string that is returned from the DATE_FORMAT function.
SELECT DATE_FORMAT(EMP_DOB, "%Y %M %e") FROM employee;
This will return a string representation of the employee date of birth. The string will be in this format: 2017 06 02.
This function has a counterpart that be used for time formatting from the datetime object. TIME_FORMAT(date_object, fmt).
Check all formatting characters that can be used in the DATE_FORMAT function.