SQL Server : WHERE clause to only return values from a range of last day of months

Question

I've got a table called theTable from where I want to import some data. theTable includes Product stock values, and I am only interested in finding the stock balances at the last day of each month in a range of 6 months.

I have a column called DateKey which looks like yyyymmdd and describes the date for which a stock balance is recorded.

As an example, I'd like to be able to deduce the stockValue at the DateKey values: 20160131,20160331

For this I've tried:

CREATE TABLE #theTable
(
    DateKey INT,
    stockValue INT
);

INSERT INTO #theTable
VALUES (20160131, 4), (20160130, 5),
       (20160312, 5), (20160331, 4);

SELECT DateKey, stockValue 
FROM #theTable
WHERE DateKey BETWEEN CONVERT(VARCHAR(8), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 16, 0), 112)
                  AND CONVERT(VARCHAR(8), DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 13, -1), 112)

(where I've added the convert() shenanigans in order to avoid error of DateKey not being of type DateTime)

But this (naturally) returns:

DateKey     stockValue
20160131    4
20160130    5
20160312    5
20160331    4

how can I adjust this SQL statement to only include:

DateKey     stockValue
20160131    4
20160331    4

i.e the last days of the months over my specified date range?

EDIT:

The question is if there is a way to get only the last day's values without having to hard code a `WHERE DATEKEY IN ('20160131','20160228','20160331') as there will be many different values I'd like to choose from, which would render a hard coded solution to become very prone to error and time consuming.

Why is `datekey` not a `date` type in the first place? Since it appears that you're only looking for the last day of the month, have you checked out the [`EOMONTH()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/eomonth-transact-sql) function? — alroc, May 10 '17 at 13:02
Possible duplicate of [SQL Query to find the last day of the month](http://stackoverflow.com/questions/16646585/sql-query-to-find-the-last-day-of-the-month) — Tab Alleman, May 10 '17 at 13:02
A quick google search produced pages of duplicates. Have you made any effort to solve this on your own? — Tab Alleman, May 10 '17 at 13:02
@TabAlleman I've found several threads which answers the question how to find the latest day of a month. I didn't find a way to use this in combination of "between" as it would still get every date in between the last day of two months. In particular where the column to filter were not of type DateTime. — Cenderze, May 10 '17 at 13:09
@alroc It is not of type DateTime as it is used for browsing and creating a big cube and I didn't want a situation where such a key were not of type integer. EOMONTH() was a very interesting function. I've read about it earlier, but I didn't grasp the conversion you must do if your column is not of type DateTime in order to use it. — Cenderze, May 10 '17 at 13:12

score 2 · Accepted Answer · answered May 10 '17 at 13:02

2

Use EOMONTH function and get only such rows.

SELECT DateKey, stockValue 
FROM #theTable
where CONVERT(date, convert(varchar(10), datekey)) = eomonth(CONVERT(date, convert(varchar(10), datekey)))

answered May 10 '17 at 13:02

Vamsi Prabhala

48,685
4
36
58

Thanks for answering. I've fiddled with EOMONTH earlier but I think I did overthink the implementation, where I for some reason found it hard to implement with having a DateKey which isnt of type DateTime. But this solution was really graceful. – Cenderze May 10 '17 at 13:07

score 1 · Answer 2 · answered May 10 '17 at 13:02

1

This gives you the end of the current month:

FORMAT(EOMONTH(GETDATE()), 'yyyyMMdd')

And this gives you the end of month 2 months ago:

FORMAT(EOMONTH(DATEADD(MONTH, -2, GETDATE())), 'yyyyMMdd')

answered May 10 '17 at 13:02

Pred

8,789
3
26
46

score 0 · Answer 3 · answered May 10 '17 at 13:02

0

SELECT DateKey, stockValue 
FROM #theTable
WHERE DateKey  = 
DATEADD(month, ((YEAR(DateKey) - 1900) * 12) + MONTH(DateKey), -1)

answered May 10 '17 at 13:02

Whencesoever

2,218
15
26

score 0 · Answer 4 · answered May 10 '17 at 13:10

Assuming you cant guarantee that the last datekey will be the last day of the month

This will work

create table #theTable(
DateKey int,
stockValue int
);

insert into #theTable
VALUES(20160131,4),
(20160130,5),
(20160312,5),
(20160331,4);

;with lastDayOfMonth as (
select 
max(DateKey) as MaxDate
from #theTable
group by left(cast(DateKey as varchar(8)),6))

SELECT DateKey, stockValue ,left(cast(DateKey as varchar(8)),6) 
FROM #theTable
inner join lastDayOfMonth
on MaxDate = DateKey
WHERE DateKey between convert(varchar(8),DATEADD(month,datediff(month,0,getdate())-16,0),112)
and CONVERT(VARCHAR(8),DATEADD(MONTH, DATEDIFF(month, -1, getdate())-13,-1),112)

drop table #theTable

score 0 · Answer 5 · answered May 10 '17 at 13:12

You can use eomonth() as below

select * from #thetable where 
convert(date,convert(varchar(8), DateKey,112)) = eomonth(convert(date, convert(varchar(8), datekey,112)))

Output:

+----------+------------+
| DateKey  | stockValue |
+----------+------------+
| 20160131 |          4 |
| 20160331 |          4 |
+----------+------------+

score 0 · Answer 6 · answered May 10 '17 at 13:15

You could utilize EOMONTH (https://learn.microsoft.com/en-us/sql/t-sql/functions/eomonth-transact-sql) to check if the date is the last date of the month.

Something like this:

create table #theTable(
DateKey int,
stockValue int
);

insert into #theTable
VALUES(20160131,4),
(20160130,5),
(20160312,5),
(20160331,4);


SELECT DateKey, stockValue 
FROM #theTable
WHERE DateKey between convert(varchar(8),DATEADD(month,datediff(month,0,getdate())-16,0),112)
and CONVERT(VARCHAR(8),DATEADD(MONTH, DATEDIFF(month, -1, getdate())-13,-1),112)
AND EOMONTH ( CONVERT(varchar(8),datekey) ) = CONVERT(varchar(8),datekey)


DROP TABLE #theTable

SQL Server : WHERE clause to only return values from a range of last day of months

6 Answers6