SQL - Datatype Functions

SQL - Data Type Conversion and Functions

In SQL, data type functions are essential for manipulating and converting data from one type to another. The most fundamental and widely used functions for this purpose are CAST and CONVERT. Mastering them is crucial for handling data from various sources, formatting output, and performing calculations.

The Standard: CAST() Function

CAST is the ANSI SQL standard function for converting a value from one data type to another. Because it’s part of the standard, it is the most portable and recommended function for type conversion across different database systems like PostgreSQL, SQL Server, MySQL, and Oracle.

Syntax

CAST (expression AS data_type)

• expression: The value or column you want to convert (e.g., ‘2023-12-25’, 123.45, ‘500’).
• data_type: The target SQL data type you want to convert to (e.g., DATE, INT, DECIMAL(10,2)).

Examples of CAST

1. Converting a String to a Number: This is common when numeric data is stored as text.

SELECT CAST('123' AS INT) + 10; -- Result: 133

2. Converting a String to a Date: Essential for working with date-formatted text.

SELECT CAST('2024-01-15' AS DATE); -- Result: A date value representing January 15, 2024

3. Converting a Number to a String: Useful for concatenation.

SELECT 'Order ID: ' || CAST(404 AS VARCHAR(10)); -- Result: 'Order ID: 404' (Note: || is the standard concat operator)

4. Truncating a Decimal: Casting a decimal to an integer truncates the fractional part.

SELECT CAST(99.99 AS INT); -- Result: 99

The Alternative: CONVERT() Function

CONVERT is another widely used function, particularly prominent in SQL Server. While it can perform the same type conversions as CAST, its main advantage is an optional third argument for style-based formatting, especially for dates and times.

Syntax

-- General Conversion
CONVERT (data_type, expression)

-- Style-based Conversion (mainly SQL Server)
CONVERT (data_type, expression, style_code)

Example of CONVERT with Formatting

In SQL Server, you can format a date into various string representations using style codes. For example, to get a date in dd/mm/yyyy format:

-- SQL Server Specific Example
SELECT CONVERT(VARCHAR, GETDATE(), 103); -- Result: '25/12/2023' (if today is Christmas)

Best Practice: Prefer CAST for standard type conversions due to its portability. Use CONVERT when you need its specific formatting capabilities in a database that supports it, like SQL Server.

Other Common Data Type Functions

Beyond conversion, SQL provides a rich set of functions to work with specific data types.

String Functions

• LENGTH() or LEN(): Returns the number of characters in a string.
• UPPER() / LOWER(): Converts a string to uppercase or lowercase.
• SUBSTRING() or SUBSTR(): Extracts a part of a string.
• TRIM(): Removes leading and trailing whitespace.

Numeric Functions

• ROUND(number, precision): Rounds a number to a specified number of decimal places.
• CEILING(number): Rounds a number up to the nearest integer.
• FLOOR(number): Rounds a number down to the nearest integer.
• ABS(number): Returns the absolute (non-negative) value of a number.

Date and Time Functions

• NOW() or GETDATE() or CURRENT_TIMESTAMP: Returns the current database server date and time.
• EXTRACT(part FROM date) or DATE_PART('part', date): Extracts a part (year, month, day, etc.) from a date.

Niche and Vendor-Specific Functions

Some functions provide highly specific information, often tied to a particular database system. These are less commonly used in general application development but can be useful for administrative or diagnostic tasks.

Function	Description & Use Case
SQL_VARIANT_PROPERTY()	SQL Server specific. Returns information about a sql_variant value, a special type that can hold data of various types. Its use is generally discouraged in modern design in favor of explicit types.
IDENT_CURRENT(), IDENT_INCR(), IDENT_SEED()	SQL Server specific. These functions return metadata about IDENTITY columns (auto-incrementing keys): the current identity value, the increment step, and the initial seed value. They are used for inspecting table definitions.
DATALENGTH()	SQL Server specific. Returns the number of bytes used to represent an expression. This differs from LEN(), which returns the number of characters. It can be useful for diagnosing storage issues with variable-length data types like NVARCHAR.