Difference Between Days of the Week

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

General SQL Server Forums

New to SQL Server Programming

Difference Between Days of the Week

Author

Topic

margo_qu
Starting Member

8 Posts

Posted - 2006-08-15 : 11:32:45

Hi! I am totally new to the forum and to the SQL.

Need your advice. My report will run on Friday automatically and I need to retrieve the data from previous Friday until Today’s Friday.

I have an example from another developer’s code, but this one gets the data on the previous day. Can you please help me to right the expression?

Here is an example ‘on the previous day’

CAST(CONVERT(CHAR(10), tbl1.closeddate,120) as datetime) = CAST(CONVERT(CHAR(10), dateadd(day, -1,getdate()), 120) as datetime)

This is what I did (assuming today is Friday)

(tbl1.closedDate BETWEEN CAST(CONVERT(CHAR(10), DATEADD(day, - 8, GETDATE()), 120) AS datetime) AND CAST(CONVERT(CHAR(10),
DATEADD(day, - 1, GETDATE()), 120) AS datetime))

I don't get any records back.

Please help.
Thanks a lot.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-15 : 12:11:16

Try


Create table #t (a datetime)
Insert into #t values ('2006-08-12') 
Insert into #t values ('2006-08-14')
Insert into #t values ('2006-08-01')
Insert into #t values ('2006-08-19')
Insert into #t values ('2006-07-14')

Select * from #t where a between getdate()- 7 and getdate()- 1

Srinika

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-15 : 13:07:31

@Srinika: only difference is that, by using getdate()- you are retaining the time component.


set nocount on
declare
	@date1	datetime,
	@date2	datetime,
	@MyDate	datetime
SET @MyDate = '2006-08-14 01:00:00.000'

SELECT 
	CAST(CONVERT(CHAR(10), DATEADD(day, - 8, GETDATE()), 120) AS datetime) , CAST(CONVERT(CHAR(10),
	DATEADD(day, - 1, GETDATE()), 120) AS datetime),
	CASE
		WHEN @MyDate BETWEEN getdate()-8 AND  GETDATE()-1 THEN 'Between'
		ELSE 'outside'
	end,
		CASE
		WHEN @MyDate BETWEEN CAST(CONVERT(CHAR(10), DATEADD(day, - 8, GETDATE()), 120) AS datetime) AND  CAST(CONVERT(CHAR(10),
	DATEADD(day, - 1, GETDATE()), 120) AS datetime) THEN 'Between'
		ELSE 'outside'
	end

Which check is correct for your requirements, Margo?

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

margo_qu
Starting Member

8 Posts

Posted - 2006-08-15 : 13:51:11

Dear Wanderer,
I probably explained it bad. I need data from Friday to Friday, and the report will be automaticly scheduled, which means, I would not be able to recent the value for variable.

Again, excuse me, if I did not understand your answer. I am very much new to SQL.

and thank you very much, for your help.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-15 : 14:13:26

After taking into account wanderer's point:


Create table #t (a datetime)
Insert into #t values ('2006-08-12 14:06:22:310') -- earlier than 7 days 
Insert into #t values ('2006-08-14 14:06:22:310') -- in the range
Insert into #t values ('2006-08-08 23:59:59:310') -- in the range
Insert into #t values ('2006-08-08 01:01:01:310') -- in the range
Insert into #t values ('2006-08-08 23:59:59:310') -- in the range
Insert into #t values ('2006-08-14 23:59:59:310') -- in the range
Insert into #t values ('2006-08-19 14:06:22:310') -- after today
Insert into #t values ('2006-07-14 14:06:22:310') -- earlier than 7 days

Select a, Convert(datetime,Convert(varchar(30),a, 101)) as [Derived Date] from #t where a between 
Convert(datetime,Convert(varchar(30),getdate()- 7, 101)) and Convert(datetime,Convert(varchar(30),getdate(), 101))

drop table #t

Srinika

margo_qu
Starting Member

8 Posts

Posted - 2006-08-15 : 14:37:39

Srinika, thank you, I will try it right now.

Can you please explain to me what '101' or '120' means and where can I find all formats and what they mean?

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-08-15 : 14:40:51

Margo,

101 and 120 are format values that control the output of Convert(). See SQL Server Books On Line, topic "CAST and CONVERT".

Ken

margo_qu
Starting Member

8 Posts

Posted - 2006-08-15 : 14:52:40

Thank you, KenW, I've just found it.

Srinika, I cannot execute it. SQL Server saying there is an error near 'AND'

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-15 : 14:55:18

r u executing the same thing or did u modify mine?
Mine should work!!

When playing with it u may have ommited a bracket or so

Srinika

margo_qu
Starting Member

8 Posts

Posted - 2006-08-15 : 15:27:10

Srinika, you are right, it was my fault... I am playing in Development, but continue to get 0 results... so I run max(date) and got 2005-11 :(

I have to contact DBA and find out when Development was updated last time... I will let you know when I have data to run.

Thanks a lot, guys!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-15 : 15:38:01

Using start of week function:


select
	*
from
	tbl1
where
	-- Greater than or equal Friday on or before Yesterday
	tbl1.closeddate >= dbo.F_START_OF_WEEK(getdate()-1,6) and
	-- Less than Friday after Yesterday
	tbl1.closeddate <  dbo.F_START_OF_WEEK(getdate()+6,6)

Start of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

CODO ERGO SUM

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-15 : 15:38:51

Just Execute mine & c whether u get the desired results out of the data provided.

If OK, replace the Table & field names accordingly.
[Remember, since today is not Friday, u may not get the exact results u expect from ur "real scenario". I just did it to find 7 days old data, not Friday to Friday !!]

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources