---
title: "SQL EXTRACT Function: How to Get Month, Day, Year"
description: "SQL DATE and Time comparison popular styles and SQL queries useful to read for your project."
url: https://srinimf.com/2019/01/28/db2-date-sql-queries-to-use-in-your-current-project/
date: 2019-01-28
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2019/01/db2-date-sql.jpg?fit=1200%2C900&ssl=1
categories: ["SQL"]
tags: ["DB2 Tips and Solutions", "SQL Date Functions"]
type: post
lang: en
---

# SQL EXTRACT Function: How to Get Month, Day, Year

Here’re sample SQL queries to extract Month, Year and Day from the Date.

Related[4 DB2 DATE Formats to Read Right now](https://srinimf.com/2015/08/22/db2-date-and-time-formats-for-software-developers/)

## Here’s SQL Query to get System Date

You can get system date using [sysibm.sysdummy1](https://srinimf.com/2014/09/29/db2-timestamp-and-sysdummy/). Those are current date current time and current time zone you can get from sysibm.sysdummy1.

**Extract Date and Time**

You can use extract command to extract date, day, month portion from Timestamp. This extract command you can use in **WHERE or ORDER BY** clause.

```
SELECT ORDER_DATE, ORDER_TOTAL
FROM ORDER_TABLE
WHERE EXTRACT(MONTH FROM ORDER_DATE)=9;
```

```
SELECT ORDER_TOTAL
FROM ORDER_TABLE
WHERE YEAR(ORDER_DATE)=2000;
```

![Manners in Australia](https://i0.wp.com/srinimf.com/wp-content/uploads/2019/01/austalian-etequette.jpg?resize=563%2C294&ssl=1)****Manners in Australia****

## Here’s [how to](https://srinimf.com/2024/10/03/how-to-work-with-date-format-top-mysql-examples/) convert Date to Char

```
TO_CHAR(ORDER_DATE,YY-MM-DD)
This function converts the Order date to Char date.
```

## Here’s how to find last day of month

```
SELECT ORDER_DATE, 
DATE(SUBSTR
(CHAR(ORDER_DATE),1,8) ||
'01' + 1 MONTH - 1 DAY 
AS LAST_DAY_OF_MONTH
FROM ORDER_TABLE;
```

#### DB2 Limitation on Calculating TimeStamp Differences

When you calculate differences of Timestamp, it does not consider leaf year. Always DB2 consider, [30 days](https://srinimf.com/2014/08/16/sql-queries-30-days-challenge-day-1/) per month

#### Differences of DATE and Time

You can find examples to find differences for DATE and TIME for your project,[this is my post on it useful for you to refer](https://srinimf.com/2011/02/06/db2-date-time-timestamp/).

Related Posts

- [How to Extract DATE and TIME from TIMESTAMP: DB2](https://srinimf.com/2023/12/27/how-to-get-date-and-time-from-timestamp-in-db2/)DateDecember 27, 2023
- [NEXT_DAY Vs DAYNAME Top SQL Examples](https://srinimf.com/2020/07/12/next_day-vs-dayname-top-sql-examples/)DateJuly 12, 2020
- [How to Extract Date from Timestamp](https://srinimf.com/2017/01/05/how-to-extract-date-from-timesmp/)DateJanuary 5, 2017

### 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%2F2019%2F01%2F28%2Fdb2-date-sql-queries-to-use-in-your-current-project%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2019%2F01%2Fdb2-date-sql.jpg%3Ffit%3D1200%252C900%26ssl%3D1&description=SQL%20EXTRACT%20Function%3A%20How%20to%20Get%20Month%2C%20Day%2C%20Year)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2019/01/28/db2-date-sql-queries-to-use-in-your-current-project/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2019/01/28/db2-date-sql-queries-to-use-in-your-current-project/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2019/01/28/db2-date-sql-queries-to-use-in-your-current-project/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2019/01/28/db2-date-sql-queries-to-use-in-your-current-project/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20SQL%20EXTRACT%20Function%3A%20How%20to%20Get%20Month%2C%20Day%2C%20Year&body=https%3A%2F%2Fsrinimf.com%2F2019%2F01%2F28%2Fdb2-date-sql-queries-to-use-in-your-current-project%2F&share=email)
-

### Like this:

Like Loading…