I want to retrieve the day of half year in sql server

Question

If I want day of year I am using Select datepart(dayofyear , getdate()).

This query will return the day of year of the system date. So the value will be between 1 to 365/366. Now I want to return the day of half-year. Let us suppose we have 12 months and we divide it into 2 parts so it will be Jan to Jun and July to Dec. Now if my system date is 1st july it should return 1 because 1st july is the starting day of the half year. Please help me in solving this issue.

score 6 · Answer 1 · answered Jul 13 '16 at 13:59

6

Pretty strange requirement but fairly simple with some date math.

select 
    case when month(GETDATE()) <= 6 
        then 
            datepart(dayofyear , getdate())
        else
            datediff(day, dateadd(month, 6, dateadd(year, datediff(year, 0, getdate()), 0)), getdate()) + 1
    end

answered Jul 13 '16 at 13:59

Sean Lange

33,028
3
25
40

the part in `else` probably can be reduced to `datediff(day, datefromparts(year(getdate()), 7,1), getdate()) + 1`? – xbb Jul 13 '16 at 14:37
@xbb the OP did not specify which version of sql server they are using. datefromparts was introduced in 2012. If they are using anything older it wouldn't work. – Sean Lange Jul 13 '16 at 14:38

Gordon Linoff · Answer 2 · 2016-07-13T14:17:18.323

1

How about this:

select (case when month(getdate()) <= 6 then datepart(dayofyear, getdate())
             else (datepart(dayofyear, getdate()) -
                   datepart(dayofyear, cast(datename(year, getdate()) + '-06-30' as date)
                  )
        end)

This is a bit tricky to get right because leap years don't affect the numbers in the second half of the year. The basic idea for the second half of the year is to subtract the day of the year for June 30th (or July 1st and then add 1).

edited Jul 13 '16 at 14:17

answered Jul 13 '16 at 14:01

Gordon Linoff

1,242,037
58
646
786

Need to change the case expression to <= 6. Otherwise you would get negative numbers in June. :) – Sean Lange Jul 13 '16 at 14:13

mortb · Answer 3 · 2016-07-13T14:26:37.943

0

If the second half always begin July 1st you can write this:

SELECT DATEPART(DAYOFYEAR, GETDATE()) 
  %  datepart(DAYOFYEAR, (DATEADD(YEAR, DATEPART(YEAR, GETDATE()) -1900, DATEADD(MONTH, 5, DATEADD(DAY, 29, 0)))))

edited Jul 13 '16 at 14:26

answered Jul 13 '16 at 13:59

mortb

9,361
3
26
44

1

does not answer the question – mcha Jul 13 '16 at 14:00
3

But July 2 will be 2. – Brad Jul 13 '16 at 14:02

score 0 · Answer 4 · edited May 23 '17 at 10:28

0

I don't have access to SQL Server right now, so can't test this, but first you need to know the number of days in the year - the easiest way to do this is creating a function as per the second answer to this SO question.

Assuming you've named the function "fn" as per the suggestion, you can then use the modulo function.

It would be something like:

Select datepart(dayofyear , getdate()) % (fn(datepart(year , getdate()) / 2)

edited May 23 '17 at 10:28

Community

1
1

answered Jul 13 '16 at 14:00

Neville Kuyt

29,247
1
37
52

But scalar functions are such poor performers. – Sean Lange Jul 13 '16 at 14:05

score 0 · Answer 5 · answered Jul 13 '16 at 14:07

0

Select 
CASE WHEN day(eomonth('2016-02-01')) = 29 then datepart(dayofyear , getdate()) - 182 ELSE datepart(dayofyear , getdate()) - 183 END

answered Jul 13 '16 at 14:07

Mark Roll

506
3
6
15

This works only with 2012+. If you can make it dynamic based on current system date then it would be viable. As it is, the date for the February check is hardcoded. – Sean Lange Jul 13 '16 at 14:09
1

@SeanLange . . . Hmm, it only works with 2012+ in leap years. That would be a very amusing bug to try to find. – Gordon Linoff Jul 13 '16 at 14:18

score 0 · Answer 6 · answered Jul 13 '16 at 14:38

0

SELECT  CASE WHEN MONTH(GETDATE()) < 7 
             THEN DATEDIFF(DAY,CONCAT(YEAR(GETDATE()),'-01-01'), GETDATE()) 
             ELSE DATEDIFF(DAY,CONCAT(YEAR(GETDATE()),'-07-01'), GETDATE())
        END + 1

answered Jul 13 '16 at 14:38

JamieD77

13,796
1
17
27

score 0 · Answer 7 · edited Nov 23 '20 at 07:06

0

DECLARE @DATE DATETIME = '08/06/2020'
SELECT CASE WHEN MONTH(@DATE) <=6 THEN DATEDIFF(DD,DATEFROMPARTS(YEAR(@DATE),01,01),@DATE)
            ELSE DATEDIFF(DD,DATEFROMPARTS(YEAR(@DATE),07,01),@DATE) END +1

edited Nov 23 '20 at 07:06

4b0

21,981
30
95
142

answered Nov 23 '20 at 07:05

Yogi

1

Your answer might be technically right. But you should add some explenations to it. This would improve your answer. – Marcinek Nov 23 '20 at 07:45

vrksoln · Answer 8 · 2023-08-05T07:51:28.320

-1

for e.g. say current date is 2023-08-05, this gives you the day of the year as 217 (days), but that is from 1st of Jan, but since, you need to count from 1 in the second half of the year, deduct this by days already passed as per example '2023-06-30' result is 181 days, Now, deduct 181 from 217 which gives 36 days in second half of the year starting from '2023-07-01' as per example)

select case when DATEPART(m,getdate()) <= 6 then 
DATEPART(DY,getdate()) else DATEPART(DY,getdate()) - Datepart(DY,Convert(date,Convert(varchar,year(getdate())) + '-06-30')) END

edited Aug 05 '23 at 07:51

answered Aug 15 '18 at 15:57

vrksoln

29
4

'2023-080-05' is clearly a typo, and the provided SQL code throws an error: "Conversion failed when converting date and/or time from character string.", see: https://dbfiddle.uk/8nlRn3_s. Please test before posting. – Luuk Aug 05 '23 at 07:47
'2023-08-05' is just an example as i am testing it today and getdate() will give this date, so '2023-080-05' (080) does not make any sense here as it whomsoever may test this on any date ,that days current date is taken (getdate()) – vrksoln Aug 05 '23 at 07:59
"(080) does not make any sense here" because "080" is not a valid month number, not because it just happens to be august when you write this. – Luuk Aug 05 '23 at 09:15

I want to retrieve the day of half year in sql server

8 Answers8