Data conversion changes a value from one data type to another. In SQL, it helps present a DATETIME as a DATE, combine different kinds of values, or make comparisons work. Although some databases convert values automatically, explicit conversion makes the intended type clear. section 15.2.2 section 5.4
Use conversion carefully in these common situations:
- Use CONVERT(value, type) to specify a target type, such as changing PublicationDate from DATETIME to DATE. section 15.2.2
- Use CAST(value AS type) for the same kind of conversion with AS-based syntax. section 15.2.2
- Expect database differences: available target types and supported conversion functions vary among RDBMS products. section 15.2.2
- Avoid converting names to numbers or dates; the result may be useless, produce an error, or return NULL in MySQL. section 15.2.2
- Remember that CONCAT converts numeric and date values to strings, so sorting the concatenated result can produce character-order results instead of numeric order. section 15.1.1
Display publication dates without time
SELECT PublicationDate, CONVERT(PublicationDate, DATE) AS PublicationDateNoTime FROM title; The first column preserves the stored DATETIME value; the second displays only its DATE portion. The equivalent CAST expression is: CAST(PublicationDate AS DATE). To test conversion limits, try converting TitleName to DATE; MySQL returns NULL because the names are not valid dates. section 15.2.2
SELECT
PublicationDate,
CONVERT(PublicationDate, DATE) AS PublicationDateNoTime
FROM title;
Inspect the complete CONVERT query that displays both the original publication timestamp and its date-only value. section 15.2.2
