Select data from today until the last 15th of month?

Question

I want to select a range of data from today until the last 15th (so this month or last month). How would this be done in TSQL?

Following Get last Friday's Date unless today is Friday using T-SQL it seems like I would have to make use of DATEDIFF at the least.

I'm considering building a date string, like:

set @date='yyyy-mm-15'

But yyyy or mm would not be able to be simply the current year/month, in case it has to look back a month/year.

jazzytomato · Accepted Answer · 2012-11-28T10:26:15.867

4

This should give you what you want

SELECT *
FROM YourTable
WHERE YourDate > CAST((
            CASE 
                WHEN DAY(getdate()) < 15
                    THEN (CAST(YEAR(GETDATE()) AS CHAR(4)) + CAST(MONTH(dateadd(month, - 1, getdate())) AS CHAR(2)) + '15')
                ELSE CAST(YEAR(GETDATE()) AS CHAR(4)) + CAST(MONTH(GETDATE()) AS CHAR(2)) + '15'
                END
            ) AS DATETIME)

EDIT changed WHEN DAY(getdate()) > 15 to WHEN DAY(getdate()) < 15, as the results were inverted :)

edited Nov 28 '12 at 10:26

answered Nov 27 '12 at 17:35

jazzytomato

6,994
2
31
44

1

I tried this, but I got one date in the result set from 11/9/2012. It should only be showing from today until 11/15/2012. Shouldn't the first case part only be from this month and not last month? – JBurace Nov 27 '12 at 20:03
At least you have the idea on how to do it and you could work from this, but I'll have a look tomorrow ( Im from my iPhone here ) – jazzytomato Nov 27 '12 at 22:11
Yes I just noticed I have inverted but cannot edit from my phone, just invert the 'then' and 'else' content :-) – jazzytomato Nov 27 '12 at 22:17

score 3 · Answer 2 · answered Nov 27 '12 at 17:39

Format the current date in the format yyyy-mm-15, convert back to date, compare to the current date and if this is later that today, subtract 1 month.

That's what I'd do in ANSI SQL... But apparently, MS SQL Server doesn't know "interval". Well, you can make use of DateAdd instead:

DateAdd() - Returns a new datetime value based on adding an interval to the specified date. Where first parameter specifies on which part of the date to return a new value: yy(yyyy) for Year, mm(m) for Month, dd(d) for Day etc. For example: select dateadd(d,2,getdate()) - adds 2 days to current date and returns new date.

Select data from today until the last 15th of month?

2 Answers2