CODEWITHSUNDEEP
sql-serverdatedateadd
ScottM
ScottM

Reputation: 1

Date range based on Column Date

I am using the latest SQL Server. I have a table with a CreatedDate column. I need to write a Query that uses dates that are plus or minus 7 from the Date in CreatedDate. I have no clue how to go about this. My thought was this:

DECLARE @Date datetime 
DECLARE @SevenBefore datetime
DECLARE @SevenAfter datetime
SET @Date = CreatedDate
SET @SevenBefore = DATEADD(day,-7,@Date)
SET @SevenAfter = DATEADD(day,7,@Date)
SELECT *
FROM <table>
WHERE <table> BETWEEN @SevenBefore AND @SevenAfter

The issue with this is that I cannot use "CreatedDate" as a SET @DATE because SQL gives an error "Invalid column name 'CreatedDate'"

Any help would be appreciated. I cannot list a date because every date in that column could be different.

Thanks

Upvotes: 0

Views: 328

Answers (1)

Laughing Vergil
Laughing Vergil

Reputation: 3766

In this case, you need to stop thinking as a programmer would, and start thinking as a Database programmer would.

Lets work only with this central part of your query:

SELECT *
FROM <table>
WHERE <table> BETWEEN @SevenBefore AND @SevenAfter

Now, you say that the CreatedDate is a column in a table. For this example, I will assume that the CreatedDate is in a table other than the one in your example above. For this purpose, I will give two fake names to the tables. The table with the CreatedDate, I will call tblCreated, and the one from the query above I will call tblData.

Looking above, it's pretty obvious that you can't compare an entire table row to a date. There must be a field in that table that contains a date/time value. I will call this column TargetDate.

Given these assumptions, your query would look something like:

SELECT *
FROM tblCreated tc
INNER JOIN tblData td
    ON td.TargetDate BETWEEN DATEADD(day, -7, tc.CreatedDate) and DATEADD(day, 7, tc.CreatedDate)

Looking at this, it is clear that you still need some other associations between the tables. Do you only want all data rows per customer based on the Created date, or perhaps only want Creations where some work was done on them as shown in the Data records, or ??. Without a fuller specification, we can't help with that, though.

Upvotes: 2

Related Questions