how to find the last date in the month for a given date 1

[TravisLaborde]

How can I find the last day in the month for a given date in TSQL?

For example, if I have a field account_date with values such as 4/23/2003, 5/10/2003... I'd like to return 4/30/2003, 5/31/2003 in my queries.

I need it to be in-line sql or a function or such so that I can then group based on the converted date.

Any help would be great!
Thanks,
Travis

 

[marsh]

Try:

select account_date , DateAdd("d", -1, DateAdd("m", 0, DateAdd("m", 1, account_date - (Day(account_date ) - 1)))) from table_name


Marsha

 

[TravisLaborde]

Wow, Marsha! I had the thought of making the date the 1st, then adding one month, then subtracting one day... which seems to be what you are doing here? I find it hard to even read it, but it works

Thanks very much,
Travis

 

[fluteplr]

In case anyone wants them here are first of month and last of month as user functions.

CREATE FUNCTION dbo.fnfirstofmonth (@ddate smalldatetime )
RETURNS smalldatetime
AS
BEGIN
set @ddate = dateadd(hh,-datepart(hh,(@ddate)),@ddate)
set @ddate = dateadd(mi,-datepart(mi,(@ddate)),@ddate)
set @ddate = dateadd(dd,-datepart(dd,@ddate)+1,@ddate)
return @ddate
END

CREATE FUNCTION dbo.fnlastofmonth (@ddate smalldatetime )
RETURNS smalldatetime
AS
BEGIN
set @ddate = dateadd(hh,-datepart(hh,(@ddate)),@ddate)
set @ddate = dateadd(mi,-datepart(mi,(@ddate)),@ddate)
set @ddate = dateadd(day,-1,dateadd(month,1,dateadd(day,-day(@ddate)+1,@ddate)))
return @ddate
END

 

[marsh]

Travis,

Oops, no wonder you thought I was too convolutted, I accidentally put the same step in twice (the month-add 0 -- already at first of month....). Here's a shortened method, with explanations:


select account_date ,
DateAdd("d", -1, DateAdd("m", 1, account_date - (Day(account_date ) - 1)))
from table_name


-- Get First of Current Month
account_date - (Day(account_date-1)

-- Then Go to First of Next Month
DateAdd("m", 1, account_date - (Day(account_date ) - 1)))

-- then back up a day for end of current month
DateAdd("d", -1, DateAdd("m", 1, account_date - (Day(account_date ) - 1)))

Marsha