SQL DATE scalar function in DB2 for i SQL

SQL DATE scalar function in DB2 for i SQL
SQL DATE scalar function in DB2 for i SQL, SQL Date function, scalar function, SQL db2, ibmi
SQL DATE scalar function in DB2 for i SQL

DATE

The DATE function returns a date that is derived from a value.

Syntax

DATE(expression)

The argument should be an expression that returns either date, timestamp, character string, graphic string, or any numeric data type. The return value is DATE. Also, the result can be NULL if the argument value is null.

Example#1:

If the argument is a string, the result is the date that is represented by the string.

SELECT  DATE('2024-03-16') FROM sysibm.sysdummy1
DATE    
03/16/24

Or

SELECT DATE('2024-03-16-15.10.40.456465') FROM sysibm.sysdummy1
DATE    
03/16/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 date(date1) from qtemp/dt
DATE ( DATE1 )
   -          

Returns NULL value as date1 field has NULL value in table qtemp/dt.

Example#3:

If the argument is a timestamp, the result is the date part of the timestamp.

SELECT  DATE(current timestamp) FROM sysibm.sysdummy1

Or

SELECT  DATE(current_timestamp) FROM sysibm.sysdummy1
DATE    
03/16/24

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   DATE('2024076') FROM sysibm.sysdummy1
DATE ( '2024316' )
     03/16/24     

Post a Comment

© AS400 and SQL Tricks. All rights reserved. Developed by Jago Desain