How to get data of current date using sum in SQL?

select datename(month, '2020-06-12'), datename(month, getdate()); --1week SELECT SUM(amount) AS total FROM tbl_expense_record WHERE dateonly >= dateadd(week, -1, cast(getdate() as date)) and dateonly <= cast(getdate() as date) --1month SELECT SUM(amount) AS total FROM tbl_expense_record WHERE dateonly >= dateadd(month, -1, cast(getdate() as date)) and dateonly <= cast(getdate() as date) --build muscle memory (it is always safe to check for < date+1 instead of <= date) --1month SELECT SUM(amount) AS total FROM tbl_expense_record WHERE dateonly >= dateadd(month, -1, cast(getdate() as date)) and dateonly < dateadd(day, 1, cast(getdate() as date)); --6months SELECT SUM(amount) AS total FROM tbl_expense_record WHERE dateonly >= dateadd(month, -6, cast(getdate() as date)) and dateonly < dateadd(day, 1, cast(getdate() as date)); if not exists ( select * FROM tbl_expense_record WHERE dateonly >= dateadd(month, -1, cast(getdate() as date)) and dateonly < dateadd(day, 1, cast(getdate() as date)) ) begin select 'no rows within the last month' end else begin select 'there are rows within the last month'; end; 

Examples:

declare @tbl_expense_record table(dateonly date, amount decimal(9,2)); insert into @tbl_expense_record values ('20200501', 10), ('20200612', 10), ('20200613', 11), ('20200614', 12), ('20200710', 5), ('20200720', 6), ('20200820', 20), ('20200825', 30), ('20201102', 1), ('20201110', 2), ('20201120', 3); --aggregation per month, for all rows select year(dateonly) as _year, month(dateonly) as _month, sum(amount) as sum_amount_per_month, count(*) as rows_per_month from @tbl_expense_record group by year(dateonly), month(dateonly); --aggregation per iso-week select year(dateonly) as _year, datepart(iso_week, dateonly) as _isoweek, sum(amount) as sum_amount_per_isoweek, count(*) as rows_per_isoweek from @tbl_expense_record group by year(dateonly), datepart(iso_week, dateonly); --aggregation per month, for all rows with a dateonly that falls in the last month --check the difference between aggregation per month earlier and this.. --filter rows first == where .... and then aggregate --there are two rows with dateonly > 06 november (the row at 05 is filtered out by the where clause) select year(dateonly) as _year, month(dateonly) as _month, sum(amount) as sum_amount_per_month, count(*) as rows_per_month from @tbl_expense_record where dateonly >= dateadd(month, -1, cast(getdate() as date)) and dateonly < dateadd(day, 1, cast(getdate() as date)) group by year(dateonly), month(dateonly); --aggregate per week diff from today/getdate() select datediff(week, getdate(), dateonly) as week_diff_from_today, dateadd(day, --datepart(weekday..) is used...account for @@datefirst setting / set datefirst 1-(@@datefirst+datepart(weekday, dateadd(week, datediff(week, getdate(), dateonly), cast(getdate() as date))))%7, dateadd(week, datediff(week, getdate(), dateonly), cast(getdate() as date))) as startofweek, dateadd(day, 6, --add 6 days to startofweek dateadd(day, --datepart(weekday..) is used...account for @@datefirst setting / set datefirst 1-(@@datefirst+datepart(weekday, dateadd(week, datediff(week, getdate(), dateonly), cast(getdate() as date))))%7, dateadd(week, datediff(week, getdate(), dateonly), cast(getdate() as date))) ) as endofweek, sum(amount) as sum_amount, count(*) as rows_within_week from @tbl_expense_record group by datediff(week, getdate(), dateonly);