SQL default value for next Monday

Question

SQL default value for next Monday

gideon.e

SSC Veteran

Points: 273
More actions
November 16, 2018 at 8:45 pm

#368953

I am using sql server through Visual Studio. I would like to set the default value of a column to next Monday. What do I put in the default column?
Regards

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

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

gideon.e - Friday, November 16, 2018 8:45 PM
I am using sql server through Visual Studio. I would like to set the default value of a column to next Monday. What do I put in the default column?
Regards

Here is an example that should get you passed this hurdle, demonstrated as a calculated column.
😎

USE TEEST; GO SET NOCOUNT ON; -- DATE VARIABLE, SET TO THE MONDAY THE 19 OF NOVEMBER DECLARE @TDATE DATE = '20181119'; -- LOGIC BREAKDOWN SELECT -- NUMBER OF DAYS SINCE 1900-01-01 DATEDIFF(DAY,0,@TDATE) AS DFZD -- THE PREVIOUS NUMBER MOD 7, 0 IS MONDAY ,DATEDIFF(DAY,0,@TDATE) % 7 AS DFZD_MOD7 -- THE REMAINDER SUBTRACTED FROM 7 DAYS IN A WEEK ,7 - (DATEDIFF(DAY,0,@TDATE) % 7) AS NDTONXTMD -- PUTTING IT ALL TOGETHER ,DATEADD(DAY,(7 - (DATEDIFF(DAY,0,GETDATE()) % 7)),CONVERT(DATE,GETDATE(),0)) AS NEXT_MONDAY ;

-- CALCULATED COLUMN EXAMPLE DECLARE @test-2 TABLE ( TEST_ID INT NOT NULL ,NEXT_MONDAY AS (DATEADD(DAY,(7 - (DATEDIFF(DAY,0,GETDATE()) % 7)),CONVERT(DATE,GETDATE(),0))) );

-- DEMONSTRATION INSERT INTO @test-2(TEST_ID) VALUES (1); SELECT T.TEST_ID ,T.NEXT_MONDAY FROM @test-2 T;

Louis Hillebrand SSCarpal Tunnel Points: 4982 More actions · Answer 2

You can create a function that return the datetime value of then next Monday and use that as default for the column in SQL.

CREATE FUNCTION dbo.Next_Monday() RETURNS datetime AS BEGIN; RETURN ( SELECT DATEADD(DAY,(7 - (DATEDIFF(DAY,0,GETDATE()) % 7)),CONVERT(DATE,GETDATE(),0)) ); END; GO -- USAGE EXAMPLE

CREATE TABLE dbo.Test(
Test_Id int NOT NULL,
Next_Monday datetime NOT NULL DEFAULT dbo.Next_Monday()
);

INSERT dbo.Test(Test_Id) VALUES (1)

SELECT * FROM dbo.Test

If you need the input in visual studio, create a function in VS and use that als default for the column in the dataset.

Louis.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 3

Louis Hillebrand - Saturday, November 17, 2018 6:30 AM
You can create a function that return the datetime value of then next Monday and use that as default for the column in SQL.
CREATE FUNCTION dbo.Next_Monday() RETURNS datetime AS BEGIN; RETURN ( SELECT DATEADD(DAY,(7 - (DATEDIFF(DAY,0,GETDATE()) % 7)),CONVERT(DATE,GETDATE(),0)) ); END; GO -- USAGE EXAMPLE
CREATE TABLE dbo.Test(
Test_Id int NOT NULL,
Next_Monday datetime NOT NULL DEFAULT dbo.Next_Monday()
);
INSERT dbo.Test(Test_Id) VALUES (1)
SELECT * FROM dbo.Test

If you need the input in visual studio, create a function in VS and use that als default for the column in the dataset.
Louis.

No need to create a function Louise as the formula is deterministic, works both as a default an as a calculated column.
😎

The ambiguity here is the missing temporal reference, next Monday from which date?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

gideon.e - Friday, November 16, 2018 8:45 PM
I am using sql server through Visual Studio. I would like to set the default value of a column to next Monday. What do I put in the default column?
Regards

Allow me to ask that which has not been asked so far... What is the datatype of the column that you want to apply the default to?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

moreno Valued Member Points: 71 More actions · Answer 5

Next Monday:
SELECT DATEADD(ww, DATEDIFF(ww,0,current_timestamp+6), 0)

Explanation:
Date 0 is 1900-01-01, which is a monday, so adding weeks to date 0 returns always a monday.
Adding 6 days to current date returns a day of next week.
Calculate the number of weeks between 1900-01-01 and next week.
Adding this number of weeks to 1900-01-01 returns the monday of next week

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 6

moreno - Sunday, November 18, 2018 4:12 AM
Next Monday:
SELECT DATEADD(ww, DATEDIFF(ww,0,current_timestamp+6), 0)
Explanation:
Date 0 is 1900-01-01, which is a monday, so adding weeks to date 0 returns always a monday.
Adding 6 days to current date returns a day of next week.
Calculate the number of weeks between 1900-01-01 and next week.
Adding this number of weeks to 1900-01-01 returns the monday of next week

Can you elaborate further on this please? When I run this for a Monday, I get
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
😎

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

Eirikur Eiriksson - Sunday, November 18, 2018 8:00 AM
moreno - Sunday, November 18, 2018 4:12 AM
Next Monday:
SELECT DATEADD(ww, DATEDIFF(ww,0,current_timestamp+6), 0)
Explanation:
Date 0 is 1900-01-01, which is a monday, so adding weeks to date 0 returns always a monday.
Adding 6 days to current date returns a day of next week.
Calculate the number of weeks between 1900-01-01 and next week.
Adding this number of weeks to 1900-01-01 returns the monday of next week
Can you elaborate further on this please? When I run this for a Monday, I get
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
😎

I don't get that error. It runs ok for me.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

moreno Valued Member Points: 71 More actions · Answer 8

Eirikur Eiriksson - Sunday, November 18, 2018 8:00 AM
moreno - Sunday, November 18, 2018 4:12 AM
Next Monday:
SELECT DATEADD(ww, DATEDIFF(ww,0,current_timestamp+6), 0)
Explanation:
Date 0 is 1900-01-01, which is a monday, so adding weeks to date 0 returns always a monday.
Adding 6 days to current date returns a day of next week.
Calculate the number of weeks between 1900-01-01 and next week.
Adding this number of weeks to 1900-01-01 returns the monday of next week
Can you elaborate further on this please? When I run this for a Monday, I get
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
😎

Hi Eirikur

This is the example I run on my environment ( SQL 2014 )
declare @d datetime = '20181119'
SELECT DATEADD(ww, DATEDIFF(ww,0,@d+6), 0) as NextMonday
I tried it with many different days and never had an error.

Louis Hillebrand SSCarpal Tunnel Points: 4982 More actions · Answer 9

Eirikur Eiriksson - Saturday, November 17, 2018 7:29 AM
No need to create a function Louise as the formula is deterministic, works both as a default an as a calculated column.
😎
The ambiguity here is the missing temporal reference, next Monday from which date?

Eirikur,
Yes, you can use the formula as a default for the column, but in my humble opinion, using a formula with a clear name as a default in this situation makes it more clear what the purpose of the default is.
I was only looking at the next Monday from now, you can change the CURRENT_TIMESTAMP to a datetime variable to get the NextMonday from any date, I agree with you on that.

Louis.