SQL - Modifying Dates

Question

What is the best way to take a date variable in SQL and set it to Beginning of Year and End of Year?

declare @calcDate date = '7/7/2016'

How do I get '1/1/2016' and '12/31/2016' using @calcDate?

please use the tags of the database engine that you are using. It even suggests you to do so when asking — Lamak, Jul 07 '16 at 17:22
I am not sure I understand what you are asking. What other tags should I add? — Anagins, Jul 07 '16 at 17:27
[sql] is the language, you could be using SQL Server, MySQL, Oracle, Postgresql, etc. The answer will depend on which one you are using, so add the corresponding tags to your question — Lamak, Jul 07 '16 at 17:28
The first day of a year is always 1/1, and the last day is always 12/31. No need to "calculate" this. — Tim Lehner, Jul 07 '16 at 17:30
Already been answered http://stackoverflow.com/questions/13437362/how-to-get-first-and-last-date-of-current-year — Dave Kelly, Jul 07 '16 at 17:32
Thank you. That question didnt come up when I was searching for an existing solution. I apologize for the repetition. — Anagins, Jul 07 '16 at 17:35

score 2 · Answer 1 · answered Jul 07 '16 at 17:35

2

I would just use datefromparts():

select datefromparts(year(@calcdate), 1, 1) as soy,
       datefromparts(year(@calcdate), 12, 31) as eoy

Earlier versions of SQL Server require strange date arithmetic. This function is available in SQL Server 2012+ (see here).

answered Jul 07 '16 at 17:35

Gordon Linoff

1,242,037
58
646
786

I got an error attempting to use DateFromParts. Does this function require a specific version of SQL Service 2012? – Anagins Jul 07 '16 at 17:39
1

@inVad3r you might be using SQL Server management Studio 2012 on a database for a previous version – Lamak Jul 07 '16 at 17:40
You would be correct. I apologize for the confusion. – Anagins Jul 07 '16 at 17:41

score 1 · Accepted Answer · answered Jul 07 '16 at 17:27

1

DECLARE @calcDate date = '7/7/2016'

SELECT
DATEADD(yy, DATEDIFF(yy,0,@calcDate), 0) AS BeginningOfYear,
DATEADD(yy, DATEDIFF(yy,0,@calcDate) + 1, -1) AS EndOfYear

answered Jul 07 '16 at 17:27

freakinthesun

699
1
9
19

This answer looks right why the down vote with no comment? – Dave Kelly Jul 07 '16 at 17:33
@DaveKelly Not my downvote, but this answer was posted when there were no tags about the RDBMS, so it could've been wrong for op. In this case, it wasn't – Lamak Jul 07 '16 at 17:34

score 0 · Answer 3 · answered Jul 07 '16 at 17:52

Seems to me there is no real "calculation" to be done here since the first day of the year is always 1/1 and the last day is always 12/31. We just need to append these to the year in question:

declare @calcDate date = '7/7/2016'
select cast(cast(year(@calcDate) as varchar(4)) + '0101' as date)
select cast(cast(year(@calcDate) as varchar(4)) + '1231' as date)

Result:

2016-01-01
2016-12-31

year is available in SQL Server 2008 and above.

SQL - Modifying Dates

3 Answers3