CODEWITHSUNDEEP
sqlreporting-servicesssrs-2008
Hinarf
Hinarf

Reputation: 161

DateAdd Syndrome

I used a ton of keywords to search for information on this but nothing popped up every time I hit enter, that's a little surprising to me.

My question is this:

I have functions in my query to return to me a specific area of time in a month of a gross sales week. It looks like this:

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate= **DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)**
SET @EndDate= **DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 12)**

EXEC dbo.lvs_SalesSummaryRpt @EndDate =@EndDate, @StartDate = @StartDate

and I have one for the entire month as well:

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate=**CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101)**
SET @EndDate=**DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))**

EXEC dbo.lvs_SalesSummaryRpt @EndDate =@EndDate, @StartDate = @StartDate

Now the areas that I've bolded, I've copied and pasted into a select in a new dataset in SSRS, with those new datasets I can apply an expression =First(Fields!BegOfWeek.Value, "WeekDS") so that it'll return to me a timeframe for whatever month they are looking at. This is great, same for day. I cannot for the life of me figure out how to do days, specifically, the sixth and the seventh of the month.

I'm extremely new to this syntax, and the only reason I have those other examples is because they were provided to me, I'm having a hard time grasping these date functions and how to get them to work and am feeling quite stupid at the moment.

Can anyone help me?

I was unable to bold the code due to it's format, so please note that those areas are surrounded by *'s

I guess that I could give more information. The dataset I have for day is thus:

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate='11/6/2014'
SET @EndDate='11/7/2014'

EXEC dbo.lvs_SalesSummaryRpt @EndDate =@EndDate, @StartDate = @StartDate

and now I hope you can see my confusion, I cannot use 11/6/2014 and 11/7/2014 because those are static dates and I need this to be dynamic to the month they want.

Upvotes: 0

Views: 287

Answers (1)

Hinarf
Hinarf

Reputation: 161

I have answered my own question I do believe, as it is now working as it should.

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),5) as BegDay

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),6) as EndDay

It's been really tedious couple of days but I am feeling more and more comfortable. The thing I need to work on is not freaking the F out whenever I run into something new. I would say thanks..... but to who? THANKS ME!

The rest of you, I hope this helps, but I doubt anyone is going to have this problem. I'm a newb.

Just for the crap of it, I also had to convert that to a format that I did not want to display hours. I did this for day and month.

Day: 

    SELECT Convert(date,GETDATE()) as BegDay,
    Convert(date,dateadd(d,1,GETDATE())) as EndDay

Month:

Select CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101) as BegofMonth,
CONVERT(Date,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))) as EndofMonth

Upvotes: 1

Related Questions