If I want to add ‘period’ in the database (one month), and would like to make calculations with the first day of the period. What would I have to do? [closed]

  softwareengineering

1

I have a question.

I want to add ‘period’ in the database. A period would always be 1 month. How Would I be able to make calculations with the period?

For example I want to calculate the following:

A = Last day of Current Period – Last day of Previous Period

How would I be able to make the system understand what the last day of the period is? And the previous period?

That could be for example 28, 30 or 31. Could anyone please help me out.

In this case it would be:

31th of October – 30th of September

Thanks!

  • database
  • data
  • data-types

4

3

Calculating anything involves behavior. Database tables store data, not behavior. When dealing with dates, choose a solution that properly models all of the crazy rules around dates. There is no way to store data that calculates something. Instead, store data that describes the period.

Your program can query the database for this information. Based on the type of period and duration, you would need to write programming code to calculate this. Programming languages have built-in library functions or data types to handle date calculations. For example, if implementing this in the .NET technology stack, the DateTime type should be used.

You could write stored procedures in the database to handle the date calculations, but only choose this option if the database vendor has a good set of library functions for calculating dates.

0

Most modern SQL databases provide you with the functions for doing this. For example, with MS SQL Server, all you need for calculating what you asked for are the functions

  • YEAR, MONTH, DAY, DATEFROMPARTS, DATEADD and DATEDIFF

(See https://www.w3schools.com/sql/sql_ref_sqlserver.asp for a full reference or other databases).

Assumed you have a column myDate and want to calculate the value A from your question, this can be a little tricky with these functions. I would approach it this way:

  • add one month to myDate: DATEADD(month,1,mydate)

  • get the month and year from that, replace the day of the month by 1, and subtract one day:

     DATEADD(day,-1, DATEFROMPARTS(YEAR(DATEADD(month,1,mydate)), MONTH(DATEADD(month,1,mydate)), 1))

This gives you the last day of the current month.

Now do the same for DATEADD(month, -1, myDate), this gives you the last day of the previous period. Finally, use DATEDIFF to calculate the difference.

Of course, trying to cram all this stuff into one large SQL statement is not the best idea, hence you may consider to use a stored procedure where you can provide some variables for the intermediate values.

I am sure there is a simpler solution out there, but the approach above is almost straightforward and should not be to hard to understand.

LEAVE A COMMENT