Simplest way to calculate first and last day of month and week for specific date in MS Sql Server

I have come up with following few lines that illustrate a way to find first and last day(date) for specified date.

And then given same thing for week.

Declare @SpecifiedDate DateTime;
Set @SpecifiedDate=GETDATE();
Declare @XStart int;
Set @XStart=0;

SELECT @XStart=DATEDIFF(mm,0,@SpecifiedDate)
SELECT DATEADD(mm,@XStart,0) ‘First Day of Specific Month’
SELECT DATEADD(d,-1,DATEADD(mm,@XStart+1,0)) ‘Last Day of Specific Month’

SELECT DATEADD(wk,DATEDIFF(wk,0,@SpecifiedDate ),0) ‘First Day of Week’
SELECT DATEADD(wk,DATEDIFF(wk,0,@SpecifiedDate ),6) ‘Last Day of Week’

Advertisements

Use of CASE END in ms sql query to get conditional result column.

When we need some conditional output in sql script or query, below syntax will help to achieve.

SELECT Field1,Field2
(CASE
WHEN  exists(select top 1 Some_Field from Some_Table where [Condition])
THEN ‘OutputA’
ELSE ‘OutputB’
END) as ”AliasName”
From Table1

 

Read more,
https://siddharthboraniait.wordpress.com/2012/05/03/display-sql-server-messageusing-infomessage-event-from-sql-server-to-troubleshoot-stored-procedure