Systems Engineering and RDBMS

Extract Day, Month or Year from a Date Field

Posted by decipherinfosys on May 21, 2008

One of the developers at a client site was writing up a report for the yearly sales. Invoice data is stored as per date when the invoice was issued. He needed to find out a way to extract year from the date field and sum up the sales amount on yearly basis. There are many ways of doing it … in this post, we will show one of the most common and easy ways of achieving this.

Oracle uses an EXTRACT(datetime) function to extract the value of a specified datetime field from a datetime expression. Function is very handy to manipulate specific datetime field value like day, month or year. Following values can be extracted from the column having date datatype. Apart from values mentioned below, timezone related values like timezone_region, timezone_hour and timezone_minute can also be obtained using EXTRACT function if underlying datatype is TIMESTAMP WITH TIME ZONE.

* Day, Month, Year, Hour, Minute, Second

Create following table and populate it with the data. If you already have table with the same name, change all the occurrences of the table name with some other name.

CREATE TABLE Invoice
(

INVOICE_NUMBER      NUMBER(9) NOT NULL,
INVOICE_DATE          DATE NOT NULL,
CLIENT_ID                NUMBER(9)     NOT NULL,
INVOICE_AMT           NUMBER(9,2) DEFAULT 0 NOT NULL,
PAID_FLAG               NUMBER(1) DEFAULT 0 NOT NULL, — 0 Not paid/ 1 paid
CONSTRAINT PK_INVOICE PRIMARY KEY(INVOICE_NUMBER)
)
— TABLESPACE Clause
/

CREATE SEQUENCE INVOICE_SEQ
START WITH 1
CACHE 100
/

INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate-5,101,1100.00);

INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate-10,102,1100.00);

INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate-20,103,1100.00);

INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate-40,101,1100.00);

INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate-38,101,1500.00);

INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate-60,102,1100.00);

INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate-65,103,1100.00);

Run following SQL from SQL*Plus prompt. It Extracts Year from the Invoice_Date and group it by year to see yearly sales.

SQL> SELECT EXTRACT(Year FROM Invoice_date) as Year,
SUM(Invoice_Amt) as Sales_Amt
FROM Invoice
GROUP BY EXTRACT(Year FROM Invoice_Date);

YEAR  SALES_AMT
—- ———-
2006       4800
2007       3300

MS SQL Server has datepart functions. Using datepart we can get following datetime fields.

*  Day , Dayofyear, Week , Weekday, Month, Quarter, Year
* Hour, Minute, Second, Millisecond

It also has DAY, MONTH and YEAR functions which are equivalent to datepart(dd,date), datepart(mm,date) and datepart(yy,date) respectively. Let us create the table and see how we can do it.

CREATE TABLE dbo.Invoice
(
INVOICE_NUMBER      INT IDENTITY(1,1) NOT NULL,
INVOICE_DATE        DATETIME NOT NULL,
CLIENT_ID           INT    NOT NULL,
INVOICE_AMT         NUMERIC(9,2) DEFAULT 0 NOT NULL,
PAID_FLAG           TINYINT DEFAULT 0 NOT NULL, — 0 Not paid/ 1 paid
CONSTRAINT PK_INVOICE PRIMARY KEY(INVOICE_NUMBER)
)
— FILEGROUP Clause
GO

INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(getDate()-5,101,1100.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(getDate()-10,102,1100.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(getDate()-20,103,1100.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(getDate()-40,101,1100.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(getDate()-38,101,1500.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(getDate()-60,102,1100.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(getDate()-65,103,1100.00);

We can use either Year function or Datepart syntax to get the final results. Both SQLs are shown under. You can either run it from Query Analyzer or Management studio.

SELECT YEAR(Invoice_date) as Year, SUM(Invoice_Amt) as Sales_Amt
FROM Invoice
GROUP BY Year(Invoice_Date)
GO
OR
SELECT DATEPART(YY,Invoice_date) as Year, SUM(Invoice_Amt) as Sales_Amt
FROM Invoice
GROUP BY DATEPART(YY,Invoice_Date)
GO

DB2 LUW has date and timestamp datatype for storing dates. Given a date, time or timestamp value, we can extract following datetime fields.

* Day,  Month, Year (date and timestamp)
*  Hour, Minute, Second, MicroSecond (time and timestamp)

Apart from above functions, it also returns week, quarter, dayofweek  and dayofyear given a date or timestamp.

CREATE TABLE Invoice
(
INVOICE_NUMBER      INTEGER NOT NULL GENERATED BY DEFAULT AS
IDENTITY (START WITH +1, INCREMENT BY +1, CACHE 1000),
INVOICE_DATE        TIMESTAMP NOT NULL,
CLIENT_ID           INTEGER       NOT NULL,
INVOICE_AMT         DECIMAL(9,2) DEFAULT 0 NOT NULL,
PAID_FLAG           SMALLINT DEFAULT 0 NOT NULL,
CONSTRAINT PK_INVOICE PRIMARY KEY(INVOICE_NUMBER)
);

INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(current timestamp – 5 days,101,1100.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(current timestamp – 10 days,102,1100.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(current timestamp – 20 days,103,1100.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(current timestamp – 40 days,101,1100.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(current timestamp – 38 days,101,1500.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(current timestamp – 60 days,102,1100.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(current timestamp – 65 days,103,1100.00);

Following is the SQL to obtain yearly sales in DB2. You can run it from Command Editor.

SELECT YEAR(Invoice_date) as Year, SUM(Invoice_Amt) as Sales_Amt
FROM Invoice
GROUP BY Year(Invoice_Date);

3 Responses to “Extract Day, Month or Year from a Date Field”

  1. Anonymous said

    Using SYSDATE, I want to determine the value of SYSDATE + 1 month, and only review MMYYYY result. Then I need to make this a character string, to put into a file name variable to search for the filename such as

    xxxx_MMYYYY_xxx.txt

    • There is a datetime function ADD_MONTHS which we can use in this case. It takes two arguments. Date and integer. It always returns date. For SYSDATE+1 MONTH command will be

      SELECT TO_CHAR(ADD_MONTHS(SYSDATE,1),’MMYYYY’) FROM DUAL;
      ‘042009’
      One thing we have to remember is that, if date is last date of the month then outcome will be last day of the resulting month. So in case if, we take last day of February (i.e.02/28/2009), ADD_MONTHS will display 31st of March.

      SELECT ADD_MONTHS(TO_DATE(’02/28/2009′,’MM/DD/YYYY’),1) FROM DUAL;

      ADD_MONTH
      ——————–
      31-MAR-09

      For more on ADD_MONTHS function, please refer to Oracle SQLReference Manual.

  2. Zulma said

    Excellent post. I definitely appreciate this
    site. Continue the good work!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: