mssql date function

SELECT GETDATE(); --today

SELECT CONVERT(CHAR(8), DATEADD(dd,-1,GETDATE()),112); -- yesterday

SELECT CONVERT(CHAR(8), DATEADD(dd,+1,GETDATE()),112); --tomorrow

SELECT CONVERT(CHAR(8), DATEADD(mm,+3, GETDATE()),112); -- next 3 month

SELECT CONVERT(CHAR(8), DATEADD(yy,+1, GETDATE()),112); -- next 1 year

SELECT CONVERT(CHAR(8), DATEADD(mm, -1, GETDATE()-DAY(GETDATE()-1)),112); -- before 1 month

SELECT CONVERT(CHAR(8), DATEADD(mm, 0, GETDATE()-DAY(GETDATE())),112); -- last day of before 1 month

SELECT CONVERT(CHAR(8), DATEADD(dd, 0, GETDATE()-DAY(GETDATE()-1)),112); --first day of a month

SELECT CONVERT(CHAR(8), DATEADD(mm, +1, GETDATE()-DAY(GETDATE())),112); --last day of a month

SELECT CONVERT(CHAR(8), DATEADD(MM, +1, GETDATE()-DAY(GETDATE()-1)),112); --first day of next month

SELECT CONVERT(CHAR(8), DATEADD(MM, +2, GETDATE()-DAY(GETDATE())),112); -- last day of next month

SELECT CONVERT(CHAR(8), DATEADD(MM, -2, GETDATE()-DAY(GETDATE()-1)),112); -- first day of before 2 month

SELECT CONVERT(CHAR(8), DATEADD(MM, -1, GETDATE()-DAY(GETDATE())),112); --last day of before 2 month

SELECT CONVERT(CHAR(8), DATEADD(YY, 0, GETDATE()-DATEPART(DAYOFYEAR, GETDATE()-1)),112); -- first day of a year 

SELECT CONVERT(CHAR(8), DATEADD(YY, +1, GETDATE()-DATEPART(DAYOFYEAR, GETDATE())),112); -- last day of a year

SELECT CONVERT(CHAR(8), DATEADD(YY, +1, GETDATE()-DATEPART(DAYOFYEAR, GETDATE()-1)),112); --first day of next year 

SELECT CONVERT(CHAR(8), DATEADD(YY, +2, GETDATE()-DATEPART(DAYOFYEAR, GETDATE())),112); --last day of next year

SELECT DATEPART(DAYOFYEAR,'2010-07-20'); -- days of between 2010-01-01 and 2010-07-02

SELECT DATEPART(WEEK, '2010-07-20');

SELECT DATEPART(WEEKDAY, '2010-07-20');

 

DECLARE @WEEK_DAY DATETIME;

SET @WEEK_DAY = '2010-08-15';

SELECT

CASE DATEPART(WEEKDAY, @WEEK_DAY) WHEN '1' THEN 'sun'

  WHEN '2' THEN 'mon'

  WHEN '3' THEN 'tue'

  WHEN '4' THEN 'wed'

  WHEN '5' THEN 'tur'

  WHEN '6' THEN 'fri'

  ELSE 'sat'

END AS 'day';

 

SELECT DATEPART(DAYOFYEAR, '2010-08-15');

SELECT DATEPART(WEEK, '2010-08-15');

 

 

DECLARE @_date CHAR(8);

SET @_date = REPLACE('2010-07-20','-','');

SELECT SUBSTRING(@_date,1,4) + 'year' + substring(@_date,5,2) +'month' + substring(@_date,7,2) + 'day'

,CONVERT(VARCHAR, DATEPART(WEEK, @_date))+'th weeks'

,CONVERT(VARCHAR, DATEPART(dayofyear, @_date))+'th days';

 

 

DECLARE @_date DATETIME;

SET @_date = CONVERT(CHAR(8), REPLACE('2010-07-20','-',''),112);

SELECT CONVERT(CHAR(4),DATEPART(YEAR,@_date)) + 'year' ,CONVERT(CHAR(2),DATEPART(MONTH,@_date))+'month', CONVERT(CHAR(2),DATEPART(DAY,@_date))+'day';

SELECT CONVERT(VARCHAR, DATEPART(WEEK, @_date))+'th weeks'

,CONVERT(VARCHAR, DATEPART(dayofyear, @_date))+'th days';