SQL EXTRACT scalar function in DB2 for i SQL

SQL EXTRACT scalar function in DB2 for i SQL

EXTRACT

The EXTRACT function returns a portion of a date or timestamp based on its arguments. The result can be null if the passed argument value is null.

Syntax for Extracting date values

EXTRACT(YEAR/MONTH/DAY FROM date/timestamp expression)

Syntax for Extracting time values

EXTRACT(HOUR/MINUTE/SECOND FROM time/timestamp expression)

Example#1: Extract Year from Date

SELECT  EXTRACT(YEAR FROM '2024-03-16') FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(YEAR FROM Current date) FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(YEAR FROM Current_date) FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(YEAR FROM '2024076') FROM sysibm.sysdummy1

      EXTRACT
        2,024

Example#2: Extract Month from Date

SELECT  EXTRACT(MONTH FROM '2024-03-16') FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(MONTH FROM Current date) FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(MONTH FROM Current_date) FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(MONTH FROM '2024076') FROM sysibm.sysdummy1

      EXTRACT
            3

Example#3: Extract Day from Date

SELECT  EXTRACT(DAY FROM '2024-03-16') FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(DAY FROM Current date) FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(DAY FROM Current_date) FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(DAY FROM '2024076') FROM sysibm.sysdummy1

      EXTRACT
           16

Example#4: Extract Year from Timestamp

SELECT  EXTRACT(YEAR FROM '2024-03-16-15.10.40.456465') FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(YEAR FROM Current timestamp) FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(YEAR FROM Current_timestamp) FROM sysibm.sysdummy1

      EXTRACT
        2,024

Example#5: Extract Month from Timestamp

SELECT  EXTRACT(MONTH FROM '2024-03-16-15.10.40.456465') FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(MONTH FROM Current timestamp) FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(MONTH FROM Current_timestamp) FROM sysibm.sysdummy1

      EXTRACT
            3

Example#6: Extract Day from Timestamp

SELECT  EXTRACT(DAY FROM '2024-03-16-15.10.40.456465') FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(DAY FROM Current timestamp) FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(DAY FROM Current_timestamp) FROM sysibm.sysdummy1

      EXTRACT
           16

Example#7: Extract Hour from Time

SELECT  EXTRACT(HOUR FROM '15.10.40') FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(HOUR FROM Current time) FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(HOUR FROM Current_time) FROM sysibm.sysdummy1

      EXTRACT
           15

Example#8: Extract Minute from Time

SELECT  EXTRACT(MINUTE FROM '15.10.40') FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(MINUTE FROM Current time) FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(MINUTE FROM Current_time) FROM sysibm.sysdummy1

      EXTRACT
           10

Example#9: Extract Second from Time

SELECT  EXTRACT(SECOND FROM '15.10.40') FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(SECOND FROM Current time) FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(SECOND FROM Current_time) FROM sysibm.sysdummy1

  EXTRACT
40.000000

Example#10: Extract Hour from Timestamp

SELECT  EXTRACT(HOUR FROM '2024-03-16-15.10.40.456465') FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(HOUR FROM Current timestamp) FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(HOUR FROM Current_timestamp) FROM sysibm.sysdummy1

      EXTRACT
           15

Example#11: Extract Minute from Timestamp

SELECT  EXTRACT(MINUTE FROM '2024-03-16-15.10.40.456465') FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(MINUTE FROM Current timestamp) FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(MINUTE FROM Current_timestamp) FROM sysibm.sysdummy1

      EXTRACT
           10

Example#12 : Extract Second from Timestamp

SELECT  EXTRACT(SECOND FROM '2024-03-16-15.10.40.456465') FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(SECOND FROM Current timestamp) FROM sysibm.sysdummy1

Or

SELECT  EXTRACT(SECOND FROM Current_timestamp) FROM sysibm.sysdummy1

  EXTRACT
40.456465