Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Day of Week function in TSQL

Posted on 2004-04-08
Medium Priority
9,676 Views
I'm attempting to write a function that will provide me with next day of a given date.  For instance, if the date I have is April 8 2004 (thursday) and I need the first monday following that date.

The problem I've run into is that I can't seem to find a way of detecting what the weekday of a date is.

I've gone this far...

Create Function dbo.GetNextDay(@SDate DateTime, @Day Int)
Returns DateTime
AS
Begin
Declare @Ret DateTime,
@DayDif Int

--HERE I NEED TO SET @DAYDIF TO THE NUMBER OF DAYS BETWEEN THE CURRENT DAY AND THE DAY I'M LOOKING FOR

Return @Ret
End
Go

0
Question by:JackieLee
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 3

LVL 6

Expert Comment

ID: 10782411
You use DatePart as so...

select DatePart(dw,'2 nov 2003')
0

LVL 6

Expert Comment

ID: 10782423
Also the @@DATEFIRST global tells you the first day of week.  It can be set too

Set @@DATEFIRST = 1 --sets the first day of week to Monday.
0

LVL 26

Expert Comment

ID: 10782437
this solution works whith any langage settings

--with  @day = 1 for Monday, 2 for Wednesday, and so on through 7 for Sunday.
set @datedif = (15+@day-@@datefirst-datepart(dw, getdate()))%7

Hilaire
0

LVL 26

Expert Comment

ID: 10782448
Create Function dbo.GetNextDay(@SDate DateTime, @Day Int)
Returns DateTime
AS
Begin
Declare @Ret DateTime,
@DayDif Int
set @DayDif = (15+@day-@@datefirst-datepart(dw, getdate()))%7
Return @Ret
End
Go
0

LVL 6

Expert Comment

ID: 10782455
Hilaire,

Should getdate() not be @Sdate?
0

LVL 26

Expert Comment

ID: 10782459
sorry, tested with getdate(), but I think you need if with @SDate instead

Create Function dbo.GetNextDay(@SDate DateTime, @Day Int)
Returns DateTime
AS
Begin
Declare @Ret DateTime,
@DayDif Int
set @DayDif = (15+@day-@@datefirst-datepart(dw, @SDate))%7
Return @Ret
End
Go
0

LVL 26

Expert Comment

ID: 10782465
Thanks billy21,
I think ours posts collided ; )
0

LVL 26

Accepted Solution

Hilaire earned 1000 total points
ID: 10782491
BTW, the whole thing could write

Create Function dbo.GetNextDay(@SDate DateTime, @Day Int)
Returns DateTime
AS
Begin
End
Go

Note I changed the dateadd, the parameters order was wrong and the "d," was missing
0

LVL 1

Author Comment

ID: 10782501
Thanks Hilaire and Billy.  I chose the more complete/better response.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides? Â Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff lâ€¦