day of 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 2005 Forums

Transact-SQL (2005)

day of week

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-07-06 : 01:13:04

i am doing where DATEPART(WEEKDAY, @date)<>7 and DATEPART(WEEKDAY, @date)<>1
as part of my query but it is still returnign dates that fall on saturday or sunday - why does this not work? and what is the correct way to do this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-06 : 01:17:44

Hard to tell without seing the full query. Do you have an OR clause somewhere?
Is your language setting set to other than ENGLISH?

To get all days except SUNDAY and SATURDAY no matter that language or culture setting, use this

WHERE DATEDIFF(DAY, 0, @Date) %7 BETWEEN 0 AND 4 -- Mon to Fri

N 56°04'39.26"
E 12°55'05.63"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-07-06 : 05:41:47

what am i doing wrong here

ALTER PROCEDURE [dbo].[checkifholiday]
-- Add the parameters for the stored procedure here
@date datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

if exists(select * from bankholidays where holidaydate=@date) or ( DATEPART(WEEKDAY, @date)=7 or DATEPART(WEEKDAY, @date)=1)

select min(dates) as next_date from
(
select dateadd(day,number,@date) as dates from master..spt_values where type='p' and number>=0
) as t1
left join bankholidays as t2
on t1.dates=t2.holidaydate

where t2.holidaydate is null

else
select @date as next_date

END
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-06 : 05:48:43

SELECT TOP(1) i.theDate AS Next_Available_Dt
FROM (
SELECT DATEADD(DAY, number, @Date)
FROM master.dbo.spt_values
WHERE Type = 'P'
AND DATEDIFF(DAY, 0, DATEADD(DAY, number, @Date)) % 7 < 5
) AS i(theDate)
LEFT JOIN dbo.Holidays AS h ON h.Holiday_Dt = i.theDate
WHERE h.Holiday_Dt IS NULL
ORDER BY i.theDate

N 56°04'39.26"
E 12°55'05.63"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-07-06 : 06:06:52

thanks :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources