locked
Need to get last Date of the month in a variable RRS feed

  • Question

  • User-1506965535 posted

    In the below procedure, I want to get the last day of the month. How to get that?

    Below is SP

    ALTER PROCEDURE Monthly_leave_Allocation_2016
    	@Year int,
        @Month int
    AS
    BEGIN
    	        declare  @actualMonth int
    		    declare  @actualYear int
    		    declare  @actuallastdate datetime
    
             BEGIN
    			   IF (@Month = 1)
    					  BEGIN
    					 	set  @actualYear = @Year - 1
    					    set	 @actualMonth = 12
    						 END
    				ELSE
    					  BEGIN
    						set @actualMonth = @Month - 1
    						set @actualYear = @Year
     					 END
     		   END	  
        	END	   
    GO

    Thursday, December 17, 2015 11:59 AM

Answers

  • User452040443 posted

    I think it would be better to do the formatting in the application but try:

    select CONVERT(CHAR(10), DATEADD(DAY, -1, DATEADD(MONTH, @Month - 1, DATEADD(YEAR, @Year - 2000, '20000101'))), 103)
    

    Hope this helps.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 17, 2015 1:08 PM
  • User452040443 posted

    Try:

    ALTER PROCEDURE Monthly_leave_Allocation_2016
    	@Year int,
        @Month int
    AS
    BEGIN
    	select CONVERT(CHAR(10), DATEADD(DAY, -1, DATEADD(MONTH, @Month - 1, DATEADD(YEAR, @Year - 2000, '20000101'))), 103)
    END
    GO

    Hope this helps.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 17, 2015 1:44 PM
  • User77042963 posted
    declare 	@Year int=2016,   @Month int=1
    
    
    select EOMONTH(Datefromparts(@Year,@Month,1),-1)
    
    select EOMONTH(Cast(@Year as varchar(4))+'-'+Cast(@Month as varchar(2))+'-01',-1)
    
    select  Dateadd(day,-1,Cast(Cast(@Year as varchar(4))+'-'+Cast(@Month as varchar(2))+'-01' as date) )

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 17, 2015 2:35 PM

All replies

  • User452040443 posted

    From SQL Server 2012 or newer try:

    SELECT DATEADD(DAY, -1, DATEFROMPARTS(@Year, @Month, 1))

    Or for all versions try:

    select DATEADD(DAY, -1, DATEADD(MONTH, @Month - 1, DATEADD(YEAR, @Year - 2000, '20000101')))
    

    Hope this helps.

    Thursday, December 17, 2015 12:23 PM
  • User-1506965535 posted

    I am using the second one,

    and it is giving me result as

    Dec 31 2015 12:00AM

    but i want to display it as

    31/12/2015 and also i want to remove the time part

    Thursday, December 17, 2015 12:33 PM
  • User452040443 posted

    I think it would be better to do the formatting in the application but try:

    select CONVERT(CHAR(10), DATEADD(DAY, -1, DATEADD(MONTH, @Month - 1, DATEADD(YEAR, @Year - 2000, '20000101'))), 103)
    

    Hope this helps.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 17, 2015 1:08 PM
  • User-1506965535 posted

    Getting error as

    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    Below is my updated SP

    ALTER PROCEDURE Monthly_leave_Allocation_2016
    	@Year int,
        @Month int
    AS
    BEGIN
    	        declare  @actualMonth int
    		    declare  @actualYear int
    		    declare  @actuallastdate datetime
             BEGIN
    			   IF (@Month = 1)
    					  BEGIN
    					 	set  @actualYear = @Year - 1
    					    set	 @actualMonth = 11
    					 END
    				ELSE
    					  BEGIN
    						set @actualMonth = @Month - 1
    						set @actualYear = @Year
     					 END
     				--select @actuallastdate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@actualMonth),0))
     				select @actuallastdate = 
     				 CONVERT(CHAR(10), DATEADD(DAY, -1, DATEADD(MONTH, @Month - 1, DATEADD(YEAR, @Year - 2000, '20000101'))), 103)
     		   END	  
     		   	    print @actuallastdate
       END	   
    GO
    
    
     -- exec Monthly_leave_Allocation_2016 2016,1

     

    Thursday, December 17, 2015 1:11 PM
  • User452040443 posted

    Try:

    ALTER PROCEDURE Monthly_leave_Allocation_2016
    	@Year int,
        @Month int
    AS
    BEGIN
    	select CONVERT(CHAR(10), DATEADD(DAY, -1, DATEADD(MONTH, @Month - 1, DATEADD(YEAR, @Year - 2000, '20000101'))), 103)
    END
    GO

    Hope this helps.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 17, 2015 1:44 PM
  • User77042963 posted
    declare 	@Year int=2016,   @Month int=1
    
    
    select EOMONTH(Datefromparts(@Year,@Month,1),-1)
    
    select EOMONTH(Cast(@Year as varchar(4))+'-'+Cast(@Month as varchar(2))+'-01',-1)
    
    select  Dateadd(day,-1,Cast(Cast(@Year as varchar(4))+'-'+Cast(@Month as varchar(2))+'-01' as date) )

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 17, 2015 2:35 PM
  • User-1506965535 posted

    It's working fine now

    Thanks to All. :)

    Friday, December 18, 2015 5:30 AM