Intro

This page goes over time and date functions for the following Microsoft products: Access, Excel, SQL Server, VBScript, Visual Basic and C#.

Dates and times are numerically stored as a number where the integer portion represents a day after some some base date while the fractional portion represents a fraction of a day, i.e., a time between midnight and 11:59:59 PM. Different systems have different base dates. EGs:

If possible, the following values will be used in the examples:

Set Date or Time

'In VB changes current system date:
Date = #February 28, 1969#    
Date = 25262
'In VB changes current system time:
Time = #4:35:17 PM#    
Time = 0.691169

Get System Date or Time

Get the current system date.

MyDate = Date()    'In Access 
MyDate = Date      'In VB

Get the current system time.

MyTime = Time()    'In Access 
MyTime = Time()    'In Excel returns serial number 
MyTime = Time      'In VB

Get the current system date and time.

MyNow = Now()         'In Access
MyNow = Now()         'In Excel returns serial number
@MyNow = GETDATE()    --In SQL Server 
MyNow = Now           'In VB

Return a Date or Time

MyDate = DateSerial(1969, 2, 26 + 2)
'Returns an integer equivalent to date. 'Not in Excel
MyDate = DateValue(#February 28, 1969#)
'Returns an integer equivalent to date. 'In both of the above cases CInt(MyDate) yields 25262.
MyTime = TimeSerial(16, 35, 10 + 7)
'Returns a fraction equivalent to time. 'Not in Excel
MyTime = TimeValue(#4:35:17 PM#)
'Returns a fraction equivalent to time. 'In both of the above cases CSng(MyTime) yields 0.691169.

Time a Process

MyTime = Timer
'Returns the number of seconds elapsed since midnight.

Perform Date Calculations

DateAdd(IntervalType, QuantityOfIntervals, Date)
'Returns (Date1 + n*Interval).
DateAdd("h", 1, #3:35:17 PM#)
'Returns #4:35:17 PM#.
DateDiff(IntervalType, Date1, Date2[, FirstDayOfWeek>[, FirstWeekOfYear]])
'Returns Date2 - Date1.
DateDiff("h", #3:35:17 PM#, #4:35:17 PM#)
'Returns 1.
DatePart(IntervalType, Date[, FirstDayOfWeek[, FirstWeekOfYear]])
'Returns specified part of Date1.
DatePart("h", #4:35:17 PM#)
'Returns 16.
DateName(IntervalType, Date)
'Returns text representing part of Date1. This is a SQL Server function.
DateName(m, '02/28/1969')
'Returns February.

The FirstDayOfWeek defaults to 1 = Sunday.
The FirstWeekOfYear defaults to 1 = start with week in which January 1 occurs.

In Excel, the EDate(StartDate,Months) and EOMonth(StartDate,Months) functions are convenient functions that add or subtract a month while taking into account the different lengths of months. Those functions are only available if you add them on via Select Tools > Add-Ins > Analysis ToolPak.

IF(A1=EOMONTH(A1,0),EOMONTH(A1,1),EDATE(A1,1))
'Returns next month

If A1 = 2004-03-31, then it returns 2004-02-29.

The following are the various IntervalTypes:

IntervalType Abbreviation Values
Year yy, yyyy 1753-9999 (depends on system settings) 
Quarter qq, q 1-4
Month mm, m 1-12
Day of Year dy, y 1-366
Day dd, d 1-31
Weekday dw, w 1-7 (Sun. - Sat.)
Week wk, ww 1-53
Hour hh, h 0-23
Minute mi, n 0-59
Second ss, s 0-59
Millisecond ms 0-999

T-SQL uses either the fully spelled IntervalType or the double-letter abbreviations.

Return Integer Values for Parts of a Date

MyDate = #2/28/69 4:35:17 PM#
MyValue = Year(MyDate)    'Returns 1969.
MyValue = Month(MyDate)   'Returns 2.
MyValue = Day(MyDate)     'Returns 28.
MyValue = WeekDay(MyDate) 'Returns 6 since 2/28/69 was a Friday.
MyValue = Hour(MyTime)    'Returns 16.
MyValue = Minute(MyTime)  'Returns 35.
MyValue = Second(MyTime)  'Returns 17.

Formatting Dates, Numbers, and Strings

An expression is formatted by either a valid named format or a user-defined format:

Format(<Expression>[, <Format>[, <FirstDayOfWeek>[, <FirstWeekOfYear>]]])

In VBScript the following is used in place of the above:

FormatDateTime(<Expression>[,<named format>])
'The named format can only be one of these:
' vbGeneralDate (default)
' vbLongDate
' vbShortDate
' vbLongTime
' vbShortTime

There are various system-defined formats:

The following symbols can be used to make custom formats. Note the case sensitivity.

EGs:

MyTime = #2/28/69 4:35:17 PM#
MyStr = Format(MyTime, "H:m:s")            'Returns "16:35:17".
MyStr = Format(MyTime, "hh:mm:ss AMPM")    'Returns "04:35:17 PM".
MyStr = Format(MyTime, "dddd, mmm d yyyy") 'Returns "Wednesday, Feb 12 1969".
'User-defined formats.
MyStr = Format(5459.4, "##,##0.00")        'Returns "5,459.40".
MyStr = Format(334.9, "###0.00")           'Returns "334.90".
MyStr = Format(5, "0.00%")                 'Returns "500.00%".
MyStr = Format("HELLO", "<")               'Returns "hello".
MyStr = Format("This is it", ">")          'Returns "THIS IS IT".
'If format is not supplied, a string is returned. 
MyStr = Format(1200.071)                   'Returns "1200.071".
//C# example. 
xFormatted = String.Format("{$#,0.00; ($#,0.00);Zero}, x)
//The semicolon separates 3 formatting possibilities: positive, negative, and zero.
//If x=1200.071 , then the above returns $1,000.00
//If x=-1200.071, then the above returns ($1,000.00)
//If x=0 , then the above returns Zero

Page Modified: (Hand noted: 2007-09-13 16:43:29Z) (Auto noted: 2007-11-14 17:40:40Z)