Friday, 14 November 2014

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