Geek Answers Handbook

Best way to store quarter and year in SQL Server?

What would be the best way to store Quarter and Year in a database? I have a payment table, and I need to set a quarter / year so that it is easy to indicate for which quarter the payment was made.

I thought:

a) adding two int columns for each payment
b) adding another table and adding possible values ​​up to 5 years in advance and using an identifier to join this table with payments.

What are the other options? Some may be better and / or easier to maintain. This database will be used with the C # program.

+3
source share
8 answers

If you need to use a separate year and quarter instead of a date (since you have certain reporting requirements), I would go for tinyint for a quarter and smallint for a year and save them in PAYMENT .

I will not store it in another table. This is bad because:

  • You must make sure that you have a sufficient number of years / quarters.
  • You need to join and use the foreign key

If you save data with a record, this will help improve read performance. Your table may be small, but it is always useful to remember performance.

Why

Suppose you need to get

all payments in a certain quarter where the payment was more than a specific amount, and the client is a specific client

In this case, you will need a coverage index for all elements and it still will not help, since your request relates to a certain quarter, and not to a quarter of a year. However, having data in a table will help with an easier implementation plan.

+4
source

I have always used the datetime value from January 1 / April / July / October, representing every quarter. Makes calculating the start and end dates of a quarter simple:

  • Start date: datetime column.
  • Final data: dateadd(month,3,quarterColumn)

Another alternative would be ISO 8601 . Here is an ISO 8601 profile for use in Internet protocols: RFC 3339 (proposed standard).

The presentation of ISO 8601 for each quarter of 2011 is as follows:

  • 2011-01-01 / P3M
  • 2011-04-01 / P3M
  • 2011-07-01 / P3M
  • 2011-10-01 / P3M

The above indicate the duration, starting date and duration (in this case, 3 months).

The advantage of ISO 8601 date and time formats is that strings are (A) human-readable, (B) they are sorted correctly, (C) they are easily parsed, and (D) its international standard.

Some people “extend” the ISO 8601 notation, where the week of the year looks like 2011W32 (32nd week of 2011), to a quarter notation. Using this unofficial extension, the quarters of 2011 look like this:

  • 2011Q1
  • 2011Q2
  • 2011Q3
  • 2011Q4
+3
source

How about using calculated columns based on the payment date? I would rather do this than have a date and a quarter / year that can go out of sync. On the other hand, I believe that it is possible that you may need to have a different year / quarter than the date indicates, in which case you will need to separate them. I would at least think about using computed columns, although this is the best way to ensure integrity.

+2
source

For something so simple, I would just save 2 int columns and to create (main) dates using dateadd when you need to use date ranges.

Another option is a column with one date for which you can save the first day in the quarter, so 4 dates in the year will be January 1, April 1, July 1, October 1. You can extract quarter, year easily using datepart Q and Y.

+1
source

How about two ints, one for a year and one for a quarter (1-4). Is that what you mean by the "a" option?

Option b will work, but you must remember to maintain a table every year or so.

+1
source

I agree that the two ints are perfect.

I would add an index consisting of both columns if you need to sort or filter by year and quarter.

+1
source

You can even use one tinyint . This is enough for storage in the form of YYQ, for example 111, 112, 113, 114, 121 ... for several years.

+1
source

Saving the quarter and year in the database depends on how your billing information is organized. Examples include: how many different payment values ​​are inserted. Will the quarter / year ranges change? and etc.

One good way to determine the quarter / year range is to have a separate table with a DateTime field that identifies the quarter. You do not need to join the table, you just need to program in C # to find out if the range falls within a certain quarter of wages.

For instance:

 Table 1: Payments ----------------- paymentID (int) paymentAmount (double(7,2)) paymentDateTime (DateTime) Table 2: QuarterYear -------------------- quarterYearID (int) dateFrom (date) dateTo (date) quarter (tinyint) description (varchar)

Data examples

 paymentID | paymentAmount | paymentDateTime ------------------------------------------------ 1 | 20.24 | 2011-04-18 08:14:20 2 | 34.15 | 2011-04-19 07:42:15 3 | 51.87 | 2011-04-20 13:04:22 quarterYearID | dateFrom | dateTo | quarter | description ----------------------------------------------------------------- 1 | 2011-01-01 | 2011-03-31 | 1 | first quarter 2 | 2011-04-01 | 2011-06-30 | 2 | second quarter 3 | 2011-07-01 | 2011-09-31 | 3 | third quarter 4 | 2011-10-01 | 2011-12-31 | 4 | forth quarter

An example of a request for receiving all payments for "quarter 2" dateValue is a dynamically pulled variable from the payment table. C # will handle the dateValue value.

 SELECT quarter FROM QuarterYear WHERE cast('dateValue' AS date) BETWEEN dateFrom AND dateTo;
+1
source

More articles:


All Articles