CODEWITHSUNDEEP
sqlsql-server-2008t-sql
DtotheG
DtotheG

Reputation: 1257

How to work out the month end date from the month - SQL

I've got a column that returns integers for months i.e. 7 for July. What I want to do is take this 7 and return the month end i.e. 31-july-2012, is there a function that allows me to do this in SQL?

Thanks

Upvotes: 0

Views: 1809

Answers (2)

Taryn
Taryn

Reputation: 247870

How about something like this:

DECLARE @Month int
DECLARE @Year int

set @Month = 7
set @Year = datepart(year,getdate())

select DATEADD(day,-1,DATEADD(month,@Month,DATEADD(year,@Year-1900,0)))

Results:

2012-07-31 00:00:00.000

Upvotes: 3

saj
saj

Reputation: 4816

Have a look at this;

http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/

this is the bit you're interested in;

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

Upvotes: 1

Related Questions