Need to Get First & Last Date of a month by passing Month value (01 or 02..) & Year value (2012 or 2013 or...) in T-Sql

All Replies

• Thursday, January 31, 2013 5:42 AM

   

   
  

  0

  Hi,

  I got below Expression:

  DECLARE @MONTH INT
         ,@YEAR INT

  SET @MONTH = 2
  SET @YEAR = 2013

  SELECT DATEADD(MONTH,@MONTH-1,DATEADD(YEAR,@YEAR-1900,0)) AS FIRST_DATE  /*FIRST DATE*/
        ,DATEADD(DAY,-1,DATEADD(MONTH,@MONTH,DATEADD(YEAR,@YEAR-1900,0))) AS LAST_DATE /*LAST DATE*/

  There is any otherway to do this.

  Anyway above will solve my problem

  ---

  Thanks Shiven:) If Answer is Helpful, Please Vote

  • Marked As Answer by S Kumar Dubey Thursday, January 31, 2013 5:44 AM
  •  
  • Reply
  • Quote

   
• Thursday, January 31, 2013 5:45 AM

   

   
  

  0

  Hi All,

  I need T-SQL to get First Date & Last date of a month by passing Month value & Year Value.

  For Example:

  Month value =01

  Year value =2013

  I need below value 2013-01-01 & 2013-01-31

  Month value =02

  Year value =2013

  I need below value 2013-02-01 & 2013-02-28

  ---

  Thanks Shiven:) If Answer is Helpful, Please Vote

  Try:

  1st day is: convert(datetime,'201301'+'01',112)

  last day is: dateadd(dd,-1,dateadd(mm,1,convert(datetime,'201301'+'01',112)))

  ---

  Many Thanks & Best Regards, Hua Min

  • Reply
  • Quote

   
• Thursday, January 31, 2013 5:52 AM

   

   
  

  0

  If running SQL Server 2012:

  Declare @firstDate datetime2;
  Set @firstDate = DateFromParts(@year, @month, 1);
  
  Select
      @firstDate
      , DateAdd(Day, -1, DateAdd(Month, 1, @firstDate))
  ;

  If using older versions you only change the second line:

  Set @firstDate = Cast(Cast(@year As varchar) + '-' + Cast(@month As varchar) + '-01' As datetime2);

  ---

  Jose R. MCP
  Code Samples

  • Reply
  • Quote