Prev: SQL connections
Next: SP parameter optional
From: DBA on 10 Mar 2010 11:19
RETURN (DATEADD(wk, DATEDIFF(wk, 0,@CURRENTDATE),0))

this one doesn't return the date of last monday, @currentdate is today but this one only return the date of this monday.

Not the last one.




gv wrote:

Function to get last Monday of current week
29-Oct-08

Hi guys,

trying to create a function to return the last Monday of a current week?

any help would be great.


CREATE FUNCTION [cvt_LastMonday]()

RETURNS datetime

AS

BEGIN

DECLARE @CURRENTDATE DATETIME
SET @CURRENTDATE = CURRENT_TIMESTAMP

RETURN (DATEADD(wk, DATEDIFF(wk, 0,@CURRENTDATE),0))

END

thanks,
gv

Previous Posts In This Thread:

On Wednesday, October 29, 2008 4:16 PM
gv wrote:

Function to get last Monday of current week
Hi guys,

trying to create a function to return the last Monday of a current week?

any help would be great.


CREATE FUNCTION [cvt_LastMonday]()

RETURNS datetime

AS

BEGIN

DECLARE @CURRENTDATE DATETIME
SET @CURRENTDATE = CURRENT_TIMESTAMP

RETURN (DATEADD(wk, DATEDIFF(wk, 0,@CURRENTDATE),0))

END

thanks,
gv

On Wednesday, October 29, 2008 4:19 PM
Aaron Bertrand [SQL Server MVP] wrote:

This word problem is confusing to me. If it is Sunday, what do you want?
This word problem is confusing to me. If it is Sunday, what do you want?
Tomorrow, or last Monday? If it is Monday, do you want today, or last
Monday? I think from Tuesday it is straight forward but you need better
specs.

On Wednesday, October 29, 2008 4:26 PM
gv wrote:

My apologies,If Sunday last MondayIf Monday then that dayI think this works
My apologies,

If Sunday last Monday
If Monday then that day

I think this works which I included:
select (DATEADD(wk, DATEDIFF(wk, 0,current_timestamp),0))

my problem is the function itself?

Thanks
gv

"Aaron Bertrand [SQL Server MVP]" <ten.xoc(a)dnartreb.noraa> wrote in message
news:C52E3D94.15AA7%ten.xoc(a)dnartreb.noraa...

On Wednesday, October 29, 2008 4:35 PM
Aaron Bertrand [SQL Server MVP] wrote:

Can you elaborate? What is the "problem"?
Can you elaborate? What is the "problem"?

I ran this and it seems to work (though I didn't change my clock to test
what you think should happen on a Sunday or Monday):

USE tempdb;
GO

CREATE FUNCTION dbo.cvt_LastMonday() -- always use schema prefix!!
RETURNS DATETIME
AS
BEGIN
-- why bother with a DECLARE for one use?
-- why use WK when WEEK is much easier to read?
RETURN (DATEADD(WEEK, DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP),0));
END
GO

SELECT dbo.cvt_LastMonday();
GO

On Wednesday, October 29, 2008 5:27 PM
K wrote:

Re: Function to get last Monday of current week
You should look at SET DATEFIRST and @@DATEFIRST before doing date
calculations -- SET DATEFIRST sets the day that is considered the first day
of the week, and your calculations could be wrong if the setting is changed
or you use the function on a server w/a different setting.

Another option is using a calendar table -- google "calendar table" and you
should find an example. Then you could do a query like:

SELECT MAX([dt])
FROM calendar
WHERE [dt] <= GETDATE() AND [dw] = 'MON';

HTH


"gv" wrote:

On Monday, November 03, 2008 8:09 AM
gv wrote:

Thanks for your help.I get this error?
Thanks for your help.

I get this error?

Server: Msg 443, Level 16, State 1, Procedure cvt_LastMonday, Line 8
Invalid use of 'getdate' within a function.

Thanks
gv

On Monday, November 03, 2008 8:19 AM
Plamen Ratchev wrote:

You can pass the current date as parameter to the
You can pass the current date as parameter to the function:
http://sqlserver2000.databases.aspfaq.com/how-do-i-use-getdate-within-a-user-defined-function-udf.html

--
Plamen Ratchev
http://www.SQLStudio.com


Submitted via EggHeadCafe - Software Developer Portal of Choice
How to display a Gravatar Image with 100 Percent Client Script Code
http://www.eggheadcafe.com/tutorials/aspnet/3c8a04cd-471e-48b7-8dcc-b0877c10ecb4/how-to-display-a-gravatar.aspx
 | 
Pages: 1
Prev: SQL connections
Next: SP parameter optional