Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: How to create a date

    Hi,

    If given a date how can you get the date for monday of the following week?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    get the weekday of the date (using DATEPART function with DW)

    this gives you a number from 1 (sunday) to 7 (saturday) although you can change the first day of the week (default is sunday) using SET DATEFIRST

    after that, it's all just arithmetic

    but a question...

    if you happen to already be on a monday, you want the monday date that is 7 days ahead, right?

    and if you're on a sunday, does the "monday of the following week" mean the next day, or the monday that's 8 days away?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Posts
    212
    Ok i seem to have worked the above out but am struggling with the following:

    how can you find the next date for a day if all you have is the name of the day and a variable date to work with?

    e.g. if I have date field and a day field with a value of 'Tuesday' how can I work out the 1st date for that day (Tuesday) from that date or the 2nd Tuesday date from that date.

    so if date field is Mon 10th Oct (2011-10-10) and day field is 'Tuesday' how do I get the Date for the 1st Tuesday after this date i.e. Tues 11th Oct (2011-10-11)

    or if date field is Tues 11th Oct (2011-10-11) and day field is 'Friday' how do I get the Date for the 1st Friday after this date i.e.
    Fri 14th Oct (2011-10-14) or the 2nd Friday after this date i.e. Fri 21 Oct (2011-10-21)
    Last edited by ozzii; 10-06-11 at 13:22. Reason: addition

  4. #4
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937 View Post
    get the weekday of the date (using DATEPART function with DW)

    this gives you a number from 1 (sunday) to 7 (saturday) although you can change the first day of the week (default is sunday) using SET DATEFIRST

    after that, it's all just arithmetic

    but a question...

    if you happen to already be on a monday, you want the monday date that is 7 days ahead, right?

    and if you're on a sunday, does the "monday of the following week" mean the next day, or the monday that's 8 days away?
    if am on a sunday the monday of the following week would be the next day.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ozzii View Post
    so if date is Monday 10th Oct (10/10/2011) how do I get the Date for the 1st Tuesday after this date i.e. 11th Oct (11/10/2011)
    again, it's just arithmetic

    you want the result to give you dayofweek=3 (tuesday) when you add some number of days to the current dayofweek

    of course, MOD 7 is involved

    if today is thursday (day 5) add 5 to get to next tuesday (day 3)
    if today is friday (day 6) add 4 to get to next tuesday (day 3)
    if today is saturday (day 7) add 3 to get to next tuesday (day 3)
    if today is sunday (day 1) add 2 to get to next tuesday (day 3)
    if today is monday (day 2) add 1 to get to next tuesday (day 3)
    if today is tuesday (day 3) add 7 to get to next tuesday (day 3)
    if today is wednesday (day 4) add 6 to get to next tuesday (day 3)

    can you do the formula?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937 View Post
    you want the result to give you dayofweek=3 (tuesday) when you add some number of days to the current dayofweek

    of course, MOD 7 is involved

    if today is thursday (day 5) add 5 to get to next tuesday (day 3)
    if today is friday (day 6) add 4 to get to next tuesday (day 3)
    if today is saturday (day 7) add 3 to get to next tuesday (day 3)
    if today is sunday (day 1) add 2 to get to next tuesday (day 3)
    if today is monday (day 2) add 1 to get to next tuesday (day 3)
    if today is tuesday (day 3) add 7 to get to next tuesday (day 3)
    if today is wednesday (day 4) add 6 to get to next tuesday (day 3)

    I need result to give me a date for that day of the week. Plus the above only considers a Tuesday. I only used Tuesday as an example but the day field could be any day of the week so the above formula is going to become huge if i add all the combination of days of the week.
    Last edited by ozzii; 10-06-11 at 13:33.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ozzii View Post
    I need result to give me a date for that day of the week
    yes, i know

    when i wrote "add N to get to next tuesday" you would of course have to use microsoft's DATEADD function, with the DAY parameter, specifying N as the appropriate number of days to add, to today's date

    if you're using GetDate() you'll get a datetime value which is some time in the middle of the desired tuesday, but at least it'll be the correct tuesday

    helps?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937 View Post
    yes, i know

    when i wrote "add N to get to next tuesday" you would of course have to use microsoft's DATEADD function, with the DAY parameter, specifying N as the appropriate number of days to add, to today's date

    if you're using GetDate() you'll get a datetime value which is some time in the middle of the desired tuesday, but at least it'll be the correct tuesday

    helps?

    Code:
    select 'due date' = CASE datepart(dw,getdate())
                          WHEN 5 THEN dateadd(dd,5,getdate())
                          WHEN 6 THEN dateadd(dd,4,getdate())
                          WHEN 7 THEN dateadd(dd,3,getdate())
                          WHEN 1 THEN dateadd(dd,2,getdate())
                          WHEN 2 THEN dateadd(dd,1,getdate())
                          WHEN 3 THEN dateadd(dd,7,getdate())
                          WHEN 4 THEN dateadd(dd,6,getdate())
                        END
    this would only work for a Tuesday. I need something a bit more flexible


    If I have a table as follows:

    Code:
    ID   |   Date Stamp      |  Day of week
    ---------------------------------
    1    |    2011-10-10     |  Tuesday
    2    |    2011-10-11     |  Friday
    3    |    2011-10-10     |  Friday
    for each record how can you get the first date for the day of week after the date stamp.


    So I need results as follows:

    Code:
    ID  | Due Date
    ---------------
    1   | 2011-10-11
    2   | 2011-10-14
    3   | 2011-10-14

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ozzii View Post
    If I have a table as follows:

    Code:
    ID   |   Date Stamp      |  Day of week
    ---------------------------------
    1    |    2011-10-10     |  Tuesday
    2    |    2011-10-11     |  Friday
    3    |    2011-10-10     |  Friday
    you lost me right here

    how can 2011-10-10 be both tuesday and friday?

    maybe you need to do your calculations in your application code and then simply pass a list of desired dates to the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937 View Post
    you lost me right here

    how can 2011-10-10 be both tuesday and friday?

    maybe you need to do your calculations in your application code and then simply pass a list of desired dates to the query
    May be i should have made the column names more meaning full. Date stamp and day of week in the table are not related. Date stamp is the date the record was created and is used as the start date to calculate the next date which will have the same day of week as the due day for each id

    so for id 1 the next date from 2011-10-10 which will have a day of week as a Tuesday will be 2010-10-11
    similarly for id 2 the next date from 2011-10-11 which will have a day of week as a Friday will be 2010-10-14 and same for id 3

    hope that makes sense.

    Code:
    ID   |   Start Date       |  Due Day
    ---------------------------------
    1    |    2011-10-10     |  Tuesday
    2    |    2011-10-11     |  Friday
    3    |    2011-10-10     |  Friday
    Last edited by ozzii; 10-07-11 at 03:33. Reason: amend

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, i get it

    okay, it's a bit more complicated, so yes, you will need something more flexible

    but in the end, it's all just arithmetic
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •