Wednesday, May 20, 2009

Date Formats in SQL Server

---Yesterday 
select dateadd(d,-1,getdate()) as yesterday

--First Day of Current Week
select dateadd(wk,datediff(wk,0,getdate()),0) as [First Day of Current Week]

--Last Day of Current Week
select dateadd(wk,datediff(wk,0,getdate()),6) as [Last Day of Current Week]

--First Day of Last Week
select dateadd(wk,datediff(wk,7,getdate()),0) as [First Day of Last Week]

--Last Day of Last Week
select dateadd(wk,datediff(wk,7,getdate()),6) as [Last Day of Last Week]

--First Day of Current Month
select dateadd(mm,datediff(mm,0,getdate()),0) as [First Day of Current Month]

--Last Day of Current Month
select dateadd(ms,- 3,dateadd(mm,0,dateadd(mm,datediff(mm,0,getdate())+1,0))) as [Last Day of Current Month]

--First Day of Last Month
select dateadd(mm,-1,dateadd(mm,datediff(mm,0,getdate()),0)) as [First Day of Last Month]

-- First day of next month
SELECT dateadd(m, datediff(m, 0,getdate())+1, 0) AS MonthStart

-- Last day of next month
SELECT dateadd(m,datediff(m, 0, dateadd(m, +2 ,getdate()))+1, -1) AS MonthEnd

--Last Day of Last Month
select dateadd(ms,-3,dateadd(mm,0,dateadd(mm,datediff(mm,0,getdate()),0))) as [Last Day of Last Month]

--First Day of Current Year
select dateadd(yy,datediff(yy,0,getdate()),0) as [First Day of Current Year]

--Last Day of Current Year
select dateadd(ms,-3,dateadd(yy,0,dateadd(yy,datediff(yy,0,getdate())+1,0))) as [Last Day of Current Year]

--First Day of Last Year
select dateadd(yy,-1,dateadd(yy,datediff(yy,0,getdate()),0)) as [First Day of Last Year]

--Last Day of Last Year
select dateadd(ms,-3,dateadd(yy,0,dateadd(yy,datediff(yy,0,getdate()),0))) as [Last Day of Last Year]

select month('09/06/2008')
select year('09/06/2008')
select getutcdate()
select getdate()
select day('09/06/2008')
select datepart(dd,'09/06/2008')
select datename(yyyy,'09/06/2008')
select datediff(dd,'09/06/2008','09/18/2008')
select dateadd(dd,60,'09/06/2008')

No comments:

Post a Comment