• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5281
  • Last Modified:

how to extract trimester out of date field

There are standard functions to get year, quarter, month, etc out of date field ( DATEPART functions). In Norway though we use often trimesters (i.e. 4 month periodes, 3 in one year). Does anyone have an idea/function to extract that out of date?
0
kvilimas
Asked:
kvilimas
  • 3
  • 2
2 Solutions
 
käµfm³d 👽Commented:
I suppose it would be something like:
CREATE FUNCTION GetTrimester(@dateVal DATETIME)
RETURNS INT
AS
BEGIN
  DECLARE @month int;

  SET @month = DATEPART(month, @dateVal);

  IF (@month <= 4)
     RETURN 1;
  ELSE IF (@month <= 8)
     RETURN 2;
  ELSE
     RETURN 3;
END;

Open in new window

0
 
StealthyDevCommented:
You can also use

((MONTH(MyDateField)-1) / 4) + 1

in your query.


Regards.
0
 
kvilimasAuthor Commented:
Hi senthurpandian. Is there something missing in your function? Truncation or something?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
StealthyDevCommented:
In my example - MyDateField is a column name of a table.

If you are running a query in a table, to get the second trimester result only, it can be used like this:

Select * from TestTable WHERE ((MONTH(MyDateField )-1) / 4) + 1 = 2

Assuming TestTable .MyDateField is a DateTime field.

Does this help you?

P.S:
Jan-Apr = 1
May-Aug = 2
Sep-Dec = 3

Regards.
0
 
StealthyDevCommented:
I would suggest to accept #30589065 as a solution,
0
 
käµfm³d 👽Commented:
I would suggest that your solution is only effective as long as one remembers the formula:  (MONTH(MyDateField )-1) / 4) + 1.  I contend that putting the logic into a user-defined function is more extensible.
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now