Date Display 1

[DebbieC]

I need to create a date that shows the previous month with just the month & year, not the day or time but actually use the month's name instead of the number. I used the following to get the previous month:

=DateSerial(iif( Month(DateTime.Now)=1, Year(DateTime.Now)-1, Year(DateTime.Now)), iif( Month(DateTime.Now)=1, 12, Month(DateTime.Now) - 1), 1)

But it returns:
11/1/2006 12:00:00 AM

And I want it to return:
November 2006

Is this possible in Reporting Services?


Thanks.

 

[Catadmin]

It's possible, but you're going to have to convert your Year(DateTime.Now) and Month(DateTime.Now) to text or character within that string that only returns the first 10 characters (the date and its delimiters).



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[xlbo]

can you not just format this in the report as

mmmm yyyy

?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[DebbieC]

I managed to do it with the following:

=Today().AddMonths(-1).ToString("MMMM") & " " & Year(DateTime.Now)

However when January comes I'm sure it will return the correct previous month of December but it will return 2007 instead of 2006 for the year. I'm hoping the following works. I will find out next month:

=Iif(Month(DateTime.Now)-1 = "12", "December " & Year(DateTime.Now)-1, Today().AddMonths(-1).ToString("MMMM") & " " & Year(DateTime.Now))


Thanks for all of your help.

 

[ablackwe]

Try the following and see if this works:

=Format(DateAdd("m",-1,DateTime.Now),"MMM yyyy")

Hope this helps
Adam


Adam Blackwell
Information Analyst

http://www.workinglinks.co.uk/

 

[Catadmin]

Let us know if your code worked. I'm sure someone else could use the knowledge in the future. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ablackwe]

I've just tried the following code in the expression of a textbox and it returns Nov 2006 which is the previous month

=Format(DateAdd("m",-1,DateTime.Now),"MMM yyyy")

Thanks
Adam

Adam Blackwell
Information Analyst

http://www.workinglinks.co.uk/

 

[DebbieC]

It's definitely a lot shorter and easier than what I had. Hopefully it works in January and returns the correct year. It looks like it will. I will know soon.

I wanted to have more than 3 characters for the month so I just added more M's and it works great.

Thanks!