locked
Date Calculation RRS feed

  • Question

  • User-797751191 posted

    Hi

       I want if user entered date in Parameter say 20/11/2019 then it should give me 31/03/2019

    If Parameter date is 18/02/2019 or 09/10/2018 or 4/7/2018 then it should give me 31/03/2018

    Thanks

    Tuesday, December 17, 2019 12:26 PM

All replies

  • User452040443 posted

    Hi,

    Try something like this:

    select
        dateadd
           (day,
            30,
            dateadd
               (month, 
                2, 
                dateadd
                   (year, 
                    datediff(year, 0, @MyParam) -
                    case when datepart(month, @MyParam) * 100 + datepart(day, @MyParam) < 0331 then 1 else 0 end, 
                    0)))

    Hope this help

    Tuesday, December 17, 2019 12:47 PM
  • User303363814 posted

    Select iif(@dateParam >= '2019-4-1', cast('2019-3-31' as DateTime), cast('2018-3-31' as DateTime))

    Tuesday, December 17, 2019 12:59 PM
  • User-797751191 posted

    Hi Paul

      It can be 10/03/2018 then it should return 31/03/2017

    THanks

    Tuesday, December 17, 2019 2:38 PM
  • User77042963 posted
    declare @dt date='2018-03-10'
    Select datepart(quarter,@dt),
    Dateadd(day,-1,
    dateadd(quarter,1+(datediff(quarter,0,@dt))
    +Case when datepart(quarter,@dt)>1 
    then 1-datepart(quarter,@dt) else -4 end,0))

    Tuesday, December 17, 2019 4:26 PM
  • User-797751191 posted

    Hi Limno

      How the below code works

    declare @dt date='2018-03-10'
    Select datepart(quarter,@dt),
    Dateadd(day,-1,
    dateadd(quarter,1+(datediff(quarter,0,@dt))
    +Case when datepart(quarter,@dt)>1 
    then 1-datepart(quarter,@dt) else -4 end,0))

    Thanks

    Tuesday, December 17, 2019 4:45 PM
  • User77042963 posted

    Did you get  the result you need with code?

    Tuesday, December 17, 2019 6:38 PM
  • User303363814 posted

    Select iif(@dateParam >= '2019-4-1', cast('2019-3-31' as DateTime), iif(@dateParam >= '2018-4-1', cast('2018-3-31' as DateTime), cast('2017-3-31)))

    Tuesday, December 17, 2019 9:49 PM
  • User77042963 posted

    --or

    declare @dt date='2019-01-18'
    
    select CASE
            WHEN MONTH(@dt) BETWEEN 1  AND 3  THEN  datefromparts(YEAR(@dt) - 1,3,31)
           else
    	   datefromparts(YEAR(@dt),3,31)
     END  

    Wednesday, December 18, 2019 3:26 AM
  • User-797751191 posted

    Hi Limno

       yes earlier was working. Can u pls guide how it works

    Thanks

    Wednesday, December 18, 2019 4:35 AM
  • User77042963 posted

    It is date calculation with date related functions in SQL.

    Check these functions used in the code. If you know how each function is working, you should be able to understand the solution by then.

    Wednesday, December 18, 2019 2:21 PM