SQL Data Types: Characters, Numeric, and Date-Time Types

Data Types and Operators

Characters

Characters are stored in SQL using the CHAR and VARCHAR data types. CHAR is fixed-length, while VARCHAR is variable-length.

  • CHAR:

    • Defined by the CHAR(length) syntax.
    • Fixed-length storage, with the specified length enforced.
    • Example: CHAR(10) stores a string of exactly 10 characters.
    • Use cases: Fixed-length fields like employee IDs or codes.

  • VARCHAR:

    • Defined by the VARCHAR(length) syntax.
    • Variable-length storage, with the maximum length specified.
    • Example: VARCHAR(50) stores a string up to 50 characters.
    • Use cases: Flexible fields like names or descriptions.

Operators like CAST and CONVERT can convert character data types to numeric or date-time types.

Numeric

Numeric data types store numbers, including integers and floating-point values.

  • INT:

    • 4-byte integer range: -2,147,483,648 to 2,147,483,647.
    • Example: INT is used for storing whole numbers in tables.

  • FLOAT:

    • 4 or 8-byte floating-point values.
    • Example: FLOAT(53) provides double-precision accuracy.

  • DECIMAL:

    • Precise numeric values with a specified scale and precision.
    • Example: DECIMAL(10,2) stores up to 10 digits, with 2 decimal places.

Operators like ROUND and CAST can manipulate numeric data.

Date-Time

Date-time data types store dates and times, with DATETIME and TIMESTAMP being the most common.

  • DATETIME:

    • 8-byte value, ranging from 1-8 AM to 12-11 PM.
    • Example: DATETIME is used in tables tracking events or logs.

  • TIMESTAMP:

    • Same as DATETIME, but often used in databases with different syntax.
    • Example: TIMESTAMP is used in SQL Server for time-based queries.

Operators like CURRENT_TIMESTAMP and GETDATE can retrieve the current date and time.

Operators

SQL operators interact with data types to perform operations.

  • CAST:

    • Converts data from one type to another.
    • Example: CAST('123' AS INT) converts a string to an integer.

  • CONVERT:

    • Provides more control over data type conversion.
    • Example: CONVERT(INT, 'abc') returns an error if the input is not a number.

  • Comparison Operators:

    • Compare values of the same data type.
    • Example: >, <, =.

Data types and operators are critical for ensuring accurate data storage, retrieval, and manipulation in SQL queries.