Common date manipulation functions for SQL Server

SQL server date manipulation functions

The common data functions used in Business Objects objects as date conditions for SQL server data base:

Current Date: 

  convert(char(10), getdate(), 111)

Yesterday:

  DATEADD(D,-1,GETDATE())

Last week:

  convert(char(10), dateadd(day, -7, getdate()), 111)

Last 2 week:

  convert(char(10), dateadd(day, -14, getdate()), 111)

Last Month Start:

  convert(char(10), dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)

Last Month End:

  convert(char(10), dateadd(dd, -(day(getdate() )), getdate() ), 111)

Quarter To Date Start Date:

  case month(getdate() )
when 1 then
convert(char(10), dateadd(mm, -3, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 2 then
convert(char(10), dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 3 then
convert(char(10), dateadd(mm, -2, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 4 then
convert(char(10), dateadd(mm, -3, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 5 then
convert(char(10), dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 6 then
convert(char(10), dateadd(mm, -2, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 7 then
convert(char(10), dateadd(mm, -3, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 8 then
convert(char(10), dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 9 then
convert(char(10), dateadd(mm, -2, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 10 then
convert(char(10), dateadd(mm, -3, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 11 then
convert(char(10), dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 12 then
convert(char(10), dateadd(mm, -2, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
end

First of Current  Month:

  convert(char(10), dateadd(dd, -(day(getdate() ))+1, getdate() ), 111)

Current Year:

  case month(getdate())
when 1 then
year(dateadd(yy, -1, getdate()) )
else
year( getdate())
end

Quarter To Date END DATE:

  convert(char(10), dateadd(dd, -(day(getdate() )), getdate() ), 111)

Previous Quarter To Date Start Date:

case month(getdate() )
when 1 then
convert(char(10), dateadd(yy,-1,dateadd(mm, -3, dateadd(dd, -(day(getdate() )-1), getdate() ) )), 111)
when 2 then
convert(char(10), dateadd(yy,-1,dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) )), 111)
when 3 then
convert(char(10), dateadd(yy,-1,dateadd(mm, -2, dateadd(dd, -(day(getdate() )-1), getdate() ) )), 111)
when 4 then
convert(char(10), dateadd(yy,-1,dateadd(mm, -3, dateadd(dd, -(day(getdate() )-1), getdate() ) )), 111)
when 5 then
convert(char(10), dateadd(yy,-1,dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) )), 111)
when 6 then
convert(char(10), dateadd(yy,-1,dateadd(mm, -2, dateadd(dd, -(day(getdate() )-1), getdate() ) )), 111)
when 7 then
convert(char(10), dateadd(yy,-1,dateadd(mm, -3, dateadd(dd, -(day(getdate() )-1), getdate() ) )), 111)
when 8 then
convert(char(10), dateadd(yy,-1,dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) )), 111)
when 9 then
convert(char(10), dateadd(yy,-1,dateadd(mm, -2, dateadd(dd, -(day(getdate() )-1), getdate() ) )), 111)
when 10 then
convert(char(10), dateadd(yy,-1,dateadd(mm, -3, dateadd(dd, -(day(getdate() )-1), getdate() ) )), 111)
when 11 then
convert(char(10), dateadd(yy,-1,dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) )), 111)
when 12 then
convert(char(10), dateadd(yy,-1,dateadd(mm, -2, dateadd(dd, -(day(getdate() )-1), getdate() ) )), 111)
end

Previous Quarter To Date End date:

  convert(char(10), dateadd(yy,-1,dateadd(dd, -(day(getdate() )), getdate() )), 111)

Year To Date START DATE:

  case month(getdate())
when 1 then
convert(char(4),dateadd(yy, -1, getdate()), 111) + '/01/01'
else
convert(char(4),datepart(yyyy, getdate()), 111) + '/01/01'
end

Last 12 Months Start date:

  convert(char(10), dateadd(mm, -12,dateadd(dd, -(day(getdate() )), getdate() )), 111)

Last 12 Months End date:

  convert(char(10), dateadd(yy, -1, dateadd(mm, 0, dateadd(dd, -(day(getdate() )-1), getdate() ) ) ), 111)

Year To Date Start Date:

  convert(char(4),datepart(yyyy, getdate()), 111) + '/01/01'

Current Quarter Start and End Dates:

 
select convert(char(10), dateadd(dd, -(day(getdate() ))+1, getdate() ), 111) , convert(char(10),dateadd(dd,-1, dateadd(mm, +3, dateadd(dd, -(day(getdate() )-1), getdate() ) )),111)

Quarter To Date Start Date Adhoc:

  case month(getdate() )
when 1 then
convert(char(10), dateadd(dd, -(day(getdate() ))+1, getdate() ), 111)
when 2 then
convert(char(10), dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 3 then
convert(char(10), dateadd(mm, -2, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 4 then
convert(char(10), dateadd(dd, -(day(getdate() ))+1, getdate() ), 111)
when 5 then
convert(char(10), dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 6 then
convert(char(10), dateadd(mm, -2, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 7 then
convert(char(10), dateadd(dd, -(day(getdate() ))+1, getdate() ), 111)
when 8 then
convert(char(10), dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 9 then
convert(char(10), dateadd(mm, -2, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 10 then
convert(char(10), dateadd(dd, -(day(getdate() ))+1, getdate() ), 111)
when 11 then
convert(char(10), dateadd(mm, -1, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
when 12 then
convert(char(10), dateadd(mm, -2, dateadd(dd, -(day(getdate() )-1), getdate() ) ), 111)
end



See also this link for PL/SQL procedure for truncating tables:
http://dwbitechguru.blogspot.ca/2014/11/sql-server-function-pl-sql-procedure-to.html