Shulou(Shulou.com)05/31 Report--

This article is about how sql server writes a general script to get a date from a year ago. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Question:

In database programming development, it is sometimes necessary to obtain the date of one year ago, so that it can be used as a demarcation point to query the corresponding amount of data before and after. For example:

1. I would like to query the amount of data before 0: 00 on the same day a year ago, and the amount of data since 0: 00 on the same day a year ago.

two。 Want to query the amount of data up to 24:00 on the same day a year ago, and the amount of data from 24:00 on the same day a year ago to the present.

3. I would like to query the amount of data before 0: 00 on the 1st of the same month a year ago, and the amount of data from 0: 00 on the 1st of the same month a year ago to the present.

4. I would like to query the amount of data up to 24:00 on the last day of the month a year ago, and the amount of data from 24:00 on the last day of the month a year ago to the present.

The specific query scenarios of the above four cases include statistics on the distribution of database data before the development of archive data script, and dynamic calculation of incremental growth in the past year according to the current date.

If you need to get a date a year ago every time you receive such a demand, and then rethink how to achieve it every time, or someone in the company has already written a script to implement it, but other colleagues encounter the same problem and rethink it again, this is a waste of time and energy, spending some energy on this detail, which is a bit like the feeling that you have to buy rice first if you want to cook but don't have rice. And it is easy to cause different people to write different scripts, which is not conducive to the principle of code standardization and standardization. When different people follow up, they have to think about what logic they wrote before. And if you re-develop the script, you need to re-test yourself, which is not conducive to improving work efficiency.

Solution:

First of all, let's briefly introduce the basic knowledge points:

Getdate () is to get the current date

Dateadd can add or subtract a date, which is used here to decrease the year by 1 [dateadd (year,-1, date string)], or to add 1 [dateadd (month, 1, date string)] to the month.

Convert can perform string interception and conversion operations on dates. Here, you can only intercept the year, month and day like 2019-07-17 [convert (varchar (10), date string, 120)], or you can only intercept the year-month part like 2019-07 [convert (varchar (7), date string, 120)].

Then there are four solutions to the above four problems:

1. At its simplest, subtract the current date by one year, and then intercept only the year, month and day.

two。 First add 1 day to the current date, then decrease it by 1 year, and finally intercept only the year, month and day.

3. First intercept the current date, then specify it as 01 of the current month, then reduce it by 1 year, and finally intercept only the year, month and day.

4. First intercept the current date, then specify it as 01 of the current month, then reduce it by 1 year, add 1 month, and finally intercept only the year, month and day.

Finally, you can use this time node directly in the query script condition:

Before querying the statistical time node: select count (*) from table where time field

= @ datePoint

Script:

/ * function: get the date one year ago. Author: zhang502219048 script Source: https://www.cnblogs.com/zhang502219048/p/11198789.html*/--1. As of 0 o'clock on the same day a year ago, declare @ datePoint datetime = convert (varchar (10), dateadd (year,-1, getdate (), 120) select @ datePoint as DatePointgo--2. As of 24:00 a year ago, declare @ datePoint datetime = convert (varchar (10), dateadd (year,-1, dateadd (day, 1, getdate ()), 120) select @ datePoint as DatePointgo--3. As of 00 o'clock on the 1st of the month a year ago, declare @ datePoint datetime = convert (varchar (10), dateadd (year,-1, convert (varchar (7), getdate (), 120) +'- 01') select @ datePoint as DatePointgo--4. As of the last day of the month a year ago, 24:00 declare @ datePoint datetime = convert (varchar (10), dateadd (month, 1, dateadd (year,-1, convert (varchar (7), getdate (), 120) +'- 01')) select @ datePoint as DatePointgo

The result of running the script:

Thank you for reading! This is the end of this article on "how to write a general script for sql server to get a date a year ago". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.