CODEWITHSUNDEEP
sqlsql-serversqlcommand
thompsonrapier
thompsonrapier

Reputation: 175

How to extract data from 1st day of the month till today's date -1

I am trying to extract all the data that has the datetime from 1st day of the month till yesterday, for example:

01/06/2017 - 22/06/2017

I have used this code:

Select *
from testDb.dbo.Company1
WHERE MONTH(CreatedDate) = MONTH(dateadd(dd, -1, GetDate()))  AND 
      YEAR(CreatedDate) = YEAR(dateadd(dd, -1, GetDate()))

EDIT

My column for CreatedDate, its data type is DateTime. Not sure if there is any difference tho.

But this prints out all the data from 01/06/2017 - 23/06/2017. What code should I write such that it will print all data till (today's date-1)? Thanks for the help and have a great day!

Upvotes: 1

Views: 1832

Answers (4)

Maverick Sachin
Maverick Sachin

Reputation: 883

Try this query --

SELECT *
FROM testDb.dbo.Company1
WHERE DATEPART(Month, CreatedDate) = Datepart(Month, GETDATE())
    AND DATEPART(Day, CreatedDate) <= DATEPART(Day, Getdate())

Edit:

SELECT *
    FROM testDb.dbo.Company1
    WHERE DATEPART(Month, CreatedDate) = Datepart(Month, GETDATE())
        AND DATEPART(Day, CreatedDate) < DATEPART(Day, Getdate())

Edit 2:

SELECT CONVERT(VARCHAR(30),CreatedDate,120) As [CreatedDate]
    FROM testDb.dbo.Company1
    WHERE DATEPART(Month, CreatedDate) = Datepart(Month, GETDATE())
        AND DATEPART(Day, CreatedDate) < DATEPART(Day, Getdate())

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I think this where clause does what you want:

where createdDate >= dateadd(month, 0, datediff(month, 0, getdate())) and
      createdDate < cast(getdate() as date)

Upvotes: 0

asmgx
asmgx

Reputation: 8054

This though wont work when today is the first day of the month

SELECT *
FROM your_table
WHERE createdDate BETWEEN 
CAST('1 ' + DateName(month, GetDate()) + ' ' +Year(GetDate()) as datetime) 
AND DATEADD(day, -1, GETDATE() )

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

Instead of comparing the month and year components separately, try bounding your result set using a range of two complete data points, one being the start of the month, and the other being yesterday.

SELECT *
FROM testDb.dbo.Company1
WHERE
    CreatedDate BETWEEN DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AND
                        DATEADD(day, -1, CAST(GETDATE() AS date))

Demo

Upvotes: 1

Related Questions