Start and End Date Ranges - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Start and End Date Ranges

Author

Topic

Alain_TV
Starting Member

12 Posts

Posted - 2015-04-16 : 08:43:42

Hi All,

I have a set of MS SQL reports, that need to always run on a certain day of the month. Generally the 20th. If the report was to run few days before the 20th, say on the 10th, I wish to retrieve those days between the 20th from the previous month, till the current date.

e.g: '2015-4-10' should only return 20 days worth of data.

I have tried the following query:


SELECT
 DATEADD(D, 1, MAX(CAST(DateTimeStamp AS DATE))) As EndDate,
 MIN(CAST(DATEFROMPARTS(DATEPART(YEAR, DateTimeStamp),DATEPART(MONTH, 
(SELECT CASE WHEN DATEDIFF(DAY,DATEPART(DAY, GETDATE()),28) <0 THEN (SELECT DATEPART(MONTH, GETDATE())) 
ELSE (SELECT DATEPART(MONTH, GETDATE()) -1) END AS Date)),28)AS DATE)) AS StartOfMonth

FROM
 tbLogTimeValues
WHERE
 DATEPART(YEAR, DateTimeStamp) = DATEPART(YEAR, DATEADD(M, -1, GETDATE()))

Which parses ok and managed to test all individual queries, however, as a whole, I get the following error message "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

Surely, there is a better way to do this. If someone has a better way or knows how to fix the above error, please let me know.

Thanks,

Alain

Regards,
Alain

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-16 : 09:59:33

The error message is clear - you have a SELECT within the MIN function, and that is not allowed. I did not understand the logic you are trying to implement. If you want to find the first of a month (StartOfMonth), do this:

DATEADD(mm,DATEDIFF(mm,'19000101',GETDATE()),'19000101')

You can substitute GETDATE() with any date to find the first of the month on which that date falls. If you want to find the 20th of the prior month, do this:

DATEADD(mm,DATEDIFF(mm,'19000101',GETDATE()),'18991220')

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-16 : 10:02:48

This worked for me:


SELECT DATEADD(D, 1, MAX(CAST(DateTimeStamp AS DATE))) AS EndDate
    , MIN(CAST(DATEFROMPARTS(DATEPART(YEAR, DateTimeStamp), DATEPART(MONTH, StartOfMonth), 28) AS DATE))
FROM tbLogTimeValues
cross apply
(
    SELECT CASE 
            WHEN DATEDIFF(DAY, DATEPART(DAY, GETDATE()), 28) < 0
                THEN DATEPART(MONTH, GETDATE())
            ELSE DATEPART(MONTH, GETDATE()) - 1
            END
) AS _(StartOfMonth)
                        
WHERE DATEPART(YEAR, DateTimeStamp) = DATEPART(YEAR, DATEADD(M, - 1, GETDATE()))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-04-16 : 10:36:08

[code]DECLARE @FromDate DATE = DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()) + DATEPART(DAY, GETDATE()) / 20, '19000120'),
@ToDate DATE = GETDATE();

-- SwePeso
SELECT *
FROM dbo.tbLogTimeValues
WHERE DateTimeStamp >= @FromDate
AND DateTimeStamp < @ToDate;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-16 : 11:57:39

quote:

Originally posted by SwePeso

DECLARE	@FromDate DATE = DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()) + DATEPART(DAY, GETDATE()) / 20, '19000120'),
	@ToDate DATE = GETDATE();

-- SwePeso
SELECT	*
FROM	dbo.tbLogTimeValues
WHERE	DateTimeStamp >= @FromDate
	AND DateTimeStamp < @ToDate;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Clever!

Alain_TV
Starting Member

12 Posts

Posted - 2015-04-17 : 07:00:37

Thank you all.

Spoiled for choice today. They are all good options and provide a valid result.

Thanks again to everyone, for the time and help.

Regards,

Alain

Regards,
Alain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources