4. "CAST and CONVERT: The Dynamic Duo of SQL Data Conversion"

When working with data, converting from one data type to another is a common task. For instance, if you import data from Excel, it might be stored as text by default. However, some columns may contain numeric values or datetime data, and you may need to convert them to their appropriate data types.

In such cases, you can use the CAST or CONVERT function to transform the data.

• CAST is a simple and ANSI-compliant way to change the data type.
• CONVERT offers more control and allows format specifications, particularly in SQL Server. n.

Example

In the employee data table, we need to extract only the date of birth. However, the BIRTHDATE column currently includes both the date and time and also wants to know their firstname, lastname & title.

To solve this issue, we can either use the CONVERT or CAST function to convert the DATETIME to DATE in SQL Server.

Solution:

The general syntax for the CAST function is:

CAST(expression AS data_type);

expression: The value or column name that you want to convert.

data_type: The target data type you want to convert the expression to.

SELECT
	FIRSTNAME,
	LASTNAME,
	TITLE,
	CAST(BIRTHDATE AS DATE) AS BIRTHDATE
FROM 
	EMPLOYEES;        

OR

The general syntax for the CONVERT function is:

CONVERT(data_type(length), expression, style)

data_type (Required): The datatype to convert expression to.

length (Optional): The length of the resulting data type

expression (Required): The value to convert to another data type

style (Optional): The format used to convert between data types, such as a date or string format.

SELECT
	FIRSTNAME,
	LASTNAME,
	TITLE,
	CONVERT(DATE,BIRTHDATE) AS BIRTHDATE
FROM
	EMPLOYEES;        

Note: Both CAST and CONVERT functions are correct for Microsoft SQL Server. However, if you're using a different SQL server like MySQL or PostgreSQL, these functions will not work.

• For MySQL, you can use the DATE() function to convert a DATETIME value to DATE.
• For PostgreSQL, you can use the :: operator to achieve the same result.

Below is an example of the syntax for each:

Mysql

SELECT
	FIRSTNAME,
	LASTNAME,
	TITLE,
	DATE(BIRTHDATE) AS BIRTHDATE
FROM
	EMPLOYEES;        

Postgresql

SELECT
	FIRSTNAME,
	LASTNAME,
	TITLE,
	BIRTHDATE :: DATE AS BIRTHDATE
FROM
	EMPLOYEES;        

Thanks for checking out my newsletter! If you're a fellow SQL learner like me, don't forget to subscribe to stay updated with tips and insights.

#sql #learningjourneyofsql