Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

DATEPART and TRIGGER to post WEEK NUMBER

Posted on 2006-04-18
6
Medium Priority
?
754 Views
Last Modified: 2007-12-19
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.
0
Comment
Question by:Bauerchick
  • 3
  • 2
6 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16483940
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
 
LVL 6

Accepted Solution

by:
Imthiyaz_ph earned 2000 total points
ID: 16487713
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
 

Author Comment

by:Bauerchick
ID: 16489368
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16490317
>>Wouldnt the above trigger fire recursively? <<
By default Recursive triggers are off.
0
 
LVL 6

Expert Comment

by:Imthiyaz_ph
ID: 16495746
Sorry acperkins, i forgot abt that option. Thank u.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16503592
>>Sorry acperkins, i forgot abt that option. Thank u.<<
No problem.  And you are absolutely correct about being cautious.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question