November 29, 2004 at 12:23 pm
I have two variables
SET @FromDate = '1/1/2004'
SET @ToDate = '5/23/2004'
Is there a way/trick to get the last day of let's say January 2004 (or any year that @FromDate variable has). I don't think that SQL date functions can do that.
Note: @FromDate variable could have '1/12/2004' or '1/23/1999' as well.
Thanks
November 29, 2004 at 12:37 pm
From somewhere on this site (don't have the URL to give credit)
CREATE FUNCTION [dbo].[getMthEndDate] (@date datetime)
RETURNS DATETIME
AS
BEGIN
RETURN dateadd(month,1+datediff(month,0,@date),0) - 1
END
November 30, 2004 at 1:02 am
This one uses fast and efficient Integer operations, so it should give good performance:
SELECT
DATEADD(MONTH,DATEDIFF(MONTH,30,GETDATE()),30)
A very cool script for lazycoders comes from SQL Server MVP Steve Kass. Try this:
declare @31st datetime
set @31st = '19341031' -- any 31st
declare @now datetime
set @now = getdate()
select dateadd(month,datediff(month,@31st,@now),@31st)
The most interesting thing about this script is that it works with virtually any last day of a month (past, present or future), as long as this month has 31 days. So, this is also possible:
declare @31st datetime
set @31st = '20051031' -- any 31st
declare @now datetime
set @now = getdate()
select dateadd(month,datediff(month,@31st,@now),@31st)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 30, 2004 at 6:18 am
Thanx for the performance pointer frank. But can you explain to me why your version is faster than the one I posted?
November 30, 2004 at 6:28 am
There shouldn't be a big difference between both methods. I only mentioned it because in most case you see someone coming up with cast and convert stuff between varchar and datetime and back again. So, something like this:
SELECT
DATEADD(DAY, -DAY(GETDATE()), CAST(CONVERT(CHAR(8), GETDATE(), 112) AS DATETIME))
AS Monatsultimo
The comment was not aimed at your posting.
However, depending on how you use your UDF, the UDF itself will degrade performance. And basically I don't see the need for a UDF here.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 30, 2004 at 6:36 am
I agree but I was referring to something I saw on this board... I would not use a UDF in this case either :-).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply