Get the records of last month in SQL server

Question

I want to get the records of last month based on my db table  member  field  date created    What s the sql to do this   For clarification   last month - 1 8 2009 to 31 8 2009  If today is 3 1 2010  I ll need to get the records of 1 12 2009 to 31 12 2009

User · Answer

Add the options which have been provided so far won't use your indexes at all.

Something like this will do the trick, and make use of an index on the table (if one exists).

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = dateadd(mm, -1, getdate())
SET @StartDate = dateadd(dd, datepart(dd, getdate())*-1, @StartDate)
SET @EndDate = dateadd(mm, 1, @StartDate)

SELECT *
FROM Member
WHERE date_created BETWEEN @StartDate AND @EndDate

User · Answer

declare  PrevMonth as nvarchar 256   SELECT  PrevMonth   DateName  month DATEADD mm  DATEDIFF mm  0  getdate    - 1  0          -    substring DateName  Year  getDate      3 4

User · Answer

DECLARE  StartDate DATETIME   EndDate DATETIME SET  StartDate   DATEADD mm  DATEDIFF mm 0 getdate   -1  0  SET  EndDate   DATEADD mm  1   StartDate   SELECT   FROM Member WHERE date created BETWEEN  StartDate AND  EndDate   An upgrade to mrdenny s solution  this way you get exactly last month from YYYY-MM-01

User · Answer

select   from  member  where DatePart  m   date created    DatePart  m   DateAdd  m   -1  getdate     AND DatePart  yyyy   date created    DatePart  yyyy   DateAdd  m   -1  getdate

User · Answer

If you are looking for previous month data   date date created  gt  date sub date format curdate     Y- m-01   interval 1 month  and  date date created  lt  date sub date format curdate     Y- m-01   interval 1 day    This will also work when the year changes  It will also work on MySQL

User · Answer

DECLARE  StartDate DATETIME   EndDate DATETIME SET  StartDate   dateadd mm  -1  getdate    SET  StartDate   dateadd dd  datepart dd  getdate    -1   StartDate  SET  EndDate   dateadd mm  1   StartDate  set  StartDate   DATEADD dd  1    StartDate

User · Answer

SQL query to get record of the present month only  SELECT   FROM CUSTOMER WHERE MONTH DATE    MONTH CURRENT TIMESTAMP  AND YEAR DATE    YEAR CURRENT TIMESTAMP

User · Answer

DECLARE  StartDate DATETIME   EndDate DATETIME     SET  StartDate   DATEADD mm  DATEDIFF mm  0  getdate    - 1  0      SET  EndDate   dateadd dd  -1  DATEADD mm  1   StartDate    SELECT   FROM Member WHERE date created BETWEEN  StartDate AND  EndDate    and another upgrade to mrdenny s solution  It gives the exact last day of the previous month as well

User · Answer

A simple query which works for me is   select   from table where DATEADD month  1 DATEFIELD     getdate

User · Answer

SELECT    FROM Member WHERE DATEPART m  date created    DATEPART m  DATEADD m  -1  getdate     AND DATEPART yyyy  date created    DATEPART yyyy  DATEADD m  -1  getdate       You need to check the month and year

User · Answer

DECLARE  curDate INT   datepart  Month GETDATE    IF   curDate   1      BEGIN         select   from Featured Deal         where datepart  Month Created Date  12 AND datepart Year Created Date     datepart Year GETDATE   -1       END ELSE     BEGIN         select   from Featured Deal         where datepart  Month Created Date   datepart  Month GETDATE   -1  AND datepart Year Created Date    datepart Year GETDATE         END

User · Answer

All the existing  working  answers have one of two problems   They will ignore indices on the column being searched The will  potentially  select data that is not intended  silently corrupting your results   1  Ignored Indices  For the most part  when a column being searched has a function called on it  including implicitly  like for CAST   the optimizer must ignore indices on the column and search through every record   Here s a quick example  We re dealing with timestamps  and most RDBMSs tend to store this information as an increasing value of some sort  usually a long or BIGINTEGER count of milli- nanoseconds   The current time thus looks is stored like this  1402401635000000  -- 2014-06-10 12 00 35 000000 GMT  You don t see the  Year  value   2014   in there  do you   In fact  there s a fair bit of complicated math to translate back and forth   So if you call any of the extraction date part functions on the searched column  the server has to perform all that math just to figure out if you can include it in the results   On small tables this isn t an issue  but as the percentage of rows selected decreases this becomes a larger and larger drain   Then in this case  you re doing it a second time for asking about MONTH    well  you get the picture  2  Unintended data  Depending on the particular version of SQL Server  and column datatypes  using BETWEEN  or similar inclusive upper-bound ranges   lt    can result in the wrong data being selected   Essentially  you potentially end up including data from midnight of the  quot next quot  day  or excluding some portion of the  quot current quot  day s records  What you should be doing  So we need a way that s safe for our data  and will use indices  if viable    The correct way is then of the form  WHERE date created  gt    startOfPreviousMonth AND date created  lt   startOfCurrentMonth  Given that there s only one month   startOfPreviousMonth can be easily substituted for derived by  DATEADD month  -1   startOCurrentfMonth   If you need to derive the start-of-current-month in the server  you can do it via the following  DATEADD month  DATEDIFF month  0  CURRENT TIMESTAMP   0   A quick word of explanation here   The initial DATEDIFF      will get the difference between the start of the current era  0001-01-01 - AD  CE  whatever   essentially returning a large integer   This is the count of months to the start of the current month   We then add this number to the start of the era  which is at the start of the given month  So your full script could should look similar to the following  DECLARE  startOfCurrentMonth DATETIME SET  startOfCurrentMonth   DATEADD month  DATEDIFF month  0  CURRENT TIMESTAMP   0   SELECT   FROM Member WHERE date created  gt   DATEADD month  -1   startOfCurrentMonth  -- this was originally    misspelled       AND date created  lt   startOfCurrentMonth  All date operations are thus only performed once  on one value  the optimizer is free to use indices  and no incorrect data will be included

User · Answer

Last month consider as till last day of the month  31 01 2016 here last day of the month would be 31 Jan  which is not similar to last 30 days   SELECT CONVERT DATE  DATEADD DAY -DAY GETDATE    GETDATE

User · Answer

You can get the last month records with this query   SELECT   FROM dbo member d  WHERE  CONVERT DATE  date created 101  gt  CONVERT DATE DATEADD m  datediff m  0  current timestamp -1  0    and CONVERT DATE  date created 101   lt  CONVERT DATE  DATEADD m  datediff m  0  current timestamp -1  0  101

User · Answer

The way I fixed similar issue was by adding Month to my SELECT portion  Month DATEADD day Created Date  1971 12 31   As Month   and than I added WHERE statement  Month DATEADD day Created Date  1971 12 31     month getdate   -1

User · Answer

WHERE      date created  gt   DATEADD MONTH  DATEDIFF MONTH  31  CURRENT TIMESTAMP   0      AND date created  lt  DATEADD MONTH  DATEDIFF MONTH  0  CURRENT TIMESTAMP   0

User · Answer

SELECT   FROM Member WHERE month date created    month NOW   - INTERVAL 1 MONTH

User · Answer

One way to do it is using the DATEPART function   select field1  field2  fieldN from TABLE where DATEPART month  date created    4  and DATEPART year  date created    2009   will return all dates in april  For last month  ie  previous to current month  you can use GETDATE and DATEADD as well   select field1  field2  fieldN from TABLE where DATEPART month  date created      DATEPART month  GETDATE    - 1  and  DATEPART year  date created    DATEPART year  DATEADD m  -1  GETDATE

User · Answer

I don t think the accepted solution is very index friendly I use the following lines instead select   from dbtable where the date  gt   convert varchar 10  DATEADD m  -1  dateadd d  - datepart dd  GETDATE    1  GETDATE     120  and the date  lt   dateadd ms  -3  convert varchar 10  DATEADD m  0  dateadd d  - datepart dd  GETDATE    1  GETDATE     120     Or simply  this is the best   select   from dbtable where the date  gt   convert varchar 10  DATEADD m  -1  dateadd d  - datepart dd  GETDATE    1  GETDATE     120  and the date  lt  SELECT convert varchar 10  DATEADD m  -1  dateadd d  - datepart dd  GETDATE    1  GETDATE     120    Some help -- Get the first of last month SELECT convert varchar 10  DATEADD m  -1  dateadd d  - datepart dd  GETDATE    1  GETDATE     120   -- Get the first of current month SELECT convert varchar 10  DATEADD m  -1  dateadd d  - datepart dd  GETDATE    1  GETDATE     120   --Get the last of last month except the last 3milli seconds   3miliseconds being used as SQL express otherwise round it up to the full second  SERIUSLY MS  SELECT dateadd ms  -3  convert varchar 10  DATEADD m  0  dateadd d  - datepart dd  GETDATE    1  GETDATE     120

User · Answer

If you are looking for last month so try this   SELECT FROM   emp  WHERE DATEDIFF MONTH CREATEDDATE GETDATE      1   If you are looking for last month so try this   SELECT FROM  emp WHERE DATEDIFF day CREATEDDATE GETDATE    between 1 and 30

User · Answer

In Sql server for last one month   select   from tablename  where order date  gt  DateAdd WEEK  -1  GETDATE   1  and order date lt  GETDATE

User · Answer

I m from Oracle env and I would do it like this in Oracle   select   from table where trunc somedatefield   MONTH     trunc sysdate -INTERVAL  0-1  YEAR TO MONTH   MONTH     Idea  I m running a scheduled report of previous month  from day 1 to the last day of the month  not windowed   This could be index unfriendly  but Oracle has fast date handling anyways  Is there a similar simple and short way in MS SQL  The answer comparing year and month separately seems silly to Oracle folks

[sql] Get the records of last month in SQL server

Examples related to sql

Examples related to sql-server

Examples related to tsql