SQL Pocket Guide, 2nd Edition

Dataype Conversion

Some platforms allow for the implicit conversion from one datatype to another. In Oracle, for example:

SELECT * FROM upfall WHERE id = '1';

However, it’s often better to use explicit type conversion so that you know for sure which value is getting converted and how.

Standard CAST Function

Use the CAST function to convert explicitly a value to a new type:

SELECT * FROM upfall u
WHERE u.id = CAST('1' AS INTEGER);

When converting from text to numeric or date types, CAST offers little flexibility in dealing with different input data formats. For example, if the value you are casting is a string, the contents must conform to your database’s default text representation of the target datatype.

Standard EXTRACT Function

Oracle, MySQL, and PostgreSQL support the standard EXTRACT function to retrieve specific elements from a datetime value. In MySQL, for example:

SELECT EXTRACT(DAY FROM CURRENT_DATE);

The result will be a number. Valid elements are SECOND, MINUTE, HOUR, DAY, MONTH, and YEAR.

Oracle supports the following additional elements: TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_ REGION, and TIMEZONE_ABBR. The latter two Oracle elements are exceptions to the rule and return string values.

PostgreSQL also supports additional elements: CENTURY, DECADE, DOW (day of week), DOY (day of year), EPOCH (number of seconds in an interval, or since 1-Jan-1970 for a date), MICROSECONDS, MILLENNIUM, MILLISECONDS, QUARTER, TIMEZONE (offset from UTC, in seconds), TIMEZONE_HOUR (hour part of UTC ...