Function that finds Sundays

  • Hey there group,

    I need to build a function that takes in a date and then returns the corresponding Sunday to that date.

    Any ideas?

    Marty

  • You can do something like this.

    Declare @Date datetime

    Set @Date = getdate()

    Select @Date -

    CASE datename(weekday,@Date)

    WHEN 'Monday' THEN 1

    WHEN 'Tuesday' THEN 2

    WHEN 'Wednesday' THEN 3

    WHEN 'Thursday' THEN 4

    WHEN 'Friday' THEN 5

    WHEN 'Saturday' THEN 6

    WHEN 'Sunday' THEN 0

    END

  • marty.seed (11/21/2008)


    Hey there group,

    I need to build a function that takes in a date and then returns the corresponding Sunday to that date.

    Any ideas?

    Marty

    Could you please provide some examples of what your are looking for? I wouldn't know which Sunday date you'd want for a given date just based on your question.

  • select getdate() + 7 - Datepart(dw, getdate())

  • Glen (11/21/2008)


    select getdate() + 7 - Datepart(dw, getdate())

    Given 2008-11-19 which Sunday do you want, 2008-11-16 or 2008-11-23?

  • Glen (11/21/2008)


    select getdate() + 7 - Datepart(dw, getdate())

    Also, on my server here at work, the above returns tomorrow (Saturday), not a Sunday.

  • Sorry, good question. The next Sunday

    So if I was to pass in todays date I would get 11/23/08

  • Given 2008-11-19 which Sunday do you want, 2008-11-16 or 2008-11-23

    Lynn, I hope this is not a question addressed to me? I am not providing interpreter's services 🙂

    I just provided a one line solution which requestor can modify accordingly to the missed part of his/her question. 🙂

    Use it as an example if it is helpful...

  • Directed more to the OP, but based on your suggestion, which didn't work on my servers here at work. It returns the Saturday following the given date, not Sunday. But then again, which Sunday does the OP want?

  • Also, on my server here at work, the above returns tomorrow (Saturday), not a Sunday.

    You have to check what are the settings on your server?

    select @@datefirst

    I believe default is 7 i.e. default first day of the week is Sunday. On mine default is set to 1.

  • But then again, which Sunday does the OP want?

    Sorry, good question. The next Sunday

  • select getdate() + 7 - Datepart(dw, getdate())

    This only works if @@DATEFIRST = 1 (Monday)

    An expression that returns the date of the Sunday in the current week regardless of the value of @@DATEFIRST is as follows:

    SELECT DATEADD(day, (1 - DATEPART(dw, GETDATE())) + (7 - @@DATEFIRST), GETDATE())

    The Sunday that is returned could be before or after the current date depending on the value of @@DATEFIRST, so if you need the previous Sunday or the next Sunday then you need a different expression.

  • marty.seed (11/21/2008)


    Sorry, good question. The next Sunday

    So if I was to pass in todays date I would get 11/23/08

    What do you want it to return if today is Sunday, today or 7 days later?

  • andrewd.smith (11/21/2008)


    select getdate() + 7 - Datepart(dw, getdate())

    This only works if @@DATEFIRST = 1 (Monday)

    An expression that returns the date of the Sunday in the current week regardless of the value of @@DATEFIRST is as follows:

    SELECT DATEADD(day, (1 - DATEPART(dw, GETDATE())) + (7 - @@DATEFIRST), GETDATE())

    The Sunday that is returned could be before or after the current date depending on the value of @@DATEFIRST, so if you need the previous Sunday or the next Sunday then you need a different expression.

    I think, someone else will need to test, that this is what is needed to get the Sunday following the date given:

    SELECT DATEADD(day, (8 - DATEPART(dw, GETDATE())) + (7 - @@DATEFIRST), GETDATE())

  • The following returns the date that is the next Sunday. If today is Sunday, it returns today.

    SELECT DATEADD(day, (15 - DATEPART(dw, GETDATE()) - @@DATEFIRST) % 7, GETDATE())

    The following returns the date that is the next Sunday. If today is Sunday, it does not return today.

    SELECT DATEADD(day, (14 - DATEPART(dw, GETDATE()) - @@DATEFIRST) % 7 + 1, GETDATE())

    The following returns the date that is the previous Sunday. If today is Sunday, it returns today.

    SELECT DATEADD(day, (1 - DATEPART(dw, GETDATE()) - @@DATEFIRST) % 7, GETDATE())

    The following returns the date that is the previous Sunday. If today is Sunday, it does not return today.

    SELECT DATEADD(day, (2 - DATEPART(dw, GETDATE()) - @@DATEFIRST) % 7 - 1, GETDATE())

Viewing 15 posts - 1 through 15 (of 33 total)

You must be logged in to reply to this topic. Login to reply