Last Day of each month function?

Question

Post reply

Last Day of each month function?

rhunt

SSCertifiable

Points: 5868
More actions
February 16, 2005 at 3:14 pm

#163769

I have a data feed that is providing dates like so:
200802 --translates to (02/01/2008)
202912
200508
200508
200805
200502
200508
200508
200503
200607
I have an app that originally needed to perioodically read the date and interpret each as the first day of each month (02/01/2008). Accordingly, the following function was created:
_______________________________________________________
Create Function dbo.fnConvertExpirationDate(@ExpirationDate int)
Returns datetime
as
begin
declare @NewExpirationDate datetime
select @NewExpirationDate = convert(datetime, substring(convert(char(6), @ExpirationDate), 5, 2)
+ '/01/'
+ substring(convert(char(6), @ExpirationDate), 1, 4))
return @NewExpirationDate
end
________________________________________________________
Easy, easy stuff... Now I need each date to automatically be interpreted as the last day of each month (i.e. 02/28/2008 or 29th in leap year). Is there an easy way to do this??
Thanks in advance.
Ryan

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

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

To get last day of month, I usually add 1 month and subtract 1 day.

So take the existing algorithm (which gives you 1st of month) and ...

DATEADD( d, -1, DATEADD(M, 1, YourDate ) )

brendt hess Hall of Fame Points: 3256 More actions · Answer 2

Just as a heads-up, you will probably be better off just appending '01' to the existing data string you have, rather than using the mm/dd/yyyy format. yyyymmdd is an international standard, and will be understood no matter where your function ends up, whereas mm/dd/yyyy is mostly only found in the USA.

PW has the idea I was going to present. So, given your data, the Function would be:

_______________________________________________________

Create Function dbo.fnConvertExpirationLDOM(@ExpirationDate int)

Returns datetime

as

begin

declare @NewExpirationDate datetime

set @NewExpirationDate = Dateadd(Day, -1, DateAdd(Month, 1, convert(datetime, convert(varchar, @ExpirationDate) + '01')))

return @NewExpirationDate

end

_______________

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

FWIW, here's a version without using a UDF. It's based on a suggestion provided by SQL Server MVP Steve Kass.

create table #dummy

(

c1 int

)

insert into #dummy values(202912)

insert into #dummy values(200508)

insert into #dummy values(200805)

insert into #dummy values(200502)

insert into #dummy values(200402) --leap year proof

declare @31st datetime

set @31st = '19341031' -- any 31st, no matter if past, present or future

select

dateadd(month,datediff(month,@31st,cast(c1 as char(6))+'01'),@31st)

from #dummy

drop table #dummy

------------------------------------------------------

2029-12-31 00:00:00.000

2005-08-31 00:00:00.000

2008-05-31 00:00:00.000

2005-02-28 00:00:00.000

2004-02-29 00:00:00.000

(5 row(s) affected)

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

rhunt SSCertifiable Points: 5868 More actions · Answer 4

rhunt

SSCertifiable

Points: 5868

February 17, 2005 at 6:24 am

#542785

Thanks everyone for the solutions.

Ryan

Shie Morozow SSC Enthusiast Points: 169 More actions · Answer 5

Ryan. Examine this statement:

SELECT CONVERT(DATETIME, '@ExpirationDate + '01', 112). s

This statement is very simple, but it may be difficult to understandd by programmers of other languages.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Äh, may I say that the requirement was to construct the *last* day of a month? I can't see your statement doing this. Let alone the fact that it won't run out of the box anyway

DECLARE @ExpirationDate CHAR(6)

SET @ExpirationDate = '200402'

SELECT CONVERT(DATETIME, '@ExpirationDate + '01', 112). s

Server: Msg 170, Level 15, State 1, Line 3

Line 3: Incorrect syntax near '01'.

Server: Msg 105, Level 15, State 1, Line 3

Unclosed quotation mark before the character string ', 112). s

'.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

PhilPacha Hall of Fame Points: 3645 More actions · Answer 7

End of month logic for "YourDate":

DATEADD(d, -1, DATEADD(m, 1 + DATEDIFF(m, 0, @YourDate), 0))

Also, first of month logic:

DATEADD(m, DATEDIFF(m, 0, @YourDate), 0)

brendt hess Hall of Fame Points: 3256 More actions · Answer 8

What a great function set - and it truncates off any minutes as well!

Two thumbs up!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

Okay, another variation:

SELECT

DATEADD(MONTH,DATEDIFF(MONTH,30,GETDATE()),0) FirstDay

, DATEADD(MONTH,DATEDIFF(MONTH,30,GETDATE()),30) LastDay

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]