How to perform date operations in SQL Sever?

Question

I got this problem with a SQL Server table, got this example table named "sales" that shows the sales per day for each vendor
this is the current table:
+-------+-----------+-----------+--------------+
| id | vendor | sales | date |
+-------+-----------+-----------+--------------+
| 1 | John | 10 | 07-20 |
| 2 | John | 5 | 07-20 |
| 3 | Jeff | 15 | 07-21 |
| 4 | Jeff | 20 | 07-21 |
| 5 | John | 5 | 07-21 |
| 5 | Jeff | 30 | 07-20 |

and I would like to transform it into this table below I need to group by vendor and compare the columns of the sales for each day
+-----------+--------------+-------------------+-----------
| vendor |sales 07/20 | sales 07/21 | Variance |
+-----------+--------------+-------------------+-----------
| John | 15 | 5 | -10 |
| Jeff | 30 | 35 | 5 |

Answer 1

While writing stored procedures in SQL Server, some times we need to perform date operations. For example we need to find the date of the last month or the first day of this week or the last day of the last month. In this article i have tried to give you an interesting list of such statements. With the help of these you can perform very interesting and complex date operations.

Today

select getdate() 

Yesterday

 select dateadd(d,-1,getdate())

First Day of Current Week

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

Last Day of Current Week

select dateadd(wk,datediff(wk,0,getdate()),6)

First Day of Last Week

select dateadd(wk,datediff(wk,7,getdate()),0)

Last Day of Last Week

select dateadd(wk,datediff(wk,7,getdate()),6)

First Day of Current Month

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

Last Day of Current Month

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

First Day of Last Month

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

Last Day of Last Month

select dateadd(ms,-3,dateadd(mm,0,dateadd(mm,datediff(mm,0,getdate()),0)))

First Day of Current Year

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

Last Day of Current Year

select dateadd(ms,-3,dateadd(yy,0,dateadd(yy,datediff(yy,0,getdate())+1,0)))

First Day of Last Year

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

Last Day of Last Year

select dateadd(ms,-3,dateadd(yy,0,dateadd(yy,datediff(yy,0,getdate()),0)))