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
eluceroAsked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
try..Can you tell me the purpose of (DATEADD(wk, 0, DATEADD(DAY, 0-DATEPART(WEEKDAY, @StartDt), DATEDIFF(dd, 0, @StartDt)))) as SatYTDDt

I have modified all your columns. Now it is picking  '2016-10-29'  

Other than this I think you need below.. please check?

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

WHILE @StartDt <= @EndDt 
BEGIN

PRINT @StartDt

SELECT
CONCAT(YEAR(@StartDt), RIGHT(CONCAT('0', MONTH(@StartDt)),2), RIGHT(CONCAT('0', DAY(@StartDt)),2))AS YTDDateKey,
CAST(@StartDt AS DATETIME) 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

Open in new window


Output


2016-10-08
YTDDateKey       Year                    SatYTDDt                LoadDttm
---------------- ----------------------- ----------------------- -----------------------
20161008         2016-10-08 00:00:00.000 2016-10-01 00:00:00.000 2016-11-16 07:59:01.773

(1 row(s) affected)

2016-10-15
YTDDateKey       Year                    SatYTDDt                LoadDttm
---------------- ----------------------- ----------------------- -----------------------
20161015         2016-10-15 00:00:00.000 2016-10-08 00:00:00.000 2016-11-16 07:59:01.773

(1 row(s) affected)

2016-10-22
YTDDateKey       Year                    SatYTDDt                LoadDttm
---------------- ----------------------- ----------------------- -----------------------
20161022         2016-10-22 00:00:00.000 2016-10-15 00:00:00.000 2016-11-16 07:59:01.773

(1 row(s) affected)

2016-10-29
YTDDateKey       Year                    SatYTDDt                LoadDttm
---------------- ----------------------- ----------------------- -----------------------
20161029         2016-10-29 00:00:00.000 2016-10-22 00:00:00.000 2016-11-16 07:59:01.773

(1 row(s) affected)
0
 
Ryan ChongCommented:
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:
2016-10-08
2016-10-15
2016-10-22
2016-10-29

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?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
20161008.png
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
HuaMin ChenSystem AnalystCommented:
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

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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).
0
 
eluceroAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.