locked
Always The First Day Of The Month RRS feed

  • Question

  • 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
    Tuesday, September 21, 2010 7:47 PM

Answers

  • My apologies the last one worked - Thank you Learning_SQL
    • Marked as answer by RudyCat Tuesday, September 21, 2010 9:59 PM
    Tuesday, September 21, 2010 9:58 PM

All replies

  • 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.

    http://blog.sqlauthority.com/2007/05/13/sql-server-query-to-find-first-and-last-day-of-current-month/

    HTH

    Tuesday, September 21, 2010 7:52 PM
  • 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.

     

     

    Tuesday, September 21, 2010 8:22 PM
  • 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.
    Tuesday, September 21, 2010 8:30 PM
  • 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.

    SELECT CAST(CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' +  
                CAST
    (MONTH(@pInputDate) AS VARCHAR(2)) + '/01' AS DATETIME)

    HTH

    • Proposed as answer by SqlRockss Wednesday, September 22, 2010 12:45 PM
    Tuesday, September 21, 2010 8:41 PM
  • 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
    Tuesday, September 21, 2010 9:01 PM
  • My apologies the last one worked - Thank you Learning_SQL
    • Marked as answer by RudyCat Tuesday, September 21, 2010 9:59 PM
    Tuesday, September 21, 2010 9:58 PM