How to Get The First Day and The Last of Previous Month Using Sql

how to get the first day and the last of previous month using sql

This should do it:

--First day of last month
SELECT DATEADD(m,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()), 0))
--Last day of last month
SELECT DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

select first day of previous month from record in sql server

select dateadd(mm, datediff(mm, 0, StartDateTime) - 1, 0)
from <yourtable>

Or, as Aaron Bertrand suggested in comments, more clear way

select dateadd(month, datediff(month, 0, StartDateTime) - 1, 0)
from <yourtable>

SQL FIDDLE EXAMPLE

How do I find the first and last day of the previous month using SQL?

Try

DATE(YEAR(CURRENT_DATE)||'-'||MONTH(CURRENT_DATE)||'-1') - 1 MONTH

and

DATE(YEAR(CURRENT_DATE)||'-'||MONTH(CURRENT_DATE)||'-1') - 1 DAY

How to retrieve first and last day of the previous month in Google BigQuery?

Try this:

WHERE FirstSold_Date BETWEEN date_trunc(date_sub(current_date(), interval 1 month), month) AND last_day(date_sub(current_date(), interval 1 month), month)

MySQL: Select with first and last day of previous month

in MYSQL you can try the below

First day of Previous Month

select last_day(curdate() - interval 2 month) + interval 1 day

Last day of Previous Month

select last_day(curdate() - interval 1 month)

First day of Current Month

select last_day(curdate() - interval 1 month) + interval 1 day

Last day of Current Month

select last_day(curdate())

How can I select the first day of a month in SQL?

SELECT DATEADD(month, DATEDIFF(month, 0, @mydate), 0) AS StartOfMonth

Get first day of previous month in YYYY-MM-DD format

You can use the EOMONTH function for this. This function is supposed to return the end of month so add one day to result:

SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(), -2)) -- returns 2019-07-01 on 2019-08-28 01:02:03.004

Wrap the above expression inside FORMAT(..., 'yyyy-MM-dd') to format the result.

SQL date scenario for getting previous month's data to-date on the 1st of month, then drop last month

WITH dates AS (SELECT * FROM VALUES ('2021-03-01'),
     ('2021-03-02'),
     ('2021-03-03'),
     ('2021-02-01'),
     ('2021-02-02') v(day))
SELECT day
    ,DATE_TRUNC('month',DATEADD('day',-1, day)) AS from_date
    ,DATEADD('month',1, from_date ) AS to_date
FROM dates
ORDER BY 1;

we get the results:

DAY         FROM_DATE                   TO_DATE
2021-02-01  2021-01-01 00:00:00.000     2021-02-01 00:00:00.000
2021-02-02  2021-02-01 00:00:00.000     2021-03-01 00:00:00.000
2021-03-01  2021-02-01 00:00:00.000     2021-03-01 00:00:00.000
2021-03-02  2021-03-01 00:00:00.000     2021-04-01 00:00:00.000
2021-03-03  2021-03-01 00:00:00.000     2021-04-01 00:00:00.000

---

Related Topics