Geek asks and answers

Select a date range from two tables from the present day to the first day of this month

Using Microsoft SQL 2008. I have two tables that I want to select from the current date to the first day of this month (or during the same month). Let's say today the date is 05/09/2012, and the date column is the date. Fom in the tables below, I should get only rows with sets of 6.7 from table 1 and a set of rows of 9.2 from table 2, because these dates refer to the same month as 09/05/2012.

table1
4 02/01/2012
5 01/02/2011
6 05/01/2012
7 05/20/2012

table2
8 02/01/2012
9 05/14/2012
3 01/02/2011
2 05/18/2012

I tried this, but it did not work:

DECLARE @daterange
SET @daterange = (DATEPART(MONTH,GETDATE()) + '/' + DATEPART(YEAR,GETDATE()))

SELECT blah from table where (DATEPART(MONTH,datecolumn) + '/' + DATEPART(YEAR,datecolumn)) = @daterange
+3
source share
3 answers

You can simplify it, no need to restore the date field from GETDATE():

SELECT blah
FROM table
WHERE DATEPART(MONTH,datecolumn) = DATEPART(MONTH,getdate()) AND
DATEPART(YEAR,datecolumn) = DATEPART(YEAR,getdate())
+1
source

, :

declare @monthBeginning datetime 
set @monthBeginning = (select dateadd(dd,-(day(dateadd(mm,1,getdate()))-1),dateadd(mm,0,getdate()))

declare @monthEnd datetime
set @monthEnd = (select dateadd(dd, -day(dateadd(m,1,getdate())), dateadd(m,1,getdate())))

select *
from dateTable
where datecolumn between @monthBeginning and @monthEnd

, - , .

0

You can get the first of the current month as follows:

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

If you add one month to the above result, you will receive the first of the following month:

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)

Using these two dates, you can get the required strings as follows:

SELECT
  …
FROM table1
WHERE date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())    , 0)
  AND date <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)
0
source

More articles:


All Articles