CODEWITHSUNDEEP
sql-server
Srinivasan
Srinivasan

Reputation: 12060

SQL Server Date Calculation

I want to get the based on current date.

If current date is:

For example:

Current_Date    Exp_date
-------------------------
01-08-2019      15-08-2019
10-08-2019      15-08-2019
14-08-2019      15-08-2019
15-08-2019      31-08-2019
20-08-2019      31-08-2019
25-08-2019      31-08-2019
31-08-2019      31-08-2019

I want as much as simplified form.

Upvotes: 0

Views: 82

Answers (5)

Partha
Partha

Reputation: 75

Check this..

select case when datepart(dd, dt) between 1 and 14 then right('0'+ cast(month(dt) as varchar(2)), 2) + '-15-' + cast(year(dt) as varchar(4)) else eomonth(dt) end from test

Upvotes: 0

Mahesh.K
Mahesh.K

Reputation: 901

We can achieve it with simple logic as below . If You are using Sql Server version 2012 and higher versions we've EOMONTH() Function to give EndOfMonth Date .

Sample Data:

CREATE TABLE #YourTable (CurrentDate DATETIME)
INSERT INTO #YourTable (CurrentDate)SELECT '08-01-2019'  
INSERT INTO #YourTable (CurrentDate) SELECT '08-10-2019'
INSERT INTO #YourTable (CurrentDate) SELECT '08-14-2019'
INSERT INTO #YourTable (CurrentDate) SELECT '08-15-2019'
INSERT INTO #YourTable (CurrentDate) SELECT '08-20-2019'
INSERT INTO #YourTable (CurrentDate) SELECT '08-25-2019'
INSERT INTO #YourTable (CurrentDate) SELECT '08-31-2019'

Query: 

 SELECT DATEPART(DD,CurrentDate),
 case when DATEPART(DD,CurrentDate)<15 THEN  DATEADD(dd,-day(CurrentDate)+15,CurrentDate) 
      when DATEPART(DD,CurrentDate)>14 THEN EOMONTH(CurrentDate) END  AS Exp_Date
 FROM #YourTable

Upvotes: 1

hardikpatel
hardikpatel

Reputation: 310

SELECT [Current_date],Exp_date,
       CASE WHEN 14 BETWEEN DATEPART(DAY,[Current_date]) AND DATEPART(DAY,Exp_date) 
                 THEN  CAST(DATEADD(DAY,15-DATEPART(DAY,[Current_date]),[Current_date])  AS DATE)
            ELSE CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,[Current_date])+1,0))  AS DATE)
         END
FROM MOnthData

Upvotes: 0

mhd.cs
mhd.cs

Reputation: 721

Try this:replace hardCode date With your date

SELECT CONCAT(CONCAT(CONCAT (CONCAT(CASE  WHEN  DAY('2017/08/25') < 14 THEN 15 else 31 end , '-'),

 CASE  WHEN  DATEPART(month, '2017/08/25') < 10  THEN Concat('0',DATEPART(month, '2017/08/25')) else DATEPART(month, '2017/08/25') end),'-'),   cast(DATEPART(year, '2017/08/25') as nvarchar(4)))

Upvotes: 0

DarkRob
DarkRob

Reputation: 3833

You may try this.

 select current_date, 
        case when datepart(day, current_date) > 14 
        then 
            DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, current_date) + 1, 0))
        else  
           DATEADD(D, 15,   DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, current_date) , 0)))
         end as Exp_date
 from yourtable

Upvotes: 1

Related Questions