how to convert date passed in by user to the first of every month for the year that was passed in in SSRS?

Question

User inputs a date.

Based on this date, I have to get the first day of each month of the year in this date.

Example:

User input: 2/1/2014

From that, I need an expression that will get me -

1/1/2014
3/1/2014
4/1/2014

and so on.

I need the expression for each of the dates (not all together).

Basically if someone can help me figure out how to get one of the dates, I can do the rest.

EDIT - I got it guys. Sorry I wasn't clear enough.

=CDate("1/1/" + Year(Now).ToString)
=CDate("2/1/" + Year(Now).ToString)
=CDate("3/1/" + Year(Now).ToString)

and so on.

This seems like a very strange way to go about this. Can't you just match on the month and day of the inputted year in SQL? Why is this being done in SSRS? — Eric Hauenstein, Aug 25 '15 at 19:12

score 0 · Answer 1 · edited May 23 '17 at 11:52

0

You should be able to do:

SELECT DATEADD(month, DATEDIFF(month, 0, @mydate), 0) AS StartOfMonth

Where @mydate is your user inputted date, to get the first day of the month they passed in.

For more info, see this question.

edited May 23 '17 at 11:52

Community

answered Aug 25 '15 at 19:13

rofls

guys, im trying to do this in SSRS and not SQL... I need a way to modify the date that is passed in so that it gives me the dates I want... again, not all at the same time. – JJ. Aug 25 '15 at 19:20

score 0 · Accepted Answer · answered Aug 25 '15 at 19:26

0

select to_char( hire_date, 'dd-mm-yy') from employees where to_char(hire_date,'dd')='&dd';

if your using sql use this it will help you

answered Aug 25 '15 at 19:26

siva krishna

2 Answers2