INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Help with date selction based on Month and Year

Help with date selction based on Month and Year

(OP)
Friends, I need help on below selection critieria in DB2.
Below where clause works fine, but only If I pass my paramter in DATE or DATE TIME format. I wan to give my end user an option to select by Month Year format May2009 instead of 05/23/2009 or so.

Below selection criteria gives me data for last 12 months.

WHERE DATE(ADDED_DT)  >= ((DATE({?Report_Date}) - Day(DATE({?Report_Date})) day)+1 day) - 12 month
AND DATE(ADDED_DT) <= (((DATE({?Report_Date}) - Day(DATE({?Report_Date})) day)+1 day) + 1 month) - 1 day

ANy help on how to get that?
Thanks a ton,
V

RE: Help with date selction based on Month and Year




Hi,

A date is not a month or a month and year.  It is year, month and day.

If you have a month and year, you must use those values to product a date; presumably either the FIRST or LAST day of the given month and year.

Alternatively, convert the date value to month and year to compare with your values.  

Keep in mind that the format May2009 will produce incorrect results for use in cirteria, as Apr2009 colates BEFORE Jan2009.  Rather, use a yyyymmm format.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Help with date selction based on Month and Year

Skip How can we format the date field into yyyymm, any ideas ?

RE: Help with date selction based on Month and Year

CODE

Year(Date)*100+Month(date)
 

CODE

cast(Year(Date)*100+Month(date) as varchar(6))



 

Ties Blom
 
 

RE: Help with date selction based on Month and Year

Blom Many thanks,  one final question how to we again break this paramter in  where condtion.
Year(Date)*100+Month(date) has worked, but getting error for
cast(Year(Date)*100+Month(date) as varchar(6))

Below is where condtion where I want to break
Year(Date)*100+Month(date) accordingly

WHERE DATE(ADDED_DT)  >= ((DATE({?Report_Date}) - Day(DATE({?Report_Date})) day)+1 day) - 12 month
AND DATE(ADDED_DT) <= (((DATE({?Report_Date}) - Day(DATE({?Report_Date})) day)+1 day) + 1 month) - 1 day

 

RE: Help with date selction based on Month and Year

any ideas friends how to split 200908 accordingly in the above formula?

RE: Help with date selction based on Month and Year

How about:

CODE


Year(Added_DT)*100+Month(Added_DT)
>= Year({?Report_Date})*100-100+Month({?Report_Date})
and
Year(Added_DT)*100+Month(Added_DT)
<= Year({?Report_Date})*100-100+Month({?Report_Date})


 

Ties Blom
 
 

RE: Help with date selction based on Month and Year

Great Idea, thanks a TON

RE: Help with date selction based on Month and Year

My example should read:

CODE

Year(Added_DT)*100+Month(Added_DT)
>= Year({?Report_Date})*100-100+Month({?Report_Date})
and
Year(Added_DT)*100+Month(Added_DT)
<= Year({?Report_Date})*100+Month({?Report_Date})

Success!

Ties Blom
 
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close