Date Calculation

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

Answer 1

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

Answer 2

User303363814 posted

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

Answer 3

User-797751191 posted

Hi Paul

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

THanks

Answer 4

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))

Answer 5

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

Answer 6

User77042963 posted

Did you get  the result you need with code?

Answer 7

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)))

Answer 8

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  

Answer 9

User-797751191 posted

Hi Limno

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

Thanks

Answer 10

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.