retrieve the last day of the month 1

[supernovae]

HI All..

May i know how to retrieve data from a table, based on a date field, the last the day of the month.
Thanks ofr any replues

 

[MarcLodge]

Hi,
One way to do this is to set the date variable up as the 1st day of the following month and subtract 1 day from it, like:
SELECT DATE('2003-10-01') - 1 DAY
FROM SYSIBM.SYSDUMMY1

Hope this is of help

Marc

 

[wing2x]

If you are using v7, the scalar function LAST_DAY(date-expression) is available. It immediately returns the last day of the month of the date parameter passed to it.

Hope this helps

Wing

 

[supernovae]

HI
Thanks for all the reply
To add , the date field that i am getting is of the following char format: YYYYMMDD
May i know how to proceed?

 

[wing2x]

Try this (if you're using v7):

Select LAST_DAY(DATE(SUBSTR(&YOURDATE,1,4) || '-' || SUBSTR(&YOURDATE,5,2) || '-' || SUBSTR(&YOURDATE,7,2))) FROM SYSIBM.SYSDUMMY1

 

[sathyarams]

I prefer using VALUES to SYSIBM.SYSDUMMY1

LAST_DAY(DATE(SUBSTR(&YOURDATE,1,4) || '-' || SUBSTR&YOURDATE,5,2) || '-' || SUBSTR(&YOURDATE,7,2)))

More DB2 questions answered at

http://www.dbforums.com/f8

 

[wing2x]

If you're not using v7, LAST_DAY is not available, you could go with Marc's suggestion. You could write your query like this:

SELECT
DATE(SUBSTR(&YOURDATE,1,4) || '-' ||
SUBSTR(&YOURDATE,5,2) || '-01')
+ 1 MONTH - 1 DAY

Let me know if this helps

Wing

 

[supernovae]

Thanks for all the replies..
im not using v7 , but from the example codes given , i ve got a rough idea on how to proceed .

my final code looks like this :

LTRIM(RTRIM(CHAR(YEAR(CURRENT DATE - 1 MONTH )))) ||

CASE WHEN MONTH(CURRENT DATE - 1 MONTH ) < 10 THEN '0'|| LTRIM(RTRIM(CHAR(MONTH(CURRENT DATE -1 MONTH )))) ELSE LTRIM(RTRIM(CHAR(MONTH(CURRENT DATE - 1 MONTH )))) end ||

LTRIM(RTRIM(CHAR(DAY(DATE( LTRIM(RTRIM(CHAR(YEAR(CURRENT DATE)))) || '-' || CASE WHEN MONTH(CURRENT DATE) < 10 THEN '0'|| LTRIM(RTRIM(CHAR(MONTH(CURRENT DATE)))) ELSE
LTRIM(RTRIM(CHAR(MONTH(CURRENT DATE)))) end ||'-' || '01') - 1 DAYS ) )))

It's retrieving the previous month last day , here im concatenating the YYYY || MM || DD( last day)

The code is a bit messy , but i wish there is an easier way of retrieving the last day of the month ...
I greatly appreciate and welcome any suggestions to other alternative codes to the above .

Thanks again all...

 

[wing2x]

Wouldn't the following suffice?

DATE(SUBSTR(CHAR(CURRENT DATE, ISO),1,8) || '01') - 1 DAY

retrieves the last day of last month. The function char(datevalue, ISO) ensures that the result is in YYYY-MM-DD format.

 

[MarcLodge]

Supernova,
having read back through your posts, I'm a little confused as to what it is that you require.

Are you saying that you have an 8 CHAR field which contains a date, in the format of YYYYMMDD, and that you want to use this to return a proper DATE field which contains the last day of the month of the value of the field hel in the 8 CHAR field?

Can you also tell what version of DB2 you are using and on what platformn and if you are using a programming language like Cobol or suchlike
Marc

 

[supernovae]

HI Marc

Im retrieving data from SAP R/3, wc is using IBM DB2 as their database. We r using an extracting tool to retrieve the tables from SAP into another application developement tool using SQL .( I hope im making sense here )

Being relatively new to DB2, im not sure of the extensive programming prowess of using SQL to retrieve data from DB2

The date fields in the tables are displayed as 'dd.mm.yyyy'. H/w for extraction purpose , entering the date in the format 'YYYYMMDD' works well.

I have tried using the command SUBSTR(CHAR(CURRENT DATE , ISO))) ,1,8) || '01' . but it didnt work .
Therefore , for extraction purposes, I have to convert the current_date() into char format YYYYMMDD .

I hope the following answers ur qns

 

[Pekka]

Hi,
once upon a time when in our database we had tables with all kinds of date formats, so we made table with all kinds of date formats including the last date of month, yes also leap years. Then we linked that table to our queries.
Example:
Date1 Date3 Date4 DOW Last DLast
03.06.04 06/04/2003 20030603 Wed 30 Mon
04.06.04 06/04/2003 20030604 Thu 30 Mon
.
.
03.07.01 07/01/2003 20030701 Tue 31 Thu

The table contained dates of one century. This was good when you didn't have to worry what kind of date functions your dbase sql contained. Yes, we didn't manually entered the table but it was produced by the program we had.

 

[MarcLodge]

Hi again,
So.... correct me if I'm wrong.... you are passing a date in the format YYYYMMDD to the extraction tool which is going away to SAP and retrieving data based upon the last day of the month of the date given to the extraction tool. The date on the DB2 tables used by SAP is a proper DB2 DATE field.

Come back to me and let me know if I've got this correct, because from the results the results you are getting, this does not appear to be the case.

Marc