| View previous topic :: View next topic |
| Author |
Message |
shyamsaravan
Beginner
Joined: 12 May 2010
Posts: 44
Topics: 12
Location: India
|
 Posted: Fri Aug 19, 2011 4:54 am Post subject: How to get the records of current year and plus two year |
 |
|
Hi
My select qurery has to return the values of current year and + 2 year record values
| Code: |
select Cus_id,Cus_name,Cus_Location,Calen_Year from <table name> where Calen_year ---------?
|
i want the result like
| Code: |
Cus_id Cus_name Cus_Location Calen_Year
111 xxxx ggggg 2011
222 yyyy gttttt 2012
333 zzzz eeee 2013
|
Thanks; |
|
| Back to top |
|
 |
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Fri Aug 19, 2011 7:15 am Post subject: |
|
|
Depending on the datatype of CALEN_YEAR
where Calen_year between year(current_date) and year(current_date) + 2
the year function returns a large integer,
so if CALEN_YEAR is an integer, you are set to go.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
shyamsaravan
Beginner
Joined: 12 May 2010
Posts: 44
Topics: 12
Location: India
|
| Posted: Fri Aug 19, 2011 8:17 am Post subject: |
|
|
Thanx for the quick reply..
CALEN_YEAR is a CHAR.Please let me know the query if the Calen_year data type is CHAR
Thanks |
|
| Back to top |
|
|
John_Deal
Beginner
Joined: 04 Sep 2003
Posts: 15
Topics: 1
|
| Posted: Fri Aug 19, 2011 8:23 am Post subject: |
|
|
That query will only work on a DATE type column... 
8)
_________________
Life is a journey; enjoy the trip! |
|
| Back to top |
|
|
Sqlcode
Intermediate
Joined: 15 Dec 2006
Posts: 157
Topics: 38
|
| Posted: Fri Aug 19, 2011 8:45 am Post subject: |
|
|
In this case, would this work?
| Code: | | where Calen_year between char(year(current_date)) and char(year(current_date) + 2); |
Just tested with below query. Not sure if it satisfies OP's requirement.
| Code: | WITH TEST_DATA AS
(
SELECT '2011' AS CALEN_YEAR,'SQLCODE1' AS NAME
FROM SYSIBM.SYSDUMMY1
)
, FINAL_PASS AS
( SELECT CALEN_YEAR,NAME FROM TEST_DATA
WHERE CALEN_YEAR BETWEEN CHAR(YEAR(CURRENT_DATE)) AND CHAR(YEAR(CURRENT_DATE) + 2)
)
SELECT * FROM FINAL_PASS; |
Thanks, |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12369
Topics: 75
Location: San Jose
|
| Posted: Fri Aug 19, 2011 10:48 am Post subject: |
|
|
shyamsaravan,
I am guessing that this is an extension of this question by you.
http://www.mvsforums.com/helpboards/viewtopic.php?t=11838
You say the date is a char formatted date, if so what is the format?
CCYY-MM-DD or CCYYMMDD or DD-MON Name-CCYY?
You need to provide complete details.
Kolusu |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Fri Aug 19, 2011 10:57 am Post subject: |
|
|
| John_Deal wrote: | | That query will only work on a DATE type column... 8) |
No, actually it will only work on an integer type column.
(though some versions of db2 will automatically do the cast if it is decimal)
if the column was date, then the year function would have to be applied to the date column.
was your shock do to knowing that you don't know what you are talking about?
to repeat my prrevious post
the year function returns a large integer datatype, not a date datatype.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
John_Deal
Beginner
Joined: 04 Sep 2003
Posts: 15
Topics: 1
|
| Posted: Mon Aug 22, 2011 8:28 am Post subject: |
|
|
I guess I'm not too old to learn something new... 
8)
_________________
Life is a journey; enjoy the trip! |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Mon Aug 22, 2011 8:51 am Post subject: |
|
|
sorry I jumped. should have been a PM.
dbz
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
John_Deal
Beginner
Joined: 04 Sep 2003
Posts: 15
Topics: 1
|
| Posted: Tue Aug 23, 2011 7:59 am Post subject: |
|
|
In my defense, my DB2 is a few versions behind (haven't used it in about 10 years), and I really shouldn't have commented... You are welcome to call me out any time I say something that is not accurate... I really do appreciate the input... Like I said... Never too old to learn something new... Thanks... 
8)
_________________
Life is a journey; enjoy the trip! |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
