Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to perform date operations in SQL Sever.

Author: Shahzad Latif
Download Source Code : 1389_TransactionWithAccessDatabase.zip

This article shows you how to perform date operations in SQL Server using GetDate(), DateAdd(), and DateDiff() methods.

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)))

 

 By using above statements you can perform much more complex date operations, rather you'll feel that dealing with the dates is just fun you know ;)

 

Article Comments
can you show me the syntax about last and first day of a specified WW ?
means i give the WW and the query returns the last or first day of this week?
i thank you for your help

Posted on 12/26/2006 7:00:39 AM by matan ben-simon

Love it,
Thank you!!!

Posted on 2/22/2007 11:12:53 AM by Pete Mercuri

Its Very Use Full for begineers.Thanks a lot.but i want to know how can we store the date like 12/12/2007.because i used datatime datatype.It stores date with time i want to store only date.what can i do. Can you give me the solutions??

Thanks in Advance

Posted on 12/30/2007 11:06:08 PM by SureshKumar.S

This article is really very good.

Posted on 2/27/2008 5:20:07 AM by arijit

its a very helpfull to get all the date function . thank you.

Posted on 2/27/2008 5:42:38 AM by Subhendu Maji

It is very good

Posted on 8/27/2008 9:05:13 AM by ramesh

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< The Australian Software Company releases new SQL Delta Version 3.1

Disclaimer - Privacy
© 2002-2017 DevASP.net