How can I get the last 12 months from the current date PLUS extra days till 1st of the last month retrieved
Using DATEADD
and DATEDIFF
:
DECLARE @ThisDate DATE = '20150817'
SELECT DATEADD(YEAR, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @ThisDate), '19000101'))
For more common date routines, see this article by Lynn Pettis.
To use in your WHERE
clause:
DECLARE @ThisDate DATE = '20150817'
SELECT *
FROM <your_table>
WHERE
<date_column> >= DATEADD(YEAR, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @ThisDate), '19000101'))
How to get previous 12 months data in SQL Server and avoiding the current month
The following should work, evaluates to >='2020-04-01 00:00:00.000'
and <'2021-04-01 00:00:00.000'
(which encompasses to end of March 23:59:59)
where Datecolumn >=DateAdd(month, DateDiff(month, 0, DateAdd(month,-12,GetDate())), 0)
and dateColumn < DateAdd(month, DateDiff(month, 0, GetDate()), 0)
Get Result set in SQL for the last 12 months starting from current date
If you already have the Year and Month then you can filter the past 12 months like the following:
(This is T-SQL/SQL Server)
IF OBJECT_ID('tempdb..#Foo') IS NOT NULL DROP TABLE #Foo;
CREATE TABLE #Foo (
[Year] INT NOT NULL,
[Month] INT NOT NULL,
Bar VARCHAR(20)
);
INSERT INTO #Foo([Year], [Month], Bar) VALUES (2018, 1, 'NO');
INSERT INTO #Foo([Year], [Month], Bar) VALUES (2018, 12, 'NO');
INSERT INTO #Foo([Year], [Month], Bar) VALUES (2019, 1, 'NO');
INSERT INTO #Foo([Year], [Month], Bar) VALUES (2019, 2, 'NO');
INSERT INTO #Foo([Year], [Month], Bar) VALUES (2019, 3, 'YES');
INSERT INTO #Foo([Year], [Month], Bar) VALUES (2019, 4, 'YES');
INSERT INTO #Foo([Year], [Month], Bar) VALUES (2019, 5, 'YES');
INSERT INTO #Foo([Year], [Month], Bar) VALUES (2019, 12, 'YES');
INSERT INTO #Foo([Year], [Month], Bar) VALUES (2020, 1, 'YES');
INSERT INTO #Foo([Year], [Month], Bar) VALUES (2020, 2, 'YES');
INSERT INTO #Foo([Year], [Month], Bar) VALUES (2020, 3, 'YES');
SELECT [Year], [Month], Bar
FROM #Foo
WHERE [Year] > YEAR(DATEADD(YEAR, -1, GETDATE()))
OR ([Year] = YEAR(DATEADD(YEAR, -1, GETDATE()))
AND [Month] >= MONTH(DATEADD(YEAR, -1, GETDATE())))
How to get Past 12months with year from current date
If you want to include the year with the output date then the simplest method is to add 1 month to the current date within your loop. You can then retrieve the month and year from that date within the loop, something like this:
var theMonths = new Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December");var now = new Date();
for (var i = 0; i < 12; i++) { var future = new Date(now.getFullYear(), now.getMonth() + i, 1); var month = theMonths[future.getMonth()]; var year = future.getFullYear(); console.log(month, year);}
SQL : Extract last 12 months of data from SQL server when period is in the format of yyyymm
You can use the following query:
SELECT *
FROM YourTable
WHERE [A header] >= YEAR(DATEADD(month, -12, GETDATE())) * 100 + MONTH(DATEADD(month, -12, GETDATE()))
db<>fiddle
This is made worse by the fact you are storing dates as integers. Instead you should store the last day of the month as a date
type, then you can do
WHERE [A header] >= DATEADD(month, -12, GETDATE()))
find start and end date of previous 12 month from current date in python
You can use current_date.replace(day=1)
to get first day in current month.
And if you substract datetime.timedelta(days=1)
then you get last day in previous month.
And you can use again replace(day=1)
to get first day in previous month.
If you repeate it in loop then you can get first day and last day for all 12 months.
import datetime
current = datetime.datetime(2022, 5, 5)
start = current.replace(day=1)
for x in range(1, 13):
end = start - datetime.timedelta(days=1)
start = end.replace(day=1)
print(f'{x:2} |', start.date(), '|', end.date())
Result:
1 | 2022-04-01 | 2022-04-30
2 | 2022-03-01 | 2022-03-31
3 | 2022-02-01 | 2022-02-28
4 | 2022-01-01 | 2022-01-31
5 | 2021-12-01 | 2021-12-31
6 | 2021-11-01 | 2021-11-30
7 | 2021-10-01 | 2021-10-31
8 | 2021-09-01 | 2021-09-30
9 | 2021-08-01 | 2021-08-31
10 | 2021-07-01 | 2021-07-31
11 | 2021-06-01 | 2021-06-30
12 | 2021-05-01 | 2021-05-31
EDIT:
And if you use pandas then you can use pd.date_range()
but it can't for previous dates so you would have to first get '2021.04.05'
(for MS
) and '2021.05.05'
(for M
)
import pandas as pd
#all_starts = pd.date_range('2021.04.05', '2022.04.05', freq='MS')
all_starts = pd.date_range('2021.04.05', periods=12, freq='MS')
print(all_starts)
#all_ends = pd.date_range('2021.05.05', '2022.05.05', freq='M')
all_ends = pd.date_range('2021.05.05', periods=12, freq='M')
print(all_ends)
for start, end in zip(all_starts, all_ends):
print(start.to_pydatetime().date(), '|', end.to_pydatetime().date())
DatetimeIndex(['2021-05-01', '2021-06-01', '2021-07-01', '2021-08-01',
'2021-09-01', '2021-10-01', '2021-11-01', '2021-12-01',
'2022-01-01', '2022-02-01', '2022-03-01', '2022-04-01'],
dtype='datetime64[ns]', freq='MS')
DatetimeIndex(['2021-05-31', '2021-06-30', '2021-07-31', '2021-08-31',
'2021-09-30', '2021-10-31', '2021-11-30', '2021-12-31',
'2022-01-31', '2022-02-28', '2022-03-31', '2022-04-30'],
dtype='datetime64[ns]', freq='M')
2021-05-01 | 2021-05-31
2021-06-01 | 2021-06-30
2021-07-01 | 2021-07-31
2021-08-01 | 2021-08-31
2021-09-01 | 2021-09-30
2021-10-01 | 2021-10-31
2021-11-01 | 2021-11-30
2021-12-01 | 2021-12-31
2022-01-01 | 2022-01-31
2022-02-01 | 2022-02-28
2022-03-01 | 2022-03-31
2022-04-01 | 2022-04-30
EDIT:
I found out that standard module calendar can gives number of days and weeks in month.
weeks, days = calendar.monthrange(year, month)
Working example:
import calendar
year = 2022
month = 5
for number in range(1, 13):
if month > 1:
month -= 1
else:
month = 12
year -= 1
weeks, days = calendar.monthrange(year, month)
print(f'{number:2} | {year}.{month:02}.01 | {year}.{month:02}.{days}')
Result:
1 | 2022.04.01 | 2022.04.30
2 | 2022.03.01 | 2022.03.31
3 | 2022.02.01 | 2022.02.28
4 | 2022.01.01 | 2022.01.31
5 | 2021.12.01 | 2021.12.31
6 | 2021.11.01 | 2021.11.30
7 | 2021.10.01 | 2021.10.31
8 | 2021.09.01 | 2021.09.30
9 | 2021.08.01 | 2021.08.31
10 | 2021.07.01 | 2021.07.31
11 | 2021.06.01 | 2021.06.30
12 | 2021.05.01 | 2021.05.31
javascript date, get past 12 months
You want to subtract 1 month only so don't use i
coz it is dynamic value . The first loop is no need to subtract so do as below
Updated : you need to move current date declaration in loop , coz setMonth will change your date value so need to call current date everytime you loop...
function formatDate(date) {
date = new Date(date); var day = date.getDate(); var monthIndex = date.getMonth(); var year = date.getFullYear();
return day + ' ' + monthIndex + ' ' + year;}
for ( let i=11; i>=0; i--) { let now = new Date(); let newdate = now.setMonth(now.getMonth() - i); console.log(formatDate(newdate ));}
Related Topics
How to Convert Yyyy-Mm-Dd Hh:Mm:Ss to Mm-Dd-Yyyy Hh:Mm:Ss in SQL Server
Select All Rows With the Same Value in Column 1 But Different Values in Columns 2 and 3 Using SQL
Sql - Sum of Positive and Negative Numbers Using Subquery
Remove Numbers from String SQL Server
How to Convert Bigint (Unix Timestamp) to Datetime in SQL Server
Use Multiple Conflict_Target in on Conflict Clause
Concat(Column) Over(Partition By...)? Group-Concatentating Rows Without Grouping the Result Itself
How to Split a Record Based on Dates in SQL Server
Unable to Get Min and Max Time from a Datetime Column from Every Backday
How to Sum Up Time Field in SQL Server
Subtract Values from Two Columns in SQL Query
Mysql - Get All Records That Have More Than 1 Record for the Same Id
What's the Best Way to Capitalise the First Letter of Each Word in a String in SQL Server
How to Merge Rows on Specific Condition
Mysql Truncated Incorrect Double Value
Check If a Time Is Between Two Times (Time Datatype)
How to Store Output of a SQL Server Stored Procedure in a .Txt File