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
No comments:
Post a Comment