How to query in SQL Server for data in particular date range

Question

I need small help, I have to configure one report where client want only 15 days data.in following way

if day of month is less than or equal to 15 then range of date would be 1 to 15 if date exceed 15 then range of date would be 16 to 30 or 31

Answer 1

select * from TABLE_NAME
where DATEPART(day, DATE_COLUMN) between 
(case when datepart(day, getdate()) <= 15 then 1 else 16 end) and
(case when datepart(day, getdate()) <= 15 then 15 else 31 end)

Answer 2

You could also try

SELECT * FROM <table> WHERE [date]  between 
    case when DATEPART(DAY, GETDATE()) <=15 then DATEADD(month, DATEDIFF(month, 0, getdate()), 0) else DATEADD(month, DATEDIFF(month, 0, getdate()), 15) end
    and 
    case when DATEPART(DAY, GETDATE()) <=15 then DATEADD(month, DATEDIFF(month, 0, getdate()), 15) else DATEADD(MONTH,1+DATEDIFF(MONTH,0,GETDATE()),-1) end

Answer 3

This query may give you the proper result

SELECT * FROM TableName
WHERE 1 = (
    CASE WHEN DAY(GETDATE()) <= 15 THEN 
        CASE WHEN (DateField BETWEEN CONVERT(DATETIME,CONVERT(VARCHAR,MONTH(DateField)) + '-' + '1-' + CONVERT(VARCHAR,YEAR(DateField)))
            AND CONVERT(DATETIME,CONVERT(VARCHAR,MONTH(DateField)) + '-' + '15-' + CONVERT(VARCHAR,YEAR(DateField)))) 
        THEN 1 ELSE 0 END
    ELSE
        CASE WHEN(DateField BETWEEN CONVERT(DATETIME,CONVERT(VARCHAR,MONTH(DateField)) + '-' + '16-' + CONVERT(VARCHAR,YEAR(DateField)))
            AND CONVERT(DATETIME,CONVERT(VARCHAR,MONTH(DateField)) + '-' + CONVERT(VARCHAR,DAY(EOMONTH(DateField))) + '-' + CONVERT(VARCHAR,YEAR(DateField)))) 
        THEN 1 ELSE 0 END
END)