All geek questions in one place

SQL Server - select all items with a date in the previous month

I have a table in my SQL Server database called " items " that has a column called " dateFinished ".

I have a script that will run on the first day of every month, which should select all the elements that were completed in the previous month.

So, for example, on February 1, you will need to select all the items in which the date-final will be greater than or equal to 00:00 on January 1 and less than 00:00 on February 1.

he must also work for new years (e.g. DEC - JAN).

Any ideas?

+4
source share
4 answers

You can get the day of the previous month with dateadd(m,-1,getdate()) . Then filter the year and month of this date in the where clause, for example:

 select * from items where datepart(yy,dateFinished) = datepart(yy,dateadd(m,-1,getdate())) and datepart(m,dateFinished) = datepart(m,dateadd(m,-1,getdate()))

This should work after years, and also if the request is executed later than the first month.

+7
source 
 Select * from items where datefinished >= dateadd(m, datediff(m, 0, GETDATE()) - 1, 0) AND datefinished < dateadd(m, datediff(m, 0, GETDATE()), 0)
+8
source

Just use only what I used: DATEDIFF(mm,dateFinished,GETDATE()) = 1

 SELECT * FROM items WHERE DATEDIFF(mm,dateFinished,GETDATE()) = 1
+1
source

I would start by checking the DATEADD function http://msdn.microsoft.com/en-us/library/ms186819.aspx

0
source

Source: https://habr.com/ru/post/1300126/

More articles:


All Articles