How to find start and end of the month as Datetime in SQL

While waiting at McDonalds, I decide to make a small post.

Some examples to get the start and the end of the month as datetime field.

Declare @mysearch datetime
Declare @StartDate datetime
Declare @EndDate datetime
set @mysearch = '20100401 00:00:01';
--First day of the month as Datetime field
set @StartDate = dateadd(ms,0,DATEADD(mm, DATEDIFF(m,0,getdate() ), 0));
--Last day of the month as Datetime field
   set @EndDate = dateadd(ms,0,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0)) ;
  if (@mysearch >= @StartDate and @mysearch < @EndDate)
   print 'Between this datetime' else print 'Not Between this datetime'
--In this example,we are using DATEDIFF, DATEADD, and GetDATE() functions.

DATEDIFF ( datepart , startdate , enddate ): You can set the datepart from nanosecond to year. It basically gives you the difference between startdate and enddate.

::To find difference in years:
SELECT DATEDIFF(year, '2003-01-01', '2006-01-01'); --> will give you 3 as a difference. You can specify startdate and enddate in different formats.

::TO find difference in hrs:

SELECT DATEDIFF(HOUR, '2010-04-22 22:59:59.9999999'
, '2010-04-23 00:00:00.0000000');-->this will give you 2 hrs.

Now if you run this::
SELECT DATEDIFF(HOUR, '2010-04-22 23:59:59.9999999'
, '2010-04-23 00:00:00.0000000');-->You will get 1hr difference. If you run for minutes, you will get 1 minute. As you might guess, it will also give 1 for difference in days.


::Referenced Datetime
In our first example we used 0 as our reference datetime field. You will still get the same result even if you set something else. We are simply taking difference and adding over the referenced datetime.

declare @REFERENCE datetime
set @REFERENCE = '20100401'

select dateadd(ms,0,DATEADD(mm, DATEDIFF(m,@REFERENCE,getdate() )+1, @REFERENCE))

If you specify this @REFERENCE as '20100401 10:12:12', you will get time field in your result. Output: 2010-05-01 10:12:12.000

Using same technique we can find many other datetime fields::

::LAST DAY OF THE PREVIOUS YEAR as Datetime
select dateadd(ms, -3, DATEADD(yy, DATEDIFF( year,0,getdate() ), 0))
::FIRST DAY OF this week
select DATEADD(week, DATEDIFF(week,0,getdate() ),0);
::LAST DAY of this week
select dateadd(day,-1,DATEADD(week, DATEDIFF(week,0,getdate() )+1,0));
::Last day of this week within ms for datetime comparison
select dateadd(ms,-3,DATEADD(week, DATEDIFF(week,0,getdate() )+1,0));

::I guess you figured out how to do the other possible cases.