Home » SQL Server

Always The First Day Of The Month

I have a date parameter (@rptMonth) that is selected by the user from a datepicker calendar. The date must be the first day of the month. No matter what the user selects I'd like to turn that into mm/01/yyyy. So in my query it would be something like WHERE YEAR_MONTH = DATEADD("m",datediff("m","1900-01-01",@RptMonth),"1900-01-01"),"mm/dd/yyyy" but when I try this I get incorrect syntax near ','. Don't know if this will even work. Any help is appreciated

6 Answers Found


Answer 1

Hi, Can u try the link below, it shows how to get first and last day  of the month. let me know if it works.




Answer 2

you can use a second hidden parameter  that takes the date  user selects  and converts it to the first:

=dateserial(year(parameters!rptmonth.value), month(parameters!rptmonth.value), 1)


also while im not sure of your specific requirements but in general, its more intuitive to have a month  and year selector if the day  of month doesnt matter.  Then you can convert that selection to the 1st of the month if thats what you need in your calculations.




Answer 3

Thanks for your response. I need the first day  of the month. So if the user  selects 06/22/2010, I need to turn  that into 06/01/2010.

Answer 4

Try this one.

declare @arbitraryDate datetime; 
set@arbitraryDate= getdate(); 
set@arbitraryDate= dateadd(dd, datediff(dd,0,@arbitraryDate),0)--strip time 
select dateadd(dd,-day(@arbitraryDate)+1,@arbitraryDate)--strip days 

OR select cast(convert(varchar(6),getdate(),112 + '01' as datetime)

 OR if both wont work  then this should work.

(MONTH(@pInputDate) AS VARCHAR(2))+'/01' AS DATETIME)



Answer 5

Thanks - unfortunately that didn't work. The user  select the date  from a datepicker  calendar, so it could be 06/22/2010 and I need to convert that to 06/01/2010 or the user selects  05/12/2009 and I need to convert that to 05/01/2009

Answer 6

My apologies the last one worked - Thank you Learning_SQL


<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure