SQL SERVER – Query to Find First and Last Day of Any month

Following queries will run respective to today’s date. It will return Last Day of Previous Month, First Day of Current Month, Today, Last Day of Previous Month and First Day of Next Month respective to current month...

First Day of Current Month

select dateadd(mm,datediff(mm,0,getdate()),0)

Last Day of Current Month

select dateadd(s,-1,dateadd(mm,datediff(mm,0,getdate())+1,0))

First Day of Last Month

select dateadd(mm,datediff(mm,0,getdate())-1,0)

Last Day of Last Month

select dateadd(s,-1,dateadd(mm,datediff(mm,0,getdate()),0))

First Day of Next Month

select dateadd(mm,datediff(mm,0,getdate())+1,0)

Last Day of Next Month

select dateadd(s,-1,dateadd(mm,datediff(mm,0,getdate())+2,0))

Major Date functions in SQL Server are

DateAdd()
DateDiff()
DataPart()
DateName()
Getdate()

 select CONVERT(DATE, dateadd(mm, datediff(mm,0, getdate())+1,-1))

 select CONVERT(DATE, CAST(YEAR(getdate()) AS VARCHAR(4)) + '/' + CAST(MONTH(getdate()) AS VARCHAR(2)) + '/' + '01', 111)  

How to get difference between two columns from two different tables?

Here you can find the differences between two columns from two different tables
Table t1
id price                            
1 12
2 10
3 18
Table t2
id price
1 22
2 6
3 18
o/p:
itemid    priceint1 priceint2 diff
1             12 22        -10
2              10 6          4
3              18 18           0

ABS It is  mathematical function that returns the absolute (positive) value of the specified numeric expression.

select coalesce(t1.id,t2.id) as itemid ,t1.price as priceint1,t2.price as priceint2,
abs(coalesce(t1.price,0)-coalesce(t2.price,0)) as [diff]
 from t1 full join t2
on t1.id=t2.id

 If you want with Negative values remove abs function from the query..

select coalesce(t1.id,t2.id) as itemid ,t1.price as priceint1,t2.price as priceint2,
(coalesce(t1.price,0)-coalesce(t2.price,0)) as [diff]
 from t1 full join t2
on t1.id=t2.id

2nd Way
DECLARE @T1 TABLE (Marks CHAR(2), Price SMALLINT);
DECLARE @T2 TABLE (Marks CHAR(2), Price SMALLINT);

INSERT INTO @T1 VALUES('A1',10);
INSERT INTO @T1 VALUES('A2',14);
INSERT INTO @T1 VALUES('A3',21);
INSERT INTO @T2 VALUES('A1',12);
INSERT INTO @T2 VALUES('A2',10);
INSERT INTO @T2 VALUES('A3',24);

SELECT t1.Marks AS ItemID, t1.Price AS PriceInT1
,ABS(t1.Price - t2.Price) AS Diff, t2.Price AS PriceInT2
FROM @T1 AS t1
INNER JOIN @T2 AS t2
ON t1.Marks = t2.Marks