Function to get last Monday of current week

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]" wrote in message

>> trying to create a function to return the last Monday of a current week?
>
> 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.
>
 >> Stay informed about: 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:

> 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]" wrote in message
>
> >> trying to create a function to return the last Monday of a current week?
> >
> > 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.
> >
>
>
>
 >> Stay informed about: Function to get last Monday of current week 

> my problem is the function itself?

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
 >> Stay informed about: Function to get last Monday of current week 

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



"Aaron Bertrand [SQL Server MVP]" wrote in message

>> my problem is the function itself?
>
> 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
>
 >> Stay informed about: Function to get last Monday of current week 

You can pass the current date as parameter to the function:
http://sqlserver2000.databases.aspfaq.com/how-do-i-use-getdate-within-...ser-def

--
Plamen Ratchev
http://www.SQLStudio.com
 >> Stay informed about: Function to get last Monday of current week 

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]" wrote in message


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-...ser-def

--
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-b0...c10ecb4
 >> Stay informed about: Function to get last Monday of current week 

The statement RETURN (DATEADD(wk, DATEDIFF(wk, 0,@CURRENTDATE),0))
doesn't return the date of last monday.

I get the value for ,@CURRENTDATE = 11 03 2010, and the whole thing return 08 mar 2010, which is THIS monday, not the last one.

DBA100.



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]" wrote in message


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-...ser-def

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

On Wednesday, March 10, 2010 11:19 AM
DBA 100 wrote:

Some comment.
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.


Submitted via EggHeadCafe - Software Developer Portal of Choice
More Fun with Fluent NHibernate Automapping
http://www.eggheadcafe.com/tutorials/aspnet/50aa9259-6dbb-4d16-9639-81...2171b00
 >> Stay informed about: Function to get last Monday of current week 

The statement RETURN (DATEADD(wk, DATEDIFF(wk, 0,@CURRENTDATE),0))
doesn't return the date of last monday.

I get the value for ,@CURRENTDATE = 11 03 2010, and the whole thing return 08 mar 2010, which is THIS monday, not the last one.

DBA100.



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]" wrote in message


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-...ser-def

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

On Wednesday, March 10, 2010 11:19 AM
DBA 100 wrote:

Some comment.
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.

On Wednesday, March 10, 2010 11:22 AM
DBA 100 wrote:

date return
The statement RETURN (DATEADD(wk, DATEDIFF(wk, 0,@CURRENTDATE),0))
doesn't return the date of last monday.

I get the value for ,@CURRENTDATE = 11 03 2010, and the whole thing return 08 mar 2010, which is THIS monday, not the last one.

DBA100.


Submitted via EggHeadCafe - Software Developer Portal of Choice
Adding WCF Service References
http://www.eggheadcafe.com/tutorials/aspnet/a1647f10-9aa4-4b0c-bbd9-df...a9fab8e
 >> Stay informed about: Function to get last Monday of current week 

The statement RETURN (DATEADD(wk, DATEDIFF(wk, 0,@CURRENTDATE),0))
doesn't return the date of last monday.

I get the value for ,@CURRENTDATE = 11 03 2010, and the whole thing return 08 mar 2010, which is THIS monday, not the last one.

DBA100.



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]" wrote in message


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-...ser-def

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

On Wednesday, March 10, 2010 11:19 AM
DBA 100 wrote:

Some comment.
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.

On Wednesday, March 10, 2010 11:22 AM
DBA 100 wrote:

date return
The statement RETURN (DATEADD(wk, DATEDIFF(wk, 0,@CURRENTDATE),0))
doesn't return the date of last monday.

I get the value for ,@CURRENTDATE = 11 03 2010, and the whole thing return 08 mar 2010, which is THIS monday, not the last one.

DBA100.

On Wednesday, March 10, 2010 11:22 AM
DBA 100 wrote:

statement.
The statement RETURN (DATEADD(wk, DATEDIFF(wk, 0,@CURRENTDATE),0))
doesn't return the date of last monday.

I get the value for ,@CURRENTDATE = 11 03 2010, and the whole thing return 08 mar 2010, which is THIS monday, not the last one.

DBA100.


Submitted via EggHeadCafe - Software Developer Portal of Choice
Making Silverlight Emulate Synchronous Requests
http://www.eggheadcafe.com/tutorials/aspnet/91f69224-3da5-4959-9901-c5...7c9b184
 >> Stay informed about: Function to get last Monday of current week 

I'd say that is last Monday but if you want the Monday before that try

RETURN (DATEADD(wk, DATEDIFF(wk, 0,CURRENT_TIMESTAMP)-1,0))


John


On Wed, 10 Mar 2010 08:22:36 -0800, DBA 100 wrote:

>The statement RETURN (DATEADD(wk, DATEDIFF(wk, 0,@CURRENTDATE),0))
> doesn't return the date of last monday.
>
>I get the value for ,@CURRENTDATE = 11 03 2010, and the whole thing return 08 mar 2010, which is THIS monday, not the last one.
>
>DBA100.
>
>
>
>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]" wrote in message
>
>
>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
 >> Stay informed about: Function to get last Monday of current week