Date Operations

Find first day of the month: • Date handling: declare @mydate datetime set @mydate='2013-06-26 15:12:36:456' select @mydate as source_date, DATEADD(mm,DATEDIFF(mm,0,@mydate),0) as 'month first day' source_date month first day ----------------------- ----------------------- 2013-06-26 15:12:36.457 2013-06-01 00:00:00.000 • String Hanling: declare @mydate datetime set @mydate='2013-06-26 15:12:36:456' select @mydate as source_date, CAST(CAST(YEAR(@mydate) as CHAR(4))+'-'+ CAST(MONTH(@mydate) as CHAR(4))+'-'+'01' as datetime) as 'month_first_day' source_date month_first_day ----------------------- ----------------------- 2013-06-26 15:12:36.457 2013-06-01 00:00:00.000 • Date handling: declare @mydate datetime set @mydate='2013-06-26 15:12:36:456' select @mydate as source_date, convert(varchar,DATEADD(dd,-(day(@mydate)-1),@mydate),101) as month_first_date source_date month_first_date ----------------------- ------------------------------ 2013-06-26 15:12:36.457 06/01/2013 'Last Day of Previous Month' 'Last Day of Current Month' First day of next month