sql query to set a date to last day of the year 1

[nsanto17]

I have two fields

mytable.approval_date
mytable.approval_exp_date

all approvals expire on 12/31 of year they were approved. how can I write a query to update the mytable.approval_exp_date.

ie.
Approval = 2/1/2014
approval_exp_date = 12/31/2014


Any help would be great.

Thanks

Nick

 

[gmmastros]

I would approach it like this...

Get the whole number of years since Jan 1, 1900.
Add that whole number of year to Jan 1, 1900. (this will return the first day of the year)
Add 1 year
Subtract 1 day.

Putting it all together...

Select	DateAdd(Day, -1, DateAdd(Year, 1 + DateDiff(Year, 0, @Approval), 0))

Please take a couple minutes to understand how this works. It's a neat trick that can be used in a lot of different ways. Hint, the 0's you see in the code actually represent "Day 0" in SQL Server which is actually Jan 1, 1900.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[nsanto17]

I like that way of approaching it however

declare @date_exp datetime = 2013-08-15	
SELECT      DateAdd(Day, -1, DateAdd(Year, 1 + DateDiff(Year, 0, @date_exp), 0)) 

            'Last Day of Current Year'

Returns a value of
1905-12-31

I would think it should return a value of 2013-12-31

Thanks

 

[gmmastros]

try again with single-quotes around your date, like this:

declare @date_exp datetime = [!]'[/!]2013-08-15[!]'[/!]
SELECT      DateAdd(Day, -1, DateAdd(Year, 1 + DateDiff(Year, 0, @date_exp), 0)) 

            'Last Day of Current Year'

Without the single quotes, SQL Server is treating 2013-08-15 and an integer math expression.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

2013 - 8 - 15 = 1990

Select 2013-08-15

Now add 1,990 days to Jan 1, 1900

Select DateAdd(Day, 1990, 'Jan 1, 1900')

You get June 14, 1905. The last day of the year for that date is Dec 31, 2013.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

My mistake.

The last day of the year for June 14, 1905 is Dec 31, 1905 (not 2013). My point is... the formula works given the proper input.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[nsanto17]

worked like a charm.

PS proper input is always important.