DateAdd for Week - 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

SQL Server 2012 Forums

Transact-SQL (2012)

DateAdd for Week

Author

Topic

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-07-23 : 04:46:16

Hi

Im trying to pull records in for the below code but having an issue trying to read the SnapShotDateKey between the start and end of the week to only show these records. I get conversion failed when converting the varchar value 'Jul 22 2' to data type int.Do I need a 112) at the end of each line to format?

Thanks

Select Distinct IncidentID,
Case
When DateKey = convert(int,convert(varchar(8),DateAdd(wk,-9,GetDate()),112))
And [priority] = '1'
And SnapShotDateKey = convert(int,convert(varchar(8),DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)))
And SnapShotDateKey = convert(int,convert(varchar(8),DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)))
Then 1 Else 0
End as P1s
From FACT_INCIDENT
order by P1s Desc

SZ1
to learn is to show the universe that you care...!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-23 : 04:58:00

[code]DECLARE @FromDateKey INT = CONVERT(INT, CONVERT(CHAR(8), DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()), '19000101'), 112)),
@ToDateKey INT = CONVERT(INT, CONVERT(CHAR(8), DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()), '19000107'), 112)),
@DateKey INT = CONVERT(INT, CONVERT(CHAR(8), DATEADD(WEEK, -9, GETDATE()), 112));

SELECT DISTINCT IncidentID,
CASE
WHEN DateKey = @DateKey AND [Priority] <> '1' AND SnapShotDateKey BETWEEN @FromDateKey AND @ToDateKey THEN 1
ELSE 0
END AS P1s
FROM dbo.Fact_Incident
ORDER BY P1s DESC;[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-23 : 05:00:53

yes you need it
Otherwise it will assume default format of Mon dd yyyy ie Jul 22 2013 etc which will throw error when you try converting to int

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-07-23 : 05:07:08

I tried adding it to end of statements but does not return and P1 records? have I got the number the wrong way around?
Thanks

Select Distinct IncidentID,
Case
When DateKey = convert(int,convert(varchar(8),DateAdd(wk,-9,GetDate()),112))
And [priority] = '1'
And SnapShotDateKey = convert(int,convert(varchar(8),DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0),112),0)And SnapShotDateKey = convert(int,convert(varchar(8),DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6),112),0)Then 1 Else 0
End as P1s
From FACT_INCIDENT
order by P1s Desc

SZ1
to learn is to show the universe that you care...!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-23 : 05:10:48

shouldnt condition be this? how can it be = to both first and last values of week at the same time? it should be BETWEEN


Select Distinct IncidentID, 
Case
When DateKey = convert(int,convert(varchar(8),DateAdd(wk,-9,GetDate()),112))
And [priority] = '1'
And SnapShotDateKey BETWEEN convert(int,convert(varchar(8),DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0),112),0)And convert(int,convert(varchar(8),DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6),112),0)Then 1 Else 0
End as P1s
From FACT_INCIDENT
order by P1s Desc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-07-23 : 05:11:46

I tried your code SwePeso and it looks good, in your statement you say not = to P1 so I changed this to = instead of <>.
The '19000101', GETDATE()), '19000107'), 112)), code in your statement does those dates only act as stamps and not actual reading those dates as the actual dates...so they are acting as a dummy date?
Thanks

SZ1
to learn is to show the universe that you care...!

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-07-23 : 05:54:20

Yeah I was wondering about the between clause thats much better!
Im seeing records for -9 weeks, but none for -10 even though I know there is one that came in on the 15/05/2013, any ideas?
I cant see the record no matter what number of weeks I put but its definately there.
Thanks

Select Distinct f.IncidentID, i.IncidentNumber, f.DateKey,
Case
When f.DateKey = convert(int,convert(varchar(8),DateAdd(week,-10,GetDate()),112))
And f.[priority] = '1'
--And f.SnapShotDateKey BETWEEN convert(int,convert(varchar(8),DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0),112),0)
--And convert(int,convert(varchar(8),DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6),112),0)
Then 1 Else 0
End as P1s
From FACT_INCIDENT f
Join DIM_INCIDENT i
On i.IncidentID = f.IncidentID
--Where IncidentNumber = '323561'
--where f.Priority =1
order by P1s Desc

SZ1
to learn is to show the universe that you care...!

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-07-23 : 06:56:16

How do I make the search between 2 periods so between weeks 7 and 10?

Select f.IncidentID, i.IncidentNumber, f.DateKey,
Case
--When f.DateKey Between convert(int,convert(varchar(8),DateAdd(week,-7,GetDate()),0),112),0)
--And f.DateKey convert(int,convert(varchar(8),DateAdd(week,-10,GetDate()),112))
When f.CreatedDateTime BETWEEN DATEADD(wk,-7,GETDATE())
And DATEADD(wk,-10,GETDATE())
And f.[priority] In ('1','2','3')
--And f.DateKey BETWEEN convert(int,convert(varchar(8),DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0),112),0)
--And convert(int,convert(varchar(8),DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6),112),0)
Then 1 Else 0
End as P1s
From FACT_INCIDENT f
Join DIM_INCIDENT i
On f.IncidentID = i.IncidentID
order by P1s Desc

SZ1
to learn is to show the universe that you care...!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-23 : 06:58:30

If you don't get any rows with this query, it means there are no rows in the table.

DECLARE	@FromDateKey INT = CONVERT(INT, CONVERT(CHAR(8), DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()), '19000101'), 112)),
	@ToDateKey INT = CONVERT(INT, CONVERT(CHAR(8), DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()), '19000107'), 112)),
	@DateKey INT = CONVERT(INT, CONVERT(CHAR(8), DATEADD(WEEK, -9, GETDATE()), 112));

SELECT DISTINCT	IncidentID, 
		CASE
			WHEN DateKey = @DateKey AND [Priority] = '1' AND SnapShotDateKey BETWEEN @FromDateKey AND @ToDateKey THEN 1
			ELSE 0
		END AS P1s
FROM		dbo.Fact_Incident
ORDER BY	P1s DESC;

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

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-07-23 : 07:04:37

Yeh Im getting rows but I need to be able to search between 1 whole week and not -9 weeks from today for that one day, I need the whole week: so below Im trying to return only between weeks 7 and 8?

Select Distinct f.IncidentID, i.IncidentNumber, f.DateKey, f.Priority,
Case
--When f.DateKey Between convert(int,convert(varchar(8),DateAdd(week,-7,GetDate()),0),112),0)
--And f.DateKey convert(int,convert(varchar(8),DateAdd(week,-10,GetDate()),112))
When f.CreatedDateTime BETWEEN DATEADD(week,-7,GETDATE())
And DATEADD(week,-8,GETDATE())
And f.[priority] = 1
--And f.DateKey BETWEEN convert(int,convert(varchar(8),DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0),112),0)
--And convert(int,convert(varchar(8),DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6),112),0)
Then 1 Else 0
End as P1s
From FACT_INCIDENT f
Join DIM_INCIDENT i
On f.IncidentID = i.IncidentID
order by P1s Desc,Priority

SZ1
to learn is to show the universe that you care...!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-23 : 07:10:25

You mean 7 and 8 weeks back?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-23 : 07:13:27

I think it's time you learn the code you have been given before asking more questions.
Everything you need to know is there.

DECLARE	@FromDateKey INT = CONVERT(CHAR(8), DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()) - 9, '19000101'), 112),
	@ToDateKey INT = CONVERT(CHAR(8), DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()) - 8, '19000107'), 112),
	@DateKey INT = CONVERT(CHAR(8), DATEADD(WEEK, -9, GETDATE()), 112);

SELECT DISTINCT	IncidentID, 
		CASE
			WHEN DateKey = @DateKey AND [Priority] = '1' AND SnapShotDateKey BETWEEN @FromDateKey AND @ToDateKey THEN 1
			ELSE 0
		END AS P1s
FROM		dbo.Fact_Incident
ORDER BY	P1s DESC;

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

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-07-23 : 07:21:45

Yeh I get the code and it works well I get records back with your first code but now I want to be able to search between a week so yes looking back between weeks or for one whole week so week 8 and 9, how many rows.
Thanks

SZ1
to learn is to show the universe that you care...!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-23 : 07:33:41

quote:
Originally posted by sz1

Yeh I get the code and it works well I get records back with your first code but now I want to be able to search between a week so yes looking back between weeks or for one whole week so week 8 and 9, how many rows.
Thanks

SZ1
to learn is to show the universe that you care...!

you just need to extend last suggestion to include parameters for weeks and then pass appropriate values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-07-23 : 07:53:51

Thanks for your help guys, much appreciated!

SZ1
to learn is to show the universe that you care...!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources