DATEPART and TRIGGER to post WEEK NUMBER

How can I write a trigger to take the datetime object in one field - ie CONTACTDATE - and calculate the week of that date and update the field WEEK with the weeknumber of that date? I think it will use DATEPART(ww, contactdate) or something like that? I really don't know sql very well.
BauerchickAsked:
Who is Participating?
 
Imthiyaz_phCommented:
Hi acperkins

Wouldnt the above trigger fire recursively? I have a doubt.

CREATE TRIGGER trg_MyTrigger On YourTableName
For INSERT, UPDATE
As
If Update(CONTACTDATE)
Begin
  Update     YourTableName
  Set [WEEK] = DATEPART(week, CONTACTDATE)
  From     YourTableName t
        Inner Join Inserted i On t.YourPrimaryKeyHere = i.YourPrimaryKeyHere
End


If the column is in the same table, i would create a computed column with the formula "DATEPART(week, CONTACTDATE)" instead of using a trigger.
0
 
Anthony PerkinsCommented:
Something like this perhaps:


CREATE TRIGGER trg_MyTrigger On YourTableName

For INSERT, UPDATE

As

Update      YourTableName
Set [WEEK] = DATEPART(week, CONTACTDATE)
From      YourTableName t
       Inner Join Inserted i On t.YourPrimaryKeyHere = i.YourPrimaryKeyHere
0
 
BauerchickAuthor Commented:
Thanks to both of you, but I found that the calculation idea worked best. A big thanks to Imthiyaz_ph for the quick and extremely simple answer.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Anthony PerkinsCommented:
>>Wouldnt the above trigger fire recursively? <<
By default Recursive triggers are off.
0
 
Imthiyaz_phCommented:
Sorry acperkins, i forgot abt that option. Thank u.
0
 
Anthony PerkinsCommented:
>>Sorry acperkins, i forgot abt that option. Thank u.<<
No problem.  And you are absolutely correct about being cautious.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.