Results 1 to 11 of 11
Thread: How to create a date

100611, 08:09 #1Registered User
 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?

100611, 08:31 #2SQL Consultant
 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?

100611, 11:48 #3Registered User
 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 (20111010) and day field is 'Tuesday' how do I get the Date for the 1st Tuesday after this date i.e. Tues 11th Oct (20111011)
or if date field is Tues 11th Oct (20111011) and day field is 'Friday' how do I get the Date for the 1st Friday after this date i.e.
Fri 14th Oct (20111014) or the 2nd Friday after this date i.e. Fri 21 Oct (20111021)Last edited by ozzii; 100611 at 13:22. Reason: addition

100611, 13:13 #4Registered User
 Join Date
 Mar 2007
 Posts
 212

100611, 13:19 #5SQL Consultant
 Join Date
 Apr 2002
 Location
 Toronto, Canada
 Posts
 20,002
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?

100611, 13:30 #6Registered User
 Join Date
 Mar 2007
 Posts
 212
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; 100611 at 13:33.

100611, 13:35 #7SQL Consultant
 Join Date
 Apr 2002
 Location
 Toronto, Canada
 Posts
 20,002
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?

100611, 14:01 #8Registered User
 Join Date
 Mar 2007
 Posts
 212
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
If I have a table as follows:
Code:ID  Date Stamp  Day of week  1  20111010  Tuesday 2  20111011  Friday 3  20111010  Friday
So I need results as follows:
Code:ID  Due Date  1  20111011 2  20111014 3  20111014

100611, 17:44 #9SQL Consultant
 Join Date
 Apr 2002
 Location
 Toronto, Canada
 Posts
 20,002

100711, 02:55 #10Registered User
 Join Date
 Mar 2007
 Posts
 212
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 20111010 which will have a day of week as a Tuesday will be 20101011
similarly for id 2 the next date from 20111011 which will have a day of week as a Friday will be 20101014 and same for id 3
hope that makes sense.
Code:ID  Start Date  Due Day  1  20111010  Tuesday 2  20111011  Friday 3  20111010  Friday
Last edited by ozzii; 100711 at 03:33. Reason: amend

100711, 03:45 #11SQL Consultant
 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