SQL question

John Lendman

SQL question
SQL question from one of my colleagues



Is there a function that I can use in SQL to get the last day of the
month, both day and date for a given date?



John C. Lendman

IBM Certified Solutions Expert

IBM Certified Database Administrator

IBM Certified Specialist

Office 561-357-7523

Cell 561-644-5708

email: [login to unmask email]

P Think Green - don't print this email unless you really need to!

Public Beach County Schools - Rated "A" by the Florida Department of
Education 2005 - 2008

www.palmbeachschools.org < http://www.palmbeachschools.org/ >

The School District of Palm Beach County is an Equal Education
Opportunity Provider and Employer







----------------------------------------- Under Florida law, e-mail
addresses are public records. If you do not want your e-mail
address released in response to a public records request, do not
send electronic mail to this entity. Instead, contact this office
by phone or in writing.


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

John Miller

Re: SQL question
(in response to John Lendman)
John,



If you are on zOS the LAST_DAY function will work.

SELECT LAST_DAY(CURRENT DATE) FROM SYSIBM.SYSDUMMY1



If you are not on zOS then here's a UDF I wrote which duplicates the
behavior



CREATE FUNCTION LAST_DAY (day DATE)

RETURNS DATE

LANGUAGE SQL

CONTAINS SQL

RETURN

CASE WHEN day IS NULL THEN NULL

WHEN DAY(day) > 28 THEN

(day + 5 days) - DAY(day + 5 DAYS) DAYS

ELSE

((day + 1 MONTH) - DAY(day) DAYS)

END;



These two functions will return a DATE. If you want the Day then use
the DAY function on them



SELECT DAY(LAST_DAY(CURRENT DATE)) FROM SYSIBM.SYSDUMMY1





Thanks,

John Miller



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of John Lendman
Sent: Friday, January 30, 2009 8:34 AM
To: [login to unmask email]
Subject: [DB2-L] SQL question



SQL question from one of my colleagues



Is there a function that I can use in SQL to get the last day of the
month, both day and date for a given date?



John C. Lendman

IBM Certified Solutions Expert

IBM Certified Database Administrator

IBM Certified Specialist

Office 561-357-7523

Cell 561-644-5708

email: [login to unmask email]

P Think Green - don't print this email unless you really need to!

Public Beach County Schools - Rated "A" by the Florida Department of
Education 2005 - 2008

www.palmbeachschools.org < http://www.palmbeachschools.org/ >

The School District of Palm Beach County is an Equal Education
Opportunity Provider and Employer





----------------------------------------- Under Florida law, e-mail
addresses are public records. If you do not want your e-mail address
released in response to a public records request, do not send electronic
mail to this entity. Instead, contact this office by phone or in
writing.

________________________________


IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia
< http://idug.org/lsAU >

IDUG.org < http://www.idug.org > was recently updated requiring members
to use a new password. You should have gotten an e-mail with the
temporary password assigned to your account. Please log in and update
your member profile. If you are not already an IDUG.org member, please
register here. < http://www.idug.org/component/juser/register.html >



The information transmitted is intended only for the addressee and may contain confidential, proprietary and/or privileged material. Any unauthorized review, distribution or other use of or the taking of any action in reliance upon this information is prohibited. If you receive this in error, please contact the sender and delete or destroy this message and any copies.


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Philip Sevetson

Re: SQL question
(in response to John Miller)
John, I can't speak for LUW functions but DB2 z/OS has a function
LAST_DAY(date_string) which returns the last day of the month. From the
manual, the following text:

*****

Example 1: Set the host variable END_OF_MONTH with the last day of the
current month.

SET :END_OF_MONTH = LAST_DAY(CURRENT_DATE);

The host variable END_OF_MONTH is set with the value representing the
end of the current month. If the current day is 2000-02-10, END_OF_MONTH
is set to 2000-02-29.

Example 2: Set the host variable END_OF_MONTH with the last day of the
month in EUR format for the given date.

SET :END_OF_MONTH = CHAR(LAST_DAY('1965-07-07'), EUR);

The host variable END_OF_MONTH is set with the value '31.07.1965'.

*****



Now, for day of the week, you would need to do this:



---------+---------+---------+---------+---------+-------

SELECT

CURRENT_DATE "TODAY"

,LAST_DAY(CURRENT_DATE) AS "DT_OF_LAST_DAY"

,DAYOFWEEK(LAST_DAY(CURRENT_DATE)) "LAST_DAY_D_O_W_NUM"

,CASE

DAYOFWEEK(LAST_DAY(CURRENT_DATE))

WHEN 1 THEN 'SUNDAY'

WHEN 2 THEN 'MONDAY'

WHEN 3 THEN 'TUESDAY'

WHEN 4 THEN 'WEDNESDAY'

WHEN 5 THEN 'THURSDAY'

WHEN 6 THEN 'FRIDAY'

WHEN 7 THEN 'SATURDAY'

ELSE 'ERROR'

END "LAST_DAY"

FROM SYSIBM.SYSDUMMY1;

---------+---------+---------+---------+---------+-------

