• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9701
  • Last Modified:

Day of Week function in TSQL

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

      Set @Ret = DateAdd(@SDate,@DayDif)

      Return @Ret
End
Go


Thanks in advance.
0
JackieLee
Asked:
JackieLee
  • 5
  • 3
1 Solution
 
billy21Commented:
You use DatePart as so...

select DatePart(dw,'2 nov 2003')
0
 
billy21Commented:
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
 
HilaireCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
HilaireCommented:
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
     Set @Ret = DateAdd(@SDate,@DayDif)
     Return @Ret
End
Go
0
 
billy21Commented:
Hilaire,

Should getdate() not be @Sdate?
0
 
HilaireCommented:
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
     Set @Ret = DateAdd(@SDate,@DayDif)
     Return @Ret
End
Go
0
 
HilaireCommented:
Thanks billy21,
I think ours posts collided ; )
0
 
HilaireCommented:
BTW, the whole thing could write

Create Function dbo.GetNextDay(@SDate DateTime, @Day Int)
Returns DateTime
AS
Begin
     Return DateAdd(d, (15+@day-@@datefirst-datepart(dw, @SDate))%7, @SDate)
End
Go

Note I changed the dateadd, the parameters order was wrong and the "d," was missing
0
 
JackieLeeAuthor Commented:
Thanks Hilaire and Billy.  I chose the more complete/better response.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now