 |
| SQL FIRST_DAY scalar function in DB2 for i SQL |
FIRST_DAY
The FIRST_DAY function returns the 1st day of the month of the passed argument.
Syntax
FIRST_DAY(expression)
The argument should be an expression that returns either date, timestamp, character string, graphic string data type. The result of the function is timestamp if expression is a timestamp. Otherwise the result is date. Also, the result can be NULL if the argument value is null.
Example#1:
If the argument is a string
SELECT FIRST_DAY('2024-03-16') FROM sysibm.sysdummy1
FIRST_DAY 03/01/24
Or
SELECT FIRST_DAY('2024-03-16-15.10.40.456465') FROM sysibm.sysdummy1
FIRST_DAY 03/01/24
Example#2:
The result can be null if the argument is null.
Create a table having date field and that allows null value to be inserted.
CREATE TABLE QTEMP/DT (DATE1 DATE )
INSERT INTO QTEMP/DT VALUES(null)
Output
DATE1 -
select first_day(date1) from qtemp/dt
FIRST_DAY ( DATE1 )
-
Returns NULL value as date1 field has NULL value in table qtemp/dt.
Example#3:
If the argument is a timestamp.
SELECT FIRST_DAY(current timestamp) FROM sysibm.sysdummy1
Or
SELECT FIRST_DAY(current_timestamp) FROM sysibm.sysdummy1
FIRST_DAY 2024-03-01-05.57.40.557700
Example#4:
Argument value is the character string '2024076', which represents a date in the format yyyynnn, where yyyy is the year, and nnn is the day of the year.
SELECT FIRST_DAY('2024076') FROM sysibm.sysdummy1
FIRST_DAY 03/01/24