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

Hi all I want to subtract on

Hi all I want to subtract on

(OP)


Hi all

I want to subtract one day from a dttm field and then return the month and year from the calculated date in short form.
EG: If DTTM 01/2/2016 I want to end up with Jan 16. Any help welcome.

RE: Hi all I want to subtract on

What have you tried? What did not work for you? Answering these questions will allow us to provide better answers for you.

That being said, using two built-in SSRS functions I was able to get the output you are looking for. My final expression in the textbox for the output value is:

CODE

=Format(CDate(Fields!YourFieldNameHere.Value), "MMM yy") 

Sample Input: "12/28/2010 12:42:57 AM"
Sample Output: "Dec 10"

The CDate "ensures" the passed value is converted to a date/time format, just in case it is coming in as a string instead. The Format function takes a string parameter of how you want the formatted text to appear...

Robert "Wizard" Johnson III
U.S. Military Vets MC
CSM, CSPO, MCPD, CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
Senior Database Developer

RE: Hi all I want to subtract on

That provides your formatted output but I realize I missed the requirement to subtract one day from the date. This sample expression includes that calculation as well:

CODE

=Format(DateAdd(DateInterval.Day, -1, CDate(Fields!YourFieldNameHere.Value)), "MMM yy") 

One question not anwered by you is what happens on the first day of a month? Should the output return the previous month? So if given 2/1/16, do you expect Feb 16 or Jan 16? What about 1/1/16? Does that return Jan 16 or Dec 15?

Based on your initial post, the first day of each month will return the month/year for the previous month. Every other day will return the current month. If that is expected, you are good. If you want the first day of each month to also return the current month, then you don't want the DateAdd function in your expression and should use my expression posted in my first response.

Robert "Wizard" Johnson III
U.S. Military Vets MC
CSM, CSPO, MCPD, CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
Senior Database Developer

RE: Hi all I want to subtract on

(OP)
Hi there and thanks for replying. I should have said the date is always the 1st of the month and I want to return the month and year of the last day of the previous month.
I got it with left(DATEPART,MM,[DTTM]-1),3)) +' '+ (DATEPART,YY,[DTTM-1])

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