DB2: First day of month and Last day of month

Hi Guys,

That’s a very simple tip but may be useful sometimes.

In Db2 database how to get the first day of current month for example?

You can do something like this:

select current date + 1 days - day(current date) days
from schema.table_name

Or, how to get the last day of the current month?

Doing something like this:

select current date + 1 month - day(current date + 1 month) days
from schema.table_name

I don’t know if that is the best solution but that’s already save me. If you know a better solution, please let me know.

All the best,

Leo

About these ads

15 comments so far

  1. Gergo on

    Hi Leo!

    Thanks a lot for the post, you saved me a lot of headache ;)

    Cheers

    Gergo

  2. Ayyappa D.N.S on

    Really a great job man keep it up.

    Nice Post

  3. Jens Larsen on

    That was by far the easiest solution to that problem I have seen so far. And it’s so easy to change. For instance:

    — RETURNING THE FIRST DATE OF THE PREVIOUS MONTH
    SELECT CURRENT DATE + 1 DAYS – DAY(CURRENT DATE) DAYS -1 MONTH FROM SYSIBM.SYSDUMMY1

    — RETURNING THE LAST DATE OF THE PREVIOUS MONTH
    SELECT CURRENT DATE – DAY(CURRENT DATE) DAYS FROM SYSIBM.SYSDUMMY1

    Thanks for the post!

  4. Ravi on

    I am trying to set up an automated ‘Month-To-Date’ report where I have to retrieve data from First of current Month till yesterday. The above date ranges (using first day and last day of current month) don’t work on the 1st of a month as I need the dates referring to last month. I can use the second set of date ranges for previous month. But the problem is this report will be auto-scheduled in Business Objects. Is there a way to use the ‘Previous Month’ dates only on 1st of month? Any help is appreciated.
    Thanks – Ravi

    • Leonardo on

      Hi, have you tried to subtract one month? See the query below. I’ve not tested, but give it a try:

      select (current date – 1 month) + 1 days – day(current date) days
      from schema.table_name

  5. Ravi on

    Leonardo, Thanks for the response. This always goes back to the previous month. I am looking for something that works without having to change from month to month. So on 2nd Feb it should retreive only the data for the 1st of Feb and not Jan.
    But you are on the right track using Month function. The following worked for me. It always limits it to the activity for that month as of yesterday:

    AND ((year(DISPATCH_DATE) = Year(Current date))
    AND(Month(DISPATCH_DATE) = Month(Current date-1 DAYS)))

  6. Aniket on

    Found more simple one to find out the last day of the month

    Query :

    SELECT CURRENT DATE – (DAY (CURRENT DATE)) DAYS + 1 MONTH FROM SYSIBM.SYSDUMMY1

    • Leonardo on

      Great! Thanks for sharing that!

      • Austin on

        Aniket
        I dont think your query is correct. If the previous month has 30 days, your query will return the month end on the 30 day. For example, if you run the query today (May 24), you will get May 30 as the Last day of the month, which is incorrect.

  7. Andi on

    Non of the above gives you the right result.

    To first post, 1st day of month. Think you are current 28th of feb. + 1 day gives you 1st of mar – 28 days does not give you 1st of feb.

    Calculating last day of month previous post gives you in dec -days = 30th of nov + 1 month gives you 30th dec, not 31st.

    Think for end-of-month of:
    SELECT CURRENT DATE – (DAY (CURRENT DATE) – 1) DAYS + 1 MONTH – 1 DAY FROM SYSIBM.SYSDUMMY1

    This calculates 1st day in month, adds one month giving 1st day of next month and – 1 day gives you last day of current month.

    • Andi on

      Oh sorry. Calculating 1st day in month is correct, had a thinking error.

  8. Amit on

    Calculating 1st day of month. Below query will help :-

    current date – day (current date -1 days) days

  9. Sam on

    You can as well use the ‘TRUNC’ function to get the first day of a month as follows:

    SELECT TRUNC(CURRENT DATE,’MONTH’) FROM SYSIBM.SYSDUMMY1

    OR

    VALUES TRUNC(CURRENT DATE, ‘MONTH’

    This returns the first day of current month, which in this case is 2014-10-28. You can use the same with any dates and timestamps.

  10. Sam on

    You can as well use the ‘TRUNC’ function to get the first day of a month as follows:

    SELECT TRUNC(CURRENT DATE,’MONTH’) FROM SYSIBM.SYSDUMMY1

    OR

    VALUES TRUNC(CURRENT DATE, ‘MONTH’)

    This returns the first day of current month, which in this case is 2014-10-28. You can use the same with any dates and timestamps.

    • Leonardo on

      Hi Sam,

      Thanks fo the tip!

      Best regards


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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: