hot to round time to next day, next week or next month?

Hi Guys:

I have a scheduled task, should be run at every day, or every
first day of next week, or every first day of next month (from now).
I'm wondering if it's possible that:
Given today = March 13, 2006
Then URoundWeek(today) == March 20, 2006
URoundMonth(today) == April 1, 2006

Thanks in advance.

McKirahan
Tue Mar 14 09:50:37 CST 2006

"Apricot" <yilu@21cn.com> wrote in message
news:akmd12p7vo63br2s6qc91vpct4sekq3tfg@4ax.com...
> Hi Guys:
>
> I have a scheduled task, should be run at every day, or every
> first day of next week, or every first day of next month (from now).
> I'm wondering if it's possible that:
> Given today = March 13, 2006
> Then URoundWeek(today) == March 20, 2006
> URoundMonth(today) == April 1, 2006
>
> Thanks in advance.

Why did you mention "a scheduled task"; it doesn't seem relevant to your
question.

Also, today is "March 14, 2006".

Anyway, you'll have to define your rounding rules:

1) round to next day

Round up if "12:01 PM"?

2) round to next week

Round up if Wednesday?
(based on Sunday being the first day of the week?)

3) round to next month

Does it depend on the number of days in the month? For example,
02/14/2006 rounds up to 03/01/2006
03/16/2006 rounds up to 04/01/2006
Does 03/15/2006 round down to 03/01/2006?

What's your purpose?

Steve
Tue Mar 14 11:13:49 CST 2006

Apricot wrote:

>Hi Guys:
>
> I have a scheduled task, should be run at every day, or every
>first day of next week, or every first day of next month (from now).
> I'm wondering if it's possible that:
> Given today = March 13, 2006
> Then URoundWeek(today) == March 20, 2006
> URoundMonth(today) == April 1, 2006

You need to use the Weekday() function to work out what day of the week
you're on (1-7), and from that you can calculate how many days to add with
the DateAdd() function.

For the month, you just need to use DateSerial(Y,M,D), calculating the
appropriate Y and M from your input date (hint: the Year() and Month()
functions might be handy), using 1 for the D value.

--
Steve Foster [SBS MVP]
---------------------------------------
MVPs do not work for Microsoft. Please reply only to the newsgroups.

Dr
Wed Mar 15 06:48:35 CST 2006

JRS: In article <xn0ejq5ko2kshy300k@msnews.microsoft.com>, dated Tue,
14 Mar 2006 09:13:49 remote, seen in news:microsoft.public.scripting.vbs
cript, Steve Foster [SBS MVP] <steve.foster@picamar.co.uk> posted :
>Apricot wrote:

>> I have a scheduled task, should be run at every day, or every
>>first day of next week, or every first day of next month (from now).
>> I'm wondering if it's possible that:
>> Given today = March 13, 2006
>> Then URoundWeek(today) == March 20, 2006
>> URoundMonth(today) == April 1, 2006
>
>You need to use the Weekday() function to work out what day of the week
>you're on (1-7), and from that you can calculate how many days to add with
>the DateAdd() function.

One does not *need* to use the Weekday function, although one can do so.
The need is to add 1..7 days, so
X = Date
X = X + 1 + (VAST-X) mod 7

It can easily be seen that X is increased by 1..7, and that the result
goes up in steps of 7. VAST is of the form 7*N+K, where K selects which
day is the first of the week and N is large enough to ensure a positive
argument for mod. VAST = 8E6 fits the condition.

>For the month, you just need to use DateSerial(Y,M,D), calculating the
>appropriate Y and M from your input date (hint: the Year() and Month()
>functions might be handy), using 1 for the D value.

To avoid excess calculation, one should observe that parameter M is not
limited to 1..12. DateSerial(Y,M+1,1) happily accepts M=12.


--
© John Stockton, Surrey, UK. ?@merlyn.demon.co.uk Turnpike v4.00 MIME. ©
Web <URL:http://www.merlyn.demon.co.uk/> - w. FAQish topics, links, acronyms
PAS EXE etc : <URL:http://www.merlyn.demon.co.uk/programs/> - see 00index.htm
Dates - miscdate.htm moredate.htm js-dates.htm pas-time.htm critdate.htm etc.

Dr
Wed Mar 15 07:09:34 CST 2006

JRS: In article <H_-dnU8BherQe4vZRVn-pw@comcast.com>, dated Tue, 14 Mar
2006 09:50:38 remote, seen in news:microsoft.public.scripting.vbscript,
McKirahan <News@McKirahan.com> posted :
>"Apricot" <yilu@21cn.com> wrote in message
>news:akmd12p7vo63br2s6qc91vpct4sekq3tfg@4ax.com...

>> I have a scheduled task, should be run at every day, or every
>> first day of next week, or every first day of next month (from now).
>> I'm wondering if it's possible that:
>> Given today = March 13, 2006
>> Then URoundWeek(today) == March 20, 2006
>> URoundMonth(today) == April 1, 2006


>Anyway, you'll have to define your rounding rules:

Scheduling is used for future events; it does not apply to past ones.

>1) round to next day
>
>Round up if "12:01 PM"?

The OP is evidently not in the USA : out of politeness, you should not
use strange notations such as the 12-hour clock. 24-hour times are much
easier for others to follow, and can be understood by most Americans
too.

The next day is given by
D = Date
ND = D + 1

>2) round to next week
>
>Round up if Wednesday?
>(based on Sunday being the first day of the week?)

Why use that basis? The International Standard has weeks Monday to
Sunday. The OP is posting from 8 hours ahead of GMT, and has an address
at 21cn.com, and indicates that March 20, 2006 is the current answer.
That will be a Monday. It's unreasonable to treat him or her as wanting
to do things as they might be done in the USA.

The OP's English is good ; "next week" does not mean "nearest start-of-
week".

>3) round to next month
>
>Does it depend on the number of days in the month? For example,
> 02/14/2006 rounds up to 03/01/2006
> 03/16/2006 rounds up to 04/01/2006
>Does 03/15/2006 round down to 03/01/2006?

This is an internationally-used newsgroup, so it is bad manners to write
dates in FFF notation. If you cannot bring yourself to use YYYY-MM-DD,
at least follow the OP's lead and put months in words.

Next month has a clear meaning; if the OP had wanted "nearest 1st",
he/she should not have put "next month".

NM = DateSerial(Year(D), Month(D)+1, 1)


Have you considered taking an EFL course?

--
© John Stockton, Surrey, UK. ?@merlyn.demon.co.uk Turnpike v4.00 MIME. ©
Web <URL:http://www.merlyn.demon.co.uk/> - w. FAQish topics, links, acronyms
PAS EXE etc : <URL:http://www.merlyn.demon.co.uk/programs/> - see 00index.htm
Dates - miscdate.htm moredate.htm js-dates.htm pas-time.htm critdate.htm etc.

Apricot
Fri Mar 17 09:54:47 CST 2006

Thank everybody!
Maybe I didn't put myself clear, and the example is just too simple,
can't stand for a typical scenario.
The problem is, I'm doing a test. The AUT (application under test) has
scheduled tasks, which admin can set it run at every month, evey day
or every week. When he customize a weekly task, AUT will use next
sunday (0:00:00 24h by default) as the first time to run the task. In
our test, I can't wait to that long, so I have to adjust machine's
time. But the first thing is, I need to know what the time it is.
Now I use the below functions to do this:

Function RoundToMonth (varDateTime)
varDateTime = DateAdd("m", 1, varDateTime)
RoundToMonth = Year(varDateTime) & "/" & Month(varDateTime) & "/01"
End Function

Function RoundToWeek (varDateTime)
const worldStart = "1970-1-4 0:00:00"
secs = DateDiff("s", worldStart, varDateTime)
secs = (secs \ ( 7 * 24 * 3600) + 1) * (7 * 24 * 3600)

RoundToWeek = DateAdd("s", secs, worldStart)
End Function

Function RoundToDay (varDateTime)
'wscript.echo "before add " & varDateTime
varDateTime = DateAdd("d", 1, varDateTime)
'wscript.echo "after add " & varDateTime
RoundToDay = Year(varDateTime) & "/" & Month(varDateTime) & "/" &
Day(varDateTime)
'wscript.echo "After round " & varDateTime
End Function

Function RoundToHour (varDateTime)
Dim worldStart : worldStart = "1970/01/01 0:00:00"
hrs = DateDiff("h", worldStart, varDateTime)
RoundToHour = DateAdd("h", hrs + 1, worldStart)
End Function

It works fine on my machine.
RoundToMonth( "2006-12-29 0:00:00") yields "2007/1/01 0:00:00"
RoundToWeek( "2006-12-29 0:00:00") yields "2006-12-31"
Just as what I want.

Sorry for response late. It tooks me a while to find my post.

>Hi Guys:
>
> I have a scheduled task, should be run at every day, or every
>first day of next week, or every first day of next month (from now).
> I'm wondering if it's possible that:
> Given today = March 13, 2006
> Then URoundWeek(today) == March 20, 2006
> URoundMonth(today) == April 1, 2006
>
> Thanks in advance.

Dr
Sat Mar 18 07:49:55 CST 2006

JRS: In article <l6ml1295la33s96mf8ndcsbqu0hq6qjv4p@4ax.com>, dated
Fri, 17 Mar 2006 23:54:47 remote, seen in news:microsoft.public.scriptin
g.vbscript, Apricot <yilu@21cn.com> posted :

>Function RoundToMonth (varDateTime)
> varDateTime = DateAdd("m", 1, varDateTime)
> RoundToMonth = Year(varDateTime) & "/" & Month(varDateTime) & "/01"
>End Function

Function RTM(vDT)
RTM = DateSerial(Year(vDT), Month(vDT)+1, 1)
End Function

>Function RoundToWeek (varDateTime)
> const worldStart = "1970-1-4 0:00:00"
> secs = DateDiff("s", worldStart, varDateTime)
> secs = (secs \ ( 7 * 24 * 3600) + 1) * (7 * 24 * 3600)
>
> RoundToWeek = DateAdd("s", secs, worldStart)
>End Function

No need to go to seconds; work in days. I've already given an efficient
answer.

>Function RoundToDay (varDateTime)
> 'wscript.echo "before add " & varDateTime
> varDateTime = DateAdd("d", 1, varDateTime)
> 'wscript.echo "after add " & varDateTime
> RoundToDay = Year(varDateTime) & "/" & Month(varDateTime) & "/" &
>Day(varDateTime)
> 'wscript.echo "After round " & varDateTime
>End Function

Function RTD(vDT)
RTD = Int(vDT) + 1
End Function

>Function RoundToHour (varDateTime)
> Dim worldStart : worldStart = "1970/01/01 0:00:00"
> hrs = DateDiff("h", worldStart, varDateTime)
> RoundToHour = DateAdd("h", hrs + 1, worldStart)
>End Function

Function RTH(vDT)
RTH = CDate((Int(vDT*24) + 1)/24)
End Function

>It works fine on my machine.

Your functions are longer than necessary, and horribly inefficient.
Conversion between String and CDate, implicit or explicit, should be
done only when necessary.

Note that only times-of-day which are multiples of power-of-two
fractions of a day can be held exactly.

--
© John Stockton, Surrey, UK. ?@merlyn.demon.co.uk Turnpike v4.00 MIME. ©
Web <URL:http://www.merlyn.demon.co.uk/> - w. FAQish topics, links, acronyms
PAS EXE etc : <URL:http://www.merlyn.demon.co.uk/programs/> - see 00index.htm
Dates - miscdate.htm moredate.htm js-dates.htm pas-time.htm critdate.htm etc.

Apricot
Sat Mar 18 21:36:39 CST 2006

Amazing!

I never know that "Month" parameter can take a value greater than 12.
Your code is apparently more effictive. Thanks!

Dr John Stockton <jrs@merlyn.demon.co.uk> wrote at Sat, 18 Mar 2006
13:49:55 +0000:

>Function RTM(vDT)
> RTM = DateSerial(Year(vDT), Month(vDT)+1, 1)
> End Function

Dr
Sun Mar 19 16:00:55 CST 2006

JRS: In article <ebkp1257mtu4udnl9rki9v93mqq51dk9ii@4ax.com>, dated
Sun, 19 Mar 2006 11:36:39 remote, seen in news:microsoft.public.scriptin
g.vbscript, Apricot <yilu@21cn.com> posted :
>Amazing!
>
>I never know that "Month" parameter can take a value greater than 12.

Also less than 1.

Duplicating News in Mail is unnecessary and leads to *plonk*.

--
© John Stockton, Surrey, UK. ?@merlyn.demon.co.uk Turnpike v4.00 MIME ©
Web <URL:http://www.uwasa.fi/~ts/http/tsfaq.html> -> Timo Salmi: Usenet Q&A.
Web <URL:http://www.merlyn.demon.co.uk/news-use.htm> : about usage of News.
No Encoding. Quotes before replies. Snip well. Write clearly. Don't Mail News.

Steve
Sun Mar 19 20:02:35 CST 2006

Dr John Stockton wrote:

>JRS: In article <l6ml1295la33s96mf8ndcsbqu0hq6qjv4p@4ax.com>, dated
>Fri, 17 Mar 2006 23:54:47 remote, seen in news:microsoft.public.scriptin
>g.vbscript, Apricot <yilu@21cn.com> posted :
>
>>Function RoundToMonth (varDateTime)
>> varDateTime = DateAdd("m", 1, varDateTime)
>> RoundToMonth = Year(varDateTime) & "/" & Month(varDateTime) & "/01"
>>End Function
>
>Function RTM(vDT)
> RTM = DateSerial(Year(vDT), Month(vDT)+1, 1)
> End Function
>
>>Function RoundToWeek (varDateTime)
>> const worldStart = "1970-1-4 0:00:00"
>> secs = DateDiff("s", worldStart, varDateTime)
>> secs = (secs \ ( 7 * 24 * 3600) + 1) * (7 * 24 * 3600)
>>
>> RoundToWeek = DateAdd("s", secs, worldStart)
>>End Function
>
>No need to go to seconds; work in days. I've already given an efficient
>answer.

The answer you gave may be efficient, but its' function is not immediately
obvious. Using the Weekday function might be slightly less efficient, but
it has the advantage of being reasonably easy to understand.

Code that you can read later and immediately comprehend is often more
desirable than code that is a model of efficiency.

>>Function RoundToDay (varDateTime)
>> 'wscript.echo "before add " & varDateTime
>> varDateTime = DateAdd("d", 1, varDateTime)
>> 'wscript.echo "after add " & varDateTime
>> RoundToDay = Year(varDateTime) & "/" & Month(varDateTime) & "/" &
>>Day(varDateTime)
>> 'wscript.echo "After round " & varDateTime
>>End Function
>
>Function RTD(vDT)
> RTD = Int(vDT) + 1
> End Function
>
>>Function RoundToHour (varDateTime)
>> Dim worldStart : worldStart = "1970/01/01 0:00:00"
>> hrs = DateDiff("h", worldStart, varDateTime)
>> RoundToHour = DateAdd("h", hrs + 1, worldStart)
>>End Function
>
>Function RTH(vDT)
> RTH = CDate((Int(vDT*24) + 1)/24)
> End Function
>
>>It works fine on my machine.
>
>Your functions are longer than necessary, and horribly inefficient.
>Conversion between String and CDate, implicit or explicit, should be
>done only when necessary.

And your solutions work on the assumption that it is safe to implicitly
convert between datetime and float/int - how is that better? It happens to
be true in VBScript, but relying on it rather than using available
datetime functions is (IMHO) poor coding.


>Note that only times-of-day which are multiples of power-of-two
>fractions of a day can be held exactly.

And that's just the result of a sub-optimal datetime implementation. MS
could have done better.

--
Steve Foster [SBS MVP]
---------------------------------------
MVPs do not work for Microsoft. Please reply only to the newsgroups.

Steve
Sun Mar 19 20:06:57 CST 2006

Apricot wrote:

>Dr John Stockton <jrs@merlyn.demon.co.uk> wrote at Sat, 18 Mar 2006
>13:49:55 +0000:
>
>>Function RTM(vDT)
>> RTM = DateSerial(Year(vDT), Month(vDT)+1, 1)
>> End Function

>Amazing!
>
>I never know that "Month" parameter can take a value greater than 12.
>Your code is apparently more effictive. Thanks!

Even if that were not the case (and relying on a function accepting "bad"
values gracefully is not generally good coding practice), a simple If
statement to cope with Month(date) = 12 is not hard to use:

Function RTM(vDT)
If Month(vDT) = 12 Then
RTM = DateSerial(Year(vDT)+1,1,1)
Else
RTM = DateSerial(Year(vDT),Month(vDT)+1,1)
End If
End Function

--
Steve Foster [SBS MVP]
---------------------------------------
MVPs do not work for Microsoft. Please reply only to the newsgroups.

Dr
Mon Mar 20 17:52:05 CST 2006

JRS: In article <xn0ejxwzxo8k3g008@msnews.microsoft.com>, dated Sun, 19
Mar 2006 18:02:35 remote, seen in news:microsoft.public.scripting.vbscri
pt, Steve Foster [SBS MVP] <steve.foster@picamar.co.uk> posted :
>Dr John Stockton wrote:

>>No need to go to seconds; work in days. I've already given an efficient
>>answer.
>
>The answer you gave may be efficient, but its' function is not immediately
>obvious. Using the Weekday function might be slightly less efficient, but
>it has the advantage of being reasonably easy to understand.

That's a matter of the intelligence of the reader. ISTM that in any
code dealing with dates, any occurrence of a 7 is quite likely to refer
to the number of days in a week. And it's rarely difficult to
comprehend the operation of a function that short, if it is given (in
actual use) a descriptive name.


>>Your functions are longer than necessary, and horribly inefficient.
>>Conversion between String and CDate, implicit or explicit, should be
>>done only when necessary.
>
>And your solutions work on the assumption that it is safe to implicitly
>convert between datetime and float/int - how is that better? It happens to
>be true in VBScript, but relying on it rather than using available
>datetime functions is (IMHO) poor coding.

Well, this *is* a VBScript newsgroup. Users of DateDiff/DateAdd seem
quite frequently to be rather liberal in giving it CDate or String
parameters, requiring what may well be an implicit conversion.


>>Note that only times-of-day which are multiples of power-of-two
>>fractions of a day can be held exactly.
>
>And that's just the result of a sub-optimal datetime implementation. MS
>could have done better.

Indeed; they've followed a crude tradition (well-known to have been
originated by one who apparently did not understand the Gregorian
calendar).


BTW, do you know whether any combination of parameters for function
DatePart is specified, either explicitly or by description, to give the
ISO 8601 Week Number (as used outside NA); and if so whether anything is
said about Year Number, and whether the function gets 2003-12-29,
2007-12-31, and 2019-12-30 right in the latest versions?
<URL:http://www.merlyn.demon.co.uk/vb-date2.htm#WN> refers.

--
© John Stockton, Surrey, UK. ?@merlyn.demon.co.uk Turnpike v4.00 MIME. ©
Web <URL:http://www.merlyn.demon.co.uk/> - w. FAQish topics, links, acronyms
PAS EXE etc : <URL:http://www.merlyn.demon.co.uk/programs/> - see 00index.htm
Dates - miscdate.htm moredate.htm js-dates.htm pas-time.htm critdate.htm etc.

Dr
Mon Mar 20 17:56:11 CST 2006

JRS: In article <xn0ejxx3uodvnw009@msnews.microsoft.com>, dated Sun, 19
Mar 2006 18:06:57 remote, seen in news:microsoft.public.scripting.vbscri
pt, Steve Foster [SBS MVP] <steve.foster@picamar.co.uk> posted :
>
>Even if that were not the case (and relying on a function accepting "bad"