how to count no of sundays - 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

General SQL Server Forums

New to SQL Server Programming

how to count no of sundays

Author

Topic

born2acheive
Yak Posting Veteran

65 Posts

Posted - 2007-01-07 : 03:16:49

hi,if we will two dates ie 2006/01/13 ,2006/01/30 and i want to count the no of sundays with in this date and i want to find in which dates suday is comming.please give me query for this please

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-07 : 03:42:14

you can use DATEPART(weekday, @mydatetime) to find what day of the week a date lands on. returns a 1-based int where 1=sunday, 2=monday, etc. the rest is just math. :)

http://msdn2.microsoft.com/en-us/library/ms174420.aspx

www.elsasoft.org

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-07 : 05:46:53

The DATEPART function depends on the SET DATEFIRST setting. Read about it in Books Online.
An easier way is to write

SELECT count(*) FROM F_TABLE_DATE('20060113', '20060130') WHERE weekday_name = 'sun'

Peter Larsson
Helsingborg, Sweden

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-07 : 11:13:17

I wasn't aware of that little gem.

you mean this one from MVJ right?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

www.elsasoft.org

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-07 : 14:59:18

Exactly. A very handy piece of code...

Peter Larsson
Helsingborg, Sweden

born2acheive
Yak Posting Veteran

65 Posts

Posted - 2007-01-07 : 22:12:11

hi peso i run your query but it shows error as

Invalid object name 'F_TABLE_DATE'.

how to cross thius error and one more help that i need to list out the dates which is a sunday,please help me

born2acheive
Yak Posting Veteran

65 Posts

Posted - 2007-01-07 : 22:25:48

hi peso i used this procedure to count the sundays but i need the dates also.

CREATE PROCEDURE sp_sunday (@a datetime,@b datetime) As
begin
select datediff(ww,@a,@b) + case when datename(dw,@a) = 'sunday' then 1 else 0 end as [no of sundays],
datediff(ww,@a,@b) + case when datename(dw,@b) = 'saturday' then 1 else 0 end as [no of saturdays]
end
GO

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-07 : 22:27:58

quote:
Originally posted by born2acheive

hi peso i run your query but it shows error as

Invalid object name 'F_TABLE_DATE'.

how to cross thius error and one more help that i need to list out the dates which is a sunday,please help me

that's because you need to create it first. see the link i posted above.

www.elsasoft.org

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-07 : 22:28:21

quote:
Originally posted by born2acheive

hi peso i run your query but it shows error as

Invalid object name 'F_TABLE_DATE'.

how to cross thius error and one more help that i need to list out the dates which is a sunday,please help me

Didn't you read this ?

quote:
Originally posted by jezemine

I wasn't aware of that little gem.

you mean this one from MVJ right?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

www.elsasoft.org

born2acheive
Yak Posting Veteran

65 Posts

Posted - 2007-01-07 : 22:47:00

hi jezemine and khtan

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[F_TABLE_DATE]')
and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[F_TABLE_DATE]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
create function dbo.F_TABLE_DATE
(
@FIRST_DATE datetime,
@LAST_DATE datetime
)

i am getting this error:

Server: Msg 170, Level 15, State 1, Procedure F_TABLE_DATE, Line 5
Line 5: Incorrect syntax near ')'.

please help me to solve this

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-07 : 22:59:42

looks like you did not have the entire script. The entire script for the function is 700+ lines

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-07 : 23:03:21

yea, you included everything but the most important part: the function body.

www.elsasoft.org

born2acheive
Yak Posting Veteran

65 Posts

Posted - 2007-01-07 : 23:08:29

hi khtan i run that 739 lines and sucessfully completed.then i used this which peso given:

SELECT count(*) FROM F_TABLE_DATE('20060113', '20060130') WHERE weekday_name = 'sun'
i t gives the count, ined the sunday dates has to be listed for that what is the query please giveme

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-07 : 23:17:30

this will give you the DATE


SELECT DATE FROM F_TABLE_DATE('20060113', '20060130') WHERE WEEKDAY_NAME = 'Sun'

born2acheive
Yak Posting Veteran

65 Posts

Posted - 2007-01-07 : 23:19:53

hi khtan thanks a lot i got it

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-07 : 23:45:06

I don't believe we actually had to tell how to get all the dates by substituting COUNT(*) with Date...

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-08 : 10:07:19

quote:
Originally posted by Peso

I don't believe we actually had to tell how to get all the dates by substituting COUNT(*) with Date...

Peter Larsson
Helsingborg, Sweden

Or to tell them to include all the code for the function.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 10:19:47

This gets more funny when thinking about OP's member name.

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-08 : 10:32:18

Is it intentional irony having the word achieve misspelled in born2acheive?

Like having a “Born Too Loose” tattoo.

CODO ERGO SUM

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-08 : 15:54:52

born2spel

www.elsasoft.org

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-01-08 : 20:43:10

Another option is the DATENAME function, which I do not believe is dependent on SET DATEFIRST.

For example this should return 'Sunday':

SELECT DATENAME(dw, CAST('20070107' AS DATETIME))

-Ryan

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources