populate date field based on another date field

Question

Post reply

populate date field based on another date field

Jules Tidsey

SSC Veteran

Points: 216
More actions
July 25, 2005 at 9:57 am

#165085

Need to automatically populate a date field via stored procedure. I'm happy to do it from todays date but not sure how to do it from another date field. Following script puts it 14 days from today, I now need it to do 14 days from another field. Other field called OPENDT, same table.
IF (not exists(select diwor from MATTER_DATES
    where MATTER_DATES.MATTER_DIWOR=@matterDiwor
     and MATTER_DATES.DATE_TYPE_CODE='INIREP'))
    begin
     execute sp_diwor 'MATTER_DATES',@diwor output
    INSERT INTO MATTER_DATES(DIWOR,DATE_TYPE_CODE,MATTER_DIWOR,ACTUAL_DATE,ESTIMATED_DATE,NOTE)
      values (@diwor,'INIREP', @matterdiwor, null, getdate()+14, null)
    end

Viewing 5 posts - 1 through 4 (of 4 total)

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

What is the value for OPENDT? Is it populated from default constraint.

Depending on what your trying to do. Remember a default has to be deterministic.

This does not work

Create table temp (pk int identity, Test1 Datetime default(getdate()), Test2 Datetime Default(dateadd(dd,Test1, 14))

Server: Msg 128, Level 15, State 1, Line 1

The name 'Test1' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

Please post your table definition. (DDL)

So we can see the entire table structure.

Farrell Keough SSCoach Points: 17414 More actions · Answer 2

Here's a potential option:

IF( NOT EXISTS( SELECT diwor FROM MATTER_DATES

WHERE MATTER_DATES.MATTER_DIWOR = @matterDiwor

AND MATTER_DATES.DATE_TYPE_CODE = 'INIREP'))

BEGIN

EXECUTE sp_diwor 'MATTER_DATES',@diwor OUTPUT

INSERT INTO MATTER_DATES( DIWOR, DATE_TYPE_CODE, MATTER_DIWOR, ACTUAL_DATE, ESTIMATED_DATE, NOTE)

SELECT @diwor, 'INIREP', @matterdiwor, NULL, DATEADD( day, 14, OPENDT), NULL FROM TableOfInterest WHERE Constraint(s)...

END

(I do not know the name of your table or what constraints you would need to get the correct OPENDT).

You may also need to consider the following for if the two week period falls upon a weekend. You can run the code below and see what you get. If it is beneficial, you can put it into your SELECT statement above.

SELECT CASE

WHEN DATENAME( weekday, DATEADD( day, 14, '07/02/2005')) = 'Saturday'

THEN DATEADD( day, 16, '07/02/2005')

WHEN DATENAME( weekday, DATEADD( day, 14, '07/02/2005')) = 'Sunday'

THEN DATEADD( day, 15, '07/02/2005')

ELSE DATEADD( day, 14, '07/02/2005')

END AS 'Two Week Date Configured'

SELECT DATENAME( weekday, '07/02/2005') AS 'Original Date'

SELECT DATEADD( day, 14, '07/02/2005') AS 'Two Week Date'

SELECT DATENAME( weekday, DATEADD( day, 14, '07/02/2005')) AS 'Two Week Day'

SELECT DATENAME( weekday, DATEADD( day, 16, '07/02/2005')) AS 'Two Week Day Configured'

I wasn't born stupid - I had to study.

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 3

DATEADD() is the function you need to use to add days to a date. (It's also the same function you use to subtract days).

As Farrell said DATEADD( day, 14, OPENDT) would give you the date 14 days after the value in OPENDT.

Julian Kuiters
juliankuiters.id.au

Jules Tidsey SSC Veteran Points: 216 More actions · Answer 4

The DateAdd() was what I needed, and the weekday part is even better. Thanks.

RayM - the value for OPENDT? is the day the case was opened on the system and is automatically populated by the software.