TODAY DT_OF_LAST_DAY LAST_DAY_D_O_W_NUM LAST_DAY

---------+---------+---------+---------+---------+-------

2009-01-30 2009-01-31 7 SATURDAY



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of John Lendman
Sent: Friday, January 30, 2009 10:34 AM
To: [login to unmask email]
Subject: [DB2-L] SQL question



SQL question from one of my colleagues



Is there a function that I can use in SQL to get the last day of the
month, both day and date for a given date?



John C. Lendman

IBM Certified Solutions Expert

IBM Certified Database Administrator

IBM Certified Specialist

Office 561-357-7523

Cell 561-644-5708

email: [login to unmask email]

P Think Green - don't print this email unless you really need to!

Public Beach County Schools - Rated "A" by the Florida Department of
Education 2005 - 2008

www.palmbeachschools.org < http://www.palmbeachschools.org/ >

The School District of Palm Beach County is an Equal Education
Opportunity Provider and Employer





----------------------------------------- Under Florida law, e-mail
addresses are public records. If you do not want your e-mail address
released in response to a public records request, do not send electronic
mail to this entity. Instead, contact this office by phone or in
writing.

________________________________


IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia
< http://idug.org/lsAU >

IDUG.org < http://www.idug.org > was recently updated requiring members
to use a new password. You should have gotten an e-mail with the
temporary password assigned to your account. Please log in and update
your member profile. If you are not already an IDUG.org member, please
register here. < http://www.idug.org/component/juser/register.html >



=========
Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the author and do not necessarily represent the opinions of the agency or the City.
=========




______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Mike Hutchinson

Re: SQL question
(in response to Philip Sevetson)
Why not just use an expression "- 1 day" from the first day of the
subsequent month?

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of John Lendman
Sent: Friday, January 30, 2009 10:34 AM
To: [login to unmask email]
Subject: [DB2-L] SQL question



SQL question from one of my colleagues



Is there a function that I can use in SQL to get the last day of the
month, both day and date for a given date?



John C. Lendman

IBM Certified Solutions Expert

IBM Certified Database Administrator

IBM Certified Specialist

Office 561-357-7523

Cell 561-644-5708

email: [login to unmask email]

P Think Green - don't print this email unless you really need to!

Public Beach County Schools - Rated "A" by the Florida Department of
Education 2005 - 2008

www.palmbeachschools.org < http://www.palmbeachschools.org/ >

The School District of Palm Beach County is an Equal Education
Opportunity Provider and Employer





----------------------------------------- Under Florida law, e-mail
addresses are public records. If you do not want your e-mail address
released in response to a public records request, do not send electronic
mail to this entity. Instead, contact this office by phone or in
writing.

________________________________


IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia
< http://idug.org/lsAU >

IDUG.org < http://www.idug.org > was recently updated requiring members
to use a new password. You should have gotten an e-mail with the
temporary password assigned to your account. Please log in and update
your member profile. If you are not already an IDUG.org member, please
register here. < http://www.idug.org/component/juser/register.html >



______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Martin Kenney

Re: SQL question
(in response to Mike Hutchinson)
The following should give you what you want:

SELECT LAST_DAY(CURRENT DATE) AS LAST_DATE_OF_MONTH
,CASE(DAYOFWEEK(LAST_DAY(CURRENT DATE)))
WHEN 1 THEN 'SUN'
WHEN 2 THEN 'MON'
WHEN 3 THEN 'TUE'
WHEN 4 THEN 'WED'
WHEN 5 THEN 'THU'
WHEN 6 THEN 'FRI'
WHEN 7 THEN 'SAT'
END AS LAST_DAY_OF_MONTH
FROM SYSIBM.SYSDUMMY1

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of John Lendman
Sent: Friday, January 30, 2009 10:34 AM
To: [login to unmask email]
Subject: [DB2-L] SQL question

SQL question from one of my colleagues

Is there a function that I can use in SQL to get the last day of the
month, both day and date for a given date?

John C. Lendman
IBM Certified Solutions Expert
IBM Certified Database Administrator
IBM Certified Specialist
Office 561-357-7523
Cell 561-644-5708
email: [login to unmask email]
P Think Green - don't print this email unless you really need to!
Public Beach County Schools - Rated "A" by the Florida Department of
Education 2005 - 2008
www.palmbeachschools.org < http://www.palmbeachschools.org/ >
The School District of Palm Beach County is an Equal Education
Opportunity Provider and Employer


----------------------------------------- Under Florida law, e-mail
addresses are public records. If you do not want your e-mail address
released in response to a public records request, do not send electronic
mail to this entity. Instead, contact this office by phone or in
writing.
________________________________

IDUG 2009 - Australasia * 18-20 March * Melbourne, Australia
< http://idug.org/lsAU >
IDUG.org < http://www.idug.org > was recently updated requiring members
to use a new password. You should have gotten an e-mail with the
temporary password assigned to your account. Please log in and update
your member profile. If you are not already an IDUG.org member, please
register here. < http://www.idug.org/component/juser/register.html >


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html