top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How to perform date operations in SQL Sever?

0 votes
578 views
How to perform date operations in SQL Sever?
posted Mar 24, 2016 by Sathyasree

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

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)))
answer Mar 24, 2016 by Shivaranjini
Similar Questions
0 votes

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 |

...