---
title: "DB2 DATE, PACK, UNPACK, and TRIM Functions"
description: "DB2 SQL TRIM,UNPACK and DATE functions to write SQL query better way."
url: https://srinimf.com/2015/07/08/db2-dateunpack-and-trim-functions/
date: 2015-07-08
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2015/07/pexels-photo-5781287.jpeg?fit=1880%2C1253&ssl=1
categories: ["DB2", "SQL"]
tags: ["Date function", "PACK Function", "SQL Functions", "TRIM Function", "UNPACK Function"]
type: post
lang: en
---

# DB2 DATE, PACK, UNPACK, and TRIM Functions

The [purpose of the](https://srinimf.com/2023/02/27/python-binary-files-purpose-of-the-to_bytes-method/) TRIM function is you can trim a portion of the input string. The purpose of the PACK function is it converts the input string to a variable binary string. The purpose of the UNPACK function is opposite to PACK. The purpose of the DATE function is to convert the input Date to DDMMMYYYY format.

## TRIM Function.

TRIM is a scalar function. [In DB2](https://srinimf.com/2010/04/12/db2-udf/) v11, we will see here how this function works.

Example:

Initial value in **:HELLO1 = HELLO1**

```
SELECT 
TRIM(:HELLO1), 
TRIM (TRAILING FROM :HELLO1) 
FROM SYSIBM.SYSDUMMY1;
```

Result: HELLO1 and HELLO1 – Both are same

In DB2, TRIM is having two arguments -trim constant and string expression.

**Valid first argument values:**

- TRAILING FROM
- LEADING FROM
- L ‘0’
- R ‘ ‘
- B ‘ ‘

**Second argument:**

The second argument is an expression. It can be any string or a host variable. By default without giving the first argument, it will remove spaces from both ends. If the second argument is NULL, we get a NULL value.

****Interview Question**

Since, in any function, the basic rule is it should return a value. So, if the second argument is NULL, then the result will be NULL.

Related[How to Use DIGITS function to get Digits from Decimal Column Value](https://srinimf.com/2017/07/19/db2-digitis-function-example/)

## UNPACK and PACK Functions.

The UNPACK function returns a row of values that are derived from unpacking the input binary string. It is used to unpack a string that was encoded according to the PACK function.

One excellent example on PACK and UNPACK is as follows:

SET
:udf_result =
PACK(CCSID 1208,’Alina’,
DATE(‘1977-08-01’),
DOUBLE(0.5));

Let us assume that, we have create an UDF with the above query, with a name “myfirstudf”.

**[How to](https://srinimf.com/2024/08/03/how-to-check-valid-date-in-pyspark-4-top-methods/) use UNPACK**

```
SELECT 
UNPACK(myfirstudf(C1)).* AS(Name VARCHAR(40) 
CCSID UNICODE,
DOB DATE, Score DOUBLE) 
FROM T1;
```

The result from UNPACK should be valid value. Never we will get null value.

## DATE Function.

Date function always return date from an expression.

DATE(timestamp1) => From the “timestamp1” it returns Date. timestamp1=1988-12-25-17.12.30.000000

Result= 25 DEC 1988

**** Interview Question**

```
DATE(DATECOl1)
```

Value in DATECOL1 Char(07) = ‘1989061’

The result will be **02 MAR 1989.** This is internal representation** **value.

**Note:** Even though the size of the variable is CHAR(07), it considers as valid date, and it gives internal representation value.

**Also Read**

- [DB2 SQL: LTRIM, RTRIM and TRIM best examples](https://srinimf.com/2012/07/04/db2-sql-ltrim-rtrim-and-trim/)DateJuly 4, 2012
- [SQL String Functions 8 Top SQL Queries](https://srinimf.com/2019/04/07/string-functions-useful-for-smart-sql-queries/)DateApril 7, 2019
- [DB2: Zero Matching Rows Then Which Value Function Returns](https://srinimf.com/2015/02/12/db2-aggregate-functionsno-row-found/)DateFebruary 12, 2015

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2015%2F07%2F08%2Fdb2-dateunpack-and-trim-functions%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2015%2F07%2Fpexels-photo-5781287.jpeg%3Ffit%3D1200%252C800%26ssl%3D1&description=DB2%20DATE%2C%20PACK%2C%20UNPACK%2C%20and%20TRIM%20Functions)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2015/07/08/db2-dateunpack-and-trim-functions/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2015/07/08/db2-dateunpack-and-trim-functions/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2015/07/08/db2-dateunpack-and-trim-functions/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2015/07/08/db2-dateunpack-and-trim-functions/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20DB2%20DATE%2C%20PACK%2C%20UNPACK%2C%20and%20TRIM%20Functions&body=https%3A%2F%2Fsrinimf.com%2F2015%2F07%2F08%2Fdb2-dateunpack-and-trim-functions%2F&share=email)
-

### Like this:

Like Loading…