asked on

sql server query?

Why am I not picking up '2016-10-29' in this query

Declare @StartDt date
Declare @EndDt date
Set @StartDt = '2016-10-08'

--print @StartDt

Set @EndDt = '2016-11-02'

WHILE @StartDt <= @EndDt
BEGIN

SELECT
CONVERT(INTEGER, CONVERT(CHAR(10), (DATEADD(wk, 0, DATEADD(DAY, 0-DATEPART(WEEKDAY, @StartDt), DATEDIFF(dd, 0, @StartDt)))), 112)) AS YTDDateKey,
YEAR((DATEADD(wk, 0, DATEADD(DAY, 0-DATEPART(WEEKDAY, @StartDt), DATEDIFF(dd, 0, @StartDt))))) AS [Year],
(DATEADD(wk, 0, DATEADD(DAY, 0-DATEPART(WEEKDAY, @StartDt), DATEDIFF(dd, 0, @StartDt)))) as SatYTDDt, LoadDttm = getdate()

SET @StartDt = DATEADD(dd, 7, @StartDt)
END

Ryan Chong

Why am I not picking up '2016-10-29' in this query

you are...

Declare @StartDt date
Declare @EndDt date
Set @StartDt  = '2016-10-08'
Set @EndDt = '2016-11-02'

WHILE @StartDt <= @EndDt
BEGIN

print @StartDt 

SET @StartDt = DATEADD(dd, 7, @StartDt)
END

Open in new window

you are getting the output such as:

Open in new window

For the query:

SELECT
CONVERT(INTEGER, CONVERT(CHAR(10),  (DATEADD(wk, 0, DATEADD(DAY, 0-DATEPART(WEEKDAY, @StartDt), DATEDIFF(dd, 0, @StartDt)))), 112)) AS YTDDateKey,
YEAR((DATEADD(wk, 0, DATEADD(DAY, 0-DATEPART(WEEKDAY, @StartDt), DATEDIFF(dd, 0, @StartDt))))) AS [Year],
(DATEADD(wk, 0, DATEADD(DAY, 0-DATEPART(WEEKDAY, @StartDt), DATEDIFF(dd, 0, @StartDt)))) as SatYTDDt, LoadDttm = getdate()

Open in new window

can you tell us what you intend to get here?

Jim Horn

When I run your code on my SQL 2012 box @startdt='2016-10-08' is the first @startdt processed, but YTDDateKey=20161001.

It would probably help if you could add some details as to what you're trying to accomplish here.

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Peter Chan

Try

Declare @StartDt date 
Declare @EndDt date
Set @StartDt  = '2016-10-01'


--print @StartDt

Set @EndDt = '2016-11-02'

WHILE @StartDt <= @EndDt 
BEGIN
SET @StartDt = DATEADD(dd, 7, @StartDt)

SELECT
CONVERT(INTEGER, CONVERT(CHAR(10),  (DATEADD(wk, 0, DATEADD(DAY, 0-DATEPART(WEEKDAY, @StartDt), DATEDIFF(dd, 0, @StartDt)))), 112)) AS YTDDateKey,
YEAR((DATEADD(wk, 0, DATEADD(DAY, 0-DATEPART(WEEKDAY, @StartDt), DATEDIFF(dd, 0, @StartDt))))) AS [Year],
(DATEADD(wk, 0, DATEADD(DAY, 0-DATEPART(WEEKDAY, @StartDt), DATEDIFF(dd, 0, @StartDt)))) as SatYTDDt, LoadDttm = getdate()



END

Open in new window

Vitor Montalvão

Why am I not picking up '2016-10-29' in this query

Tell us what you want to do with the query.

What's actually doing is processing the last week based in the current day and that's why '2016-10-29' isn't returned. When '2016-10-29' it's processing '2016-10-22' (one week before).

elucero

ASKER

Thanks!