Use CURRENT_USER or USER to retrieve the current active user of the computer where the DBMS is running.

Example:

     SELECT CURRENT_USER AS 'User';

Result:

                   

> DB2, MySQL, PostgreSQL, SQL Server, and Sybase support CURRENT_USER.

> Access doesn’t support CURRENT_USER. Instead it uses CURRENTUSER.

> DB2 uses SELECT CURRENT_USER FROM SYSIBM.SYSDUMMY1;.

> Oracle doesn’t support CURRENT_USER, but it does support SELECT USER FROM DUAL;.

> CURRENT_USER and USER are synonymous.

Use CURRENT_TIMESTAMP to get the current date and time from the system clock of the computer where the DBMS is running.

Example:

     SELECT CURRENT_TIMESTAMP AS 'Timestamp';

Result:

                   

> DB2, MySQL, PostgreSQL, SQL Server, and Sybase support CURRENT_TIMESTAMP.

> Access doesn’t support CURRENT_TIME. Instead it uses NOW().

> MySQL also uses NOW().

> Oracle doesn’t support CURRENT_TIME, but it does support CURRENT_TIMESTAMP and SYSDATE.

 

Use CURRENT_TIME to get the current time from the system clock of the computer where the DBMS is running.
Example:

     SELECT CURRENT_TIME AS 'Time';

Result:

                   

> DB2, MySQL, PostgreSQL, SQL Server, and Sybase support CURRENT_TIME.

> Access doesn’t support CURRENT_TIME. Instead it uses TIME().

> Oracle doesn’t support CURRENT_TIME, but it does support CURRENT_TIMESTAMP.

Use CURRENT_DATE to get the current date from the system clock of the computer where the DBMS is running.
Example:

     SELECT CURRENT_DATE AS 'Date';

Result:

                   

> DB2, MySQL, Oracle, PostgreSQL, SQL Server, and Sybase support CURRENT_DATE.

> Access doesn’t support CURRENT_DATE. Instead it uses DATE().

> MySQL also uses SYSDATE.

> Oracle also uses SELECT SYSDATE FROM DUAL;.

> SQL Server also uses GETDATE().

 

Here is an example:

     SELECT (CAST(QTY AS CHAR(3)) || ' units sold of ' || INVN_ID)
       FROM SALES_TBL;

Results:

                   

The format for the CAST command is: CAST(expr AS data_type)

The data type being converted is the source data type and the result data type is the target data type.
An error will occur if expr is not compatible with data_type.
Some data may be truncated and lost, such as converting DECIMAL to INTEGER.
Some data may cause an error if too much data is present, such as FLOAT to SMALLINT.
You can convert character data type to any other data type, provided the data has valid literal values.
When character values are converted, leading and trailing spaces are removed.
Some data can not be converted, such as FLOAT to TIMESTAMP.
If you convert DATE to TIMESTAMP, the time portion will become 00:00:00 which is midnight.
If you convert TIME to TIMESTAMP, the date portion will become the current date.

Examples are: 

     CAST(PRICE AS CHAR(12))          DECIMAL(12,2) to CHAR(12)
     CAST(PRICE AS INTEGER)           DECIMAL(12,2) to INTEGER
     CAST(DESCRIPTION AS CHAR(40))    CHAR(25) to CHAR(40)
     CAST(TAX_PCT AS INTEGER)         SMALLINT to INTEGER
     CAST(CARGO_TONS AS DECIMAL(8))   INTEGER to DECIMAL(8)

> Instead of using the CAST() function, Access uses individual functions such as:
CStr(expr) for string, CInt(expr) for integer, CDec(expr) for decimal number,
Space(number) to add spaces to strings, Left(string, length) to truncate strings, etc.
> DB2 uses TO_CHAR() and TO_DATE().
> MySQL uses SIGNED instead of INTEGER, such as CAST(PRICE AS SIGNED).
> MySQL and SQL Server use CONVERT().
> Oracle uses SUBSTR() to truncate a character string and treats an empty string as NULL.
> Oracle and PostgreSQL uses TO_CHAR, TO_DATE, TO_NUMBER, and TO_TIMESTAMP.

CAST is used to convert one data type to another data type. The most common data types are:

Character String Data:
     CHAR(length)
     VARCHAR(length)
     LONG VARCHAR(length)
     NATIONAL CHARACTER
     Character Large OBject (CLOB)
     NCLOB

Boolean Data

Binary Large OBject (BLOB) Data:
     GRAPHIC(length)
     VARGRAPHIC(length)
     LONG VARGRAPHIC(length)

Exact Numeric Data:
     SMALLINT
     INTEGER
     BIGINT
     DECIMAL(total,fraction) or NUMERIC

Approximate Numeric Data:
     FLOAT(precisionbits)
     REAL
     DOUBLE PRECISION

Date/Time/Timestamp Data:
     DATE
     TIME
     TIME WITH TIME ZONE
     TIMESTAMP

Intervals:
     Year-month
     Day-time

The data you are converting must be compatible with the new data type. Here are some possible conversions:

> Any character type to any other type such as date, time, or numeric.
> Any type to any character type.
> Any numeric type to any other numeric type.
> Any numeric type to an interval such as INTERVAL DAY, or INTERVAL MINUTE.
> Any DATE to a TIMESTAMP where the time portion will be zero.
> Any TIME to a TIME with fractional seconds, or a TIMESTAMP where the date is the current date.
> Any TIMESTAMP to a DATE, TIME, or TIMESTAMP with different fractional seconds.
> Any year-month INTERVAL to another year-month INTERVAL with different leading precision.
> Any day-time INTERVAL to another day-time INTERVAL with different leading precision.
> DB2, MySQL, PostgreSQL, and Sybase support CAST.

 

Search CASE allows you to use alternate comparison ranges to determine the results.

          SELECT STORE_NAME,
                 CASE
                      WHEN YTD_QUOTA IS NULL
                           THEN 'Unknown'
                      WHEN YTD_QUOTA < 100000
                           THEN 'less than 100,000'
                      WHEN YTD_QUOTA < 5000000
                           THEN 'between 100,000 and 5,000,000'
                      ELSE 'over 5,000,000'
                 END
                      AS 'Store Quota'
            FROM STORE_TBL
           ORDER BY YTD_QUOTA;

Result:

           

Simple CASE allows you to choose an alternate value instead of using the actual value.

          SELECT CITY,
                 CASE STATE
                      WHEN 'CA'
                           THEN 'CALIFORNIA'
                      WHEN 'FL'
                           THEN 'FLORIDA'
                      WHEN 'LU2'
                           THEN 'LAGRANGE POINT 2'
                      WHEN 'MS1'
                           THEN 'MARS REGION 1'
                      ELSE STATE
                 END
                      AS 'STATE'
            FROM CUSTOMER_TBL
           ORDER BY CITY;

Result:

                     

The CASE function gives you IF-THEN-ELSE capability. It evaluates a list of conditions and returns one value from several possible values. CASE can be used in two ways: simple and search.

Simple CASE compares one value (the input_value) to a list of other values and returns the first match:

                      CASE input_value
                      WHEN match_condition THEN result_value
                      ...
                      ELSE result_value
                      END

Search CASE compares to multiple conditions and returns a result from the first condition that is true:

                      CASE
                      WHEN search_condition THEN result_value
                      ...
                      ELSE result_value
                      END

> For the simple CASE, a match is found when input_value = match_condition.
> For the search CASE, search_condition compares two values with a boolean operator.
> Multiple WHEN conditions are used, but only one ELSE clause is allowed.
> All values must be of the same type or implicitly convertible to the same type.
> Access does not support CASE. Instead it uses SWITCH.
> DB2, MySQL, Oracle, SQL Server, and Sybase support CASE.
> Oracle uses CASE and DECODE.