I was writing few Tsql queries for work where I came across the situation while I had to calculate the first and last day of the current month. So, I looked up some Tsql functions to achieve this.
First Day of the Month:
Firstly, lets find out how many days we are into the current month:
DAY(GETDATE()) -- returns 21 as today is 10/21/2010. So if we were to get the first date of the current month we need to add -20 days to todays date. Notice if we add -21 days it will give us the last date of previous month. 9/30/2010 instead of 10/1/2010. So we get:
DATEADD(d,-DAY(GETDATE()) + 1, GETDATE())
If u need to format and get rid of the time portion then just do convert (us format date 101):
CONVERT(varchar(10),
DATEADD(d,-DAY(GETDATE()) + 1, GETDATE()), 101)
Last Day of the Month:
Using the same methodology I came up with this procedure to calculate last day of the month.
Firstly, lets a add a month to current date:
DATEADD(m, 1, GETDATE()) which should give us the same day of the next month (11/21/2010)
Now find out how many days we are into the month on next month:
Day(DATEADD(m, 1, GETDATE()) ) which gives 21.
Now, if we just subtract 21 from next months date it will give us the last day of this month:
DATEADD(d, [-21], [what we got after adding 1m to today's date]
=>DATEADD(d, -Day(DATEADD(m, 1, GETDATE())), DATEADD(m, 1, GETDATE()))
to get rid off the time you could do:
CONVERT(varchar(10), DATEADD(d, -Day(DATEADD(m, 1, GETDATE())), DATEADD(m, 1, GETDATE())), 101)
I know this was too big until I found out about zero date in SQL. If you pass 0 as date in SQL server as starting date, it gives you the beginning of the time ( I meant the beginning of SQL server not universe..LOL). So if I were to call the following:
DATEDIFF(m, 0, GETDATE())
it will return 1329 today(10/21/2010). Notice I passed a 0 instead of a date in the second parameter. So, we can rewrite the above quires as:
First Day of the month:
DATEADD(d,-DAY(GETDATE()) + 1, GETDATE())
or,
DATEADD(m,DATEDIFF(mm, 0, getdate()), 0)
Last Day of the month:
DATEADD(d, -Day(DATEADD(m, 1, GETDATE())), DATEADD(m, 1, GETDATE()))
or,
DATEADD(s, -2, DATEADD(m,DATEDIFF(mm, 0, getdate()) + 1, 0))
