Originally Posted by
RayMilhon
I have a query I'm working on that I need to create a function that has 1 parameter which will be an integer from 1 to 12. The function needs to return the first of the month
Sample:
firstofthemonth(2) should return 01/01/2013
Firstofthemonth(4) should return 11/01/2012
I know I can do it with Cast(str(month(dateadd(m,-2,getdate()))) + '01' + str(year(dateadd(m,-2,getdate()))) as date) But in this particular query I'm going to have to do that a number of times
1 in the Select, twice in the where and 1 in the group by and I have to union for the last 12 months So it's 48 times and I'm not that good of a typist. I was thinking if I had something similar to
Function firstofthemonth(byref m as int) as date
d1 = dateadd("m",-m,Now())
firstofthemonth = format(month(d1) & "/01/" & year(d1),"mm/dd/yyyy")
End function
However that syntax is good for Access but I have to do this in SQL Server and I'm not sure of the syntax any help is appreciated.