convert year to last date of the year

Question

convert year to last date of the year

lein

SSC Eights!

Points: 974
More actions
May 26, 2017 at 11:49 am

#325938

I have a column with different years.How can i convert this column from year to last date of the year column.
Desired Output with the Last Date of the year column
Year    Last Date of the year
2010    12/31/2010
2015    12/31/2015
2011    12/31/2011
2016    12/31/2016
2013    12/31/2013

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

This is based on current date, you'll need to replace 'getdate()' with the date you are working with'

SELECT datepart(year, CAST(DATEADD(dd, -1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)) as date)) YEAR, CAST(DATEADD(dd, -1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)) as date) LastDayofYear

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

SELECT DATEADD(yyyy, DATEDIFF(yyyy, 0, GetDate())+1, -1)

lein SSC Eights! Points: 974 More actions · Answer 3

Henrico - I need the opposite of that i need to transform it to date not year.

Thanks 🙂

lein SSC Eights! Points: 974 More actions · Answer 4

Chris -- For some reason its giving me an illegal value date part error.

lein SSC Eights! Points: 974 More actions · Answer 5

Henrico Bekker - Friday, May 26, 2017 11:58 AM
This is based on current date, you'll need to replace 'getdate()' with the date you are working with'
SELECT datepart(year, CAST(DATEADD(dd, -1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)) as date)) YEAR, CAST(DATEADD(dd, -1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)) as date) LastDayofYear

Works like charm 🙂 Big thanks!

lein SSC Eights! Points: 974 More actions · Answer 6

lhynlen - Friday, May 26, 2017 12:09 PM
Chris -- For some reason its giving me an illegal value date part error.

Thanks for your response 🙂

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Summarizing (and correcting) the options:
SELECT YEAR(GETDATE()) YEAR, CAST(DATEADD(dd, -1, DATEADD(yy, DATEDIFF(yy, -1, GETDATE()), 0)) as date) LastDayofYear, CONVERT(date, DATENAME(YEAR,GETDATE())+'1231'), DATEADD(yyyy, DATEDIFF(yyyy, 0, GetDate())+1, -1)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 8

Being that the last day of every year is 12/31, why not just use DATEFROMPARTS...
DECLARE @Year INT = 2017; SELECT LastDayOfYear = DATEFROMPARTS(@Year, 12, 31);

Ed Wagner SSC Guru Points: 287026 More actions · Answer 9

Jason A. Long - Friday, May 26, 2017 1:20 PM
Being that the last day of every year is 12/31, why not just use DATEFROMPARTS...
DECLARE @Year INT = 2017; SELECT LastDayOfYear = DATEFROMPARTS(@Year, 12, 31);

Now that's the easy way. 😉

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions