Working with Dates

  • Article

By The Microsoft Scripting Guys

Sesame Script

Welcome to Sesame Script, the column for beginning script writers. The goal of this column is to teach the very basics of Windows scripting for system administration automation. We’ll provide you with the information you’ll need to begin reading and understanding scripts and to start modifying those scripts to suit your own needs. If there’s anything in particular about scripting you’re finding confusing, let us know; you’re probably not alone.

Check the Sesame Script Archives to see past articles.

On This Page

Introduction to Dates…and Shrimp
Topic of the Day
Topic of the Date
Topic of the WMI Dates
Topic of the Shrimp

Introduction to Dates…and Shrimp

A shrimp’s heart is in its head. Does that mean it always suffers from a pounding headache?

That’s right, if you don’t know what else to do, start with a bad joke. Okay, a really bad joke. At least we’ve succeeded at one thing today. And a shrimp’s heart really is in its head, so if you didn’t know that you just learned something new.

Well, we’ve had a productive day today, let’s call it quits and go home.

What? This month’s Sesame Script? You’re kidding, right? After all the effort we put into finding out about shrimp anatomy you expect us to teach you something about scripting too? No way; we’re outta here.

Did we just say we weren’t going to talk about scripting? We were just kidding. After all, that’s our job, right? And the Scripting Guys are very devoted to their jobs. And it’s just a coincidence that we happened to run into our manager on our way out the door.

Topic of the Day

Let’s start with an introduction to today’s topic. That’s a good idea, right? Just as soon as we come up with one that’s exactly what we’ll do.

Anyone know what time it is? (All we know is that it’s still not time to go home.)

Anyone know what day it is? (Still a weekday, huh? Okay.)

Anyone know what year it is? (Hey, we only had one margarita at lunchtime. Maybe two.)

Speaking of years…hey, look at that, there’s our topic. Let’s talk about dates. Maybe if we waste enough time on that - er, um, we mean to say…maybe if we go into an in-depth discussion of dates you really will learn something about scripting from this article.

Topic of the Date

No, we have no idea what that heading means.

Maybe we should just show you a script. Here you go:

today = Date

Wscript.Echo "Two months from today: " & DateAdd("m", 2, today)
Wscript.Echo "Days left in the year: " & DateDiff("d", today, #1/1/2008#)
Wscript.Echo "Day: " & Day(today)
Wscript.Echo "Month: " & Month(today)
Wscript.Echo "Year: " & Year(today)
Wscript.Echo "Day of the week: " & Weekday(today)
dy = Weekday(today)
Wscript.Echo "Day of the week, readable: " & WeekdayName(dy)

This script gives you a look at some of the things you can do with dates. Since we’re stuck here at our desks (our manager wandered by again, just to check on us) we’ll go ahead and walk through the script.

We start by calling the VBScript Date function, which simply returns the current system date. We assign that date to the variable today. We’ll use the date stored in that variable throughout the rest of the script:

today = Date

Note that there’s also a function by the name of Now, a function that also returns the current date. However, in addition to the date, Now returns the current time as well. In this script, however, we’re just going to concern ourselves with the date. Which is why we used Date instead of Now.

Next we echo back a statement that gives us the date two months from today. We do that by calling the DateAdd function:

Wscript.Echo "Two months from today: " & DateAdd("m", 2, today)

We’ve passed three parameters to the DateAdd function. The first is the letter m. We’ve enclosed the m in quotes because we’re passing a string value. If we passed an m without the quotes, DateAdd would think we’re passing a variable named m. In turn, because we haven’t assigned anything to a variable named m, DateAdd would get a little grumpy about all this. Like we said, the “m” that we passed in stands for month. We’re telling DateAdd that we want to add to the month part of the date. Can we add to other parts of the date? Of course; here are our choices:

yyyy

year

q

quarter

m

month

y

day of year

d

day

w

weekday

ww

week of year

h

hour

n

minute

s

second

As you can see, if we had included the time in our date variable we could add to the time too.

For the second parameter we’ve passed in a 2. This parameter tells DateAdd how much time we want to add. In this case we’re adding 2 to the months, so if it’s currently April, adding 2 will make it June. If only it were really that easy to jump into summer! What would be even better would be if it were that easy to go back in time. Come on, there must be something in your past you’d like to go back and change. And no, we don’t want to hear about it. But DateAdd can go back in time; simply pass a negative number. Want to retrieve the date two months ago? Simple:

DateAdd("m", -2, today)

The final parameter is the date we’re adding to, in this case the date stored in our today variable. So if today’s date is April 13, 2007, we’d see this when we run the script:

Two months from today: 6/13/2007

Yes, we know, not everyone in the world formats their dates like that. But we do, so that’s what we’re showing you. And really, that’s the great thing about these date functions - it doesn’t matter how your date is formatted. VBScript knows to check your regional settings and work with the correct part of the date.

Note: Before we move on we want to throw in a little more explanation for those of you who are absolute beginners. Let’s take another look at this line of code:

Wscript.Echo "Two months from today: " & DateAdd("m", 2, today)

In the first part we call Wscript.Echo. As you probably know, this will display to the screen whatever follows the Echo command. In this case, the first thing that follows the Echo command is the string “Two months from today: ”. After that we have an ampersand (&). This ties together the string and whatever follows the & so that everything can be displayed by a single Wscript.Echo statement. (See the Sesame Script article on VBScript Special Characters for more on combining values.) We then have our DateAdd function call. This function call returns a value, and it’s that return value that we’ll display. This might be easier to understand if we break it down a little:

dt = DateAdd("m", 2, today)
Wscript.Echo "Two months from today: " & dt

Here we’ve simply broken things up a bit to make it more readable. We first assign the output of the DateAdd function to the variable dt, then we echo back the string plus the value stored in dt.

The rest of the examples in this column work pretty much the same way.

All right, next line:

Wscript.Echo "Days left in the year: " & DateDiff("d", today, #1/1/2008#)

Here we’re calling the DateDiff function. DateDiff returns the difference between two dates in whatever interval you choose. We’ve decided to use DateDiff to find out how many days are left in the year. (We’d check how many days are left until retirement, but if we even knew what our retirement date was we’re sure it would just depress us to find out how far away that is.)

The first parameter we pass to DateDiff is the string “d”, which tells DateDiff we want to work with days. You use the same values here that we showed you in the table above. The second parameter is the first date you want to compare, the third parameter is the second date. For the first date we simply used today’s date (which, you’ll recall, we stored in the appropriately-named today) variable. But take a look at the second date. To find out how many days are left in the year, we find out how many days there are between today’s date and January 1 of next year (2008). To make it clear to VBScript that we’re passing a date rather than a string, a number, or some other type of data, we enclose the date in pound signs (#). (To learn more about data types and why they’re important, take a look at the Sesame Script article What Type of Data is That?.)

If you run this script on April 13, 2007, the output from this line of code will look like this:

Days left in the year: 263

Moving on…

No, we’re not rushing, it really is time to move on to the next line in the script. As a matter of fact, let’s move on to the next three lines:

Wscript.Echo "Day: " & Day(today)
Wscript.Echo "Month: " & Month(today)
Wscript.Echo "Year: " & Year(today)

It shouldn’t be too difficult to figure out what these lines of code are doing: they’re simply returning specific parts of the given date. The first line calls the Day function and returns the day contained in the date stored in the today variable. The second line does the same thing with the month by calling the Month function, and the third calls the Year function to return the year. On April 13, 2007, our output looks like this:

Day: 13
Month: 4
Year: 2007

And, like we said, it doesn’t matter how you format your date. Regardless of whether your regional settings display April 13, 2007 as 4/13/2007 or 13/4/2007 or something else, VBScript will recognize that and these functions will return the correct part of the date.

The next thing we want to know is the day of the week:

Wscript.Echo "Day of the week: " & Weekday(today)

As you can see, to retrieve the day of the week that a specific date falls on we call the Weekday function. If we run this code on Friday April 13, 2007, this will be our output:

Day of the week: 6

Yes, that’s right, the day of the week is 6. What were you expecting: Friday, maybe? Well, if so, you were wrong. If someone asks you what day it is today, you can confidently reply “6.”

On the bright side, that will be the last time anyone asks you what day it is.

Your other option is to actually figure out what day “6” is. There are a couple of ways to do this; in fact, VBScript has some constants that pretty much spell out what the actual days is:

vbSunday

1

vbMonday

2

vbTuesday

3

vbWednesday

4

vbThursday

5

vbFriday

6

vbSaturday

7

That means that you can just check to see if the value returned by Weekday is equal to vbFriday, and, if it is, you know it’s Friday. Or you could do what we did in the final two lines of our script:

dy = Weekday(today)
Wscript.Echo "Day of the week, readable: " & WeekdayName(dy)

Here we again call the Weekday function, but this time instead of simply echoing back the result we assign that result to the variable dy. Next we echo back the readable version of the day of the week by calling the WeekdayName function and passing it dy, the value returned from Weekday.

Note: Yes, we could have saved ourselves a line of code and done this:

Wscript.Echo "Day of the week, readable: " & WeekdayName(Weekday(today))

But that looked pretty confusing so we didn’t. But you can if you want.

Now instead of getting back the oh-so-useful “6”, we get back this:

Day of the week, readable: Friday

And there you have it. There are several other date-related function in VBScript, feel free to play around with them on your own. You’ll find them in the VBScript Language Reference.

Topic of the WMI Dates

That heading doesn’t make sense either, but we were on a roll so we just went with it.

If you do much system administration scripting you pretty much have to use Windows Management Instrumentation (WMI) at some point. One of the really fun things about WMI is the way it handles dates. (Sarcasm? From us? Never!) WMI stores dates in UTC format. For example, let’s take a look at a script that tells us when our operating system was installed:

strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set objOS = objWMIService.ExecQuery("Select * from Win32_OperatingSystem")
 
For Each strOS in objOS
    Wscript.Echo strOS.InstallDate
Next

We’re not going to explain this script in detail; all it’s doing is connecting to the WMI service on the local computer and querying for all instances of the class Win32_OperatingSystem. These instances will contain information about the currently-installed operating system. The query will return a collection (even though there will never be more than one operating system returned), so we loop through the collection and echo back the InstallDate property. Here’s what we get:

20041201092959.000000-480

Yes, believe it or not that’s a date. Even more unbelievable, it is possible to figure this out:

  • 2004 - the first four characters are the year

  • 12 - the next two characters are the month

  • 01 - the next two characters are the day

  • 092959.000000 - the rest of this is the time, 9:29:59 AM

  • -480 - offset from GMT (Greenwich Mean Time)

Now, we could sit down and really think this out, trying to figure out how to turn this into a readable date. We could go through the VBScript Language Reference and the WMI SDK, or…we could go to the Script Repository and steal the answer. We like that last option best:

Function WMIDateStringToDate(dtmInstallDate)
    WMIDateStringToDate = CDate(Mid(dtmInstallDate, 5, 2) & "/" & _
        Mid(dtmInstallDate, 7, 2) & "/" & Left(dtmInstallDate, 4) _
            & " " & Mid (dtmInstallDate, 9, 2) & ":" & _
                Mid(dtmInstallDate, 11, 2) & ":" & Mid(dtmInstallDate, _
                    13, 2))
End Function

We found this script in the Script Repository under Scripting Techniques, Dates and Times. This is actually only part of the script, we’ll show you the whole thing in a little bit. But this is the part we’re concerned with right now. This is a function named WMIDateStringToDate that converts a WMI UTC date into something readable. Simply copy this function into any script where you’re retrieving dates from WMI, then pass the date to this function and it will return a nicely-formatted date to you.

Let’s start by taking a look at the function. We’d look at it one line at a time, but if you noticed all the underscores (_) at the end of each line you’ve realized that it’s really one long line of script code. But we’ll take it one piece at a time. We start with this:

WMIDateStringToDate =

WMIDateStringToDate is the name of our function. The way we return a value from a function is by assigning a value to the name of the function, which is what we’re doing here. The results of the equation to the right of the equals sign is what will be returned when the function is called from the main part of the script. (For more on functions and how they work see the Sesame Script article Functions, Subroutines, and How to Call Them From Other Scripts.)

Next, we see that the entire equation is passed as a parameter to the CDate function:

CDate(...)

As we’ll see in a moment, everything inside the parentheses turns out to be a string. We’d like to return an actual date from our function rather than a string, so we call CDate to convert the string to a date. The first thing we do inside CDate is start to build our date string:

Mid(dtmInstallDate, 5, 2)

The Mid function returns a portion of a string. The first parameter is the string we want to work with; in this case the date string that we got from WMI (stored in the dtmInstallDate variable). We’re going to start our reformatted date with the month. If you’ll recall, the month starts at the fifth character in the date string. The second parameter to Mid is the character we want to start at (5), and the third parameter is how many characters we want to retrieve. The month takes two characters, so we pass 2 as the third parameter. (See the Sesame Script article Working with Strings for more on the Mid - and Left and Right - functions.)

We now have the month. Next comes this:

& "/"

Here we’re simply tacking a forward slash (/) onto the end of the month before we move on to the day. (Hint: if you format your dates as Day/Month, simply swap the first two Mid functions in the statement.) We’re not going to walk through the rest of this, it’s just a combination of Mid and Left functions all combined with &s and /s and :s to make up a date and time. To see a full explanation of stringing a date together see the Sesame Script article Working with String.

Now here’s the whole script, combining our WMI query for the operating system installation date with our conversion function:

strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set objOS = objWMIService.ExecQuery("Select * from Win32_OperatingSystem")
 
For Each strOS in objOS
    dtmInstallDate = strOS.InstallDate
    strReturn = WMIDateStringToDate(dtmInstallDate)
    Wscript.Echo strReturn 
Next
 
Function WMIDateStringToDate(dtmInstallDate)
    WMIDateStringToDate = CDate(Mid(dtmInstallDate, 5, 2) & "/" & _
        Mid(dtmInstallDate, 7, 2) & "/" & Left(dtmInstallDate, 4) _
            & " " & Mid (dtmInstallDate, 9, 2) & ":" & _
                Mid(dtmInstallDate, 11, 2) & ":" & Mid(dtmInstallDate, _
                    13, 2))
End Function

The only thing we changed from our original script were the lines inside the For Each loop:

dtmInstallDate = strOS.InstallDate
strReturn = WMIDateStringToDate(dtmInstallDate)
Wscript.Echo strReturn

Instead of directly echoing back the date that WMI returned in the InstallDate property, we save that date to a variable named dtmInstallDate. We then call our new function, WMIDateStringToDate, passing the function the install date. The function then converts the date to a more readable date and passes it back, at which point we store it in the strReturn variable. We then simply echo back strReturn, and this is what we get:

12/1/2004 9:29:59 AM

Simple, right? Actually if you think about it, it really is - it just looks complicated.

Topic of the Shrimp

If shrimp’s hearts are in their heads, where are their brains? Do shrimp have brains? We need to go do some more research on this. While we do that, you should check out the Windows 2000 Scripting Guide, where you’ll find even more information on working with dates and times than we showed you here.