February 16, 2005 at 3:14 pm
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
February 16, 2005 at 3:20 pm
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 ) )
February 16, 2005 at 5:51 pm
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
_______________
February 17, 2005 at 2:15 am
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]
February 17, 2005 at 6:24 am
Thanks everyone for the solutions.
Ryan
February 17, 2005 at 3:33 pm
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.
February 18, 2005 at 1:30 am
Ä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]
February 18, 2005 at 2:40 pm
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)
February 18, 2005 at 5:03 pm
What a great function set - and it truncates off any minutes as well!
Two thumbs up!
February 21, 2005 at 2:19 am
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]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply