Stone Coast Web Design's Weblog

Date and Time Properties

When working with dates and times it is often necessary to know the current date and/or time. These values can be extracted from the server's system clock through the properties shown in the table below. These properties are used in several of the date and time functions described below.

Date Functions

Date functions are summarized in the following table with functions described more fully below.

DateAdd() adds a given date interval to a date to produce a calculated date. Its general format is

DateAdd(interval, number, date)

where interval is one of the string values shown in the accompanying table, number gives the number of intervals to add, and date is the date and time to which the interval is to be added. For example, the following function returns the (formatted) date six months from today.

FormatDateTime(DateAdd("m", 6, Today),
DateFormat.LongDate)

DateDiff() returns a value giving the number of identified intervals between two dates. Its general format is

DateDiff(interval, date1, date2)

where interval is a string value representing an interval type using the date values shown in the table under the DateAdd() function, and date1 and date2 are the two dates between which the interval is calculated (date1 is subtracted from date2). The following example calculates the number of shopping days until Christmas.

DateDiff( "d", Today, "12/24/" & DatePart("yyyy", Today) )

Notice that the date2 argument passes "12/24" & DatePart("yyyy", Today). The DatePart() function (see below) extracts the current year from the current date and appends it to the string so that the calculation is always based on the current year.

DatePart() extracts a specified component of a given Date. Its general format is

DatePart(part, date)

where part is a string value representing a date component using the values shown in the table under the DateAdd() function, and date is any Date value. For example, the following function determines the day of the week on January 1, 2010.

WeekdayName(DatePart("w", "01/01/2010"))

DatePart() returns an integer value (Sunday = 1) representing the day of the week. This value is converted into a weekday name with the WeekDayName() function (see below).

DateSerial() returns a Date value based upon integer values representing a year, month, and day. Its general format is

DateSerial(year, month, day)

For example, the function DateSerial(5, 7, 15) returns 7/15/2005 12:00:00 AM. You might wonder why the need to return a date if you already know the date! The point is that the passed values are integers, perhaps collected from a form in which a year, month, and day are selected from drop-down lists. These integer values are combined and converted into a Date type with the DateSerial() function.

DateValue() returns a Date type from a String value representing a date. This function works as a converter to a Date data type. For example, the function call DateValue("January 1, 2005") returns 1/1/2005 12:00:00 AM.

Day() returns an Integer representing the day of the month from a passed date. For instance, function Day(Today) returns 15. This is the same value as returned from the function DatePart("d", Today).

IsDate() returns a Boolean value indicating whether the passed value can be converted into a Date type. This function is handy when testing user input to verify that an actual date has been entered. A function call in the format IsDate("02/31/2005") returns False.

Month() returns an Integer value from 1 through 12 representing the month of the year. It works similar to the Day() function. The function call Month(Today) returns 6.

MonthName() accepts an Integer representing a month of the year and returns a String value containing the name of the month. Used in conjunction with the Month() function, the function call MonthName(Month(Today)) produces June.

WeekDay() returns an Integer value between 1 and 7 (Sunday = 1) representing the day of the week. The function WeekDay(Today) returns 6, which is identical to the value returned by DatePart("w", Today).

WeekDayName() accepts an Integer representing a day of the week and returns a String value containing the name of the day of the week. Used in conjunction with the Weekday() function, the function call WeekDayName(WeekDay(Today)) produces Friday.

Time Functions

Time functions are summarized in the following table with functions described more fully below. Several of the date functions can be applied to time measurements.

DateAdd() adds a given time interval to a time to produce a calculated time. Its general format is

DateAdd(interval, number, time)

where interval is one of the string values shown in the accompanying table, number gives the number of intervals to add, and time is the date and time to which the interval is to be added. For example, the following function returns the (formatted) time twelve hours from now.

FormatDateTime(DateAdd("h", 12, Now), DateFormat.LongDate)

DateDiff() can be used to return a value giving the number of identified intervals between two times. Its general format is

DateDiff(interval, time1, time2)

where interval is a string value representing an interval type using the time values shown in the table under the DateAdd() function, and time1 and time2 are the two times between which the interval is calculated (time1 is subtracted from time2). The following example calculates the number of minutes until midnight.

DateDiff("n", TimeString, "11:59:59 PM") + 1

It can be a bit tricky working with times because of the roll-over that takes place at midnight. If the value "00:00:00 AM" were to be used in the above example, it would produce -933 minutes, which are the number of minutes from the beginning of the current day. To get around this problem, one minute is added to the time difference until "11:59:59 PM" of the current day.

DatePart() can be used to extract a specified component of a given time. Its general format is

DatePart(part, time)

where part is a string value representing a time component using the time values shown in the table under the DateAdd() function, and time is any Date value. For example, the following function determines the current hour (24-hour clock) of the current day.

DatePart("h", Now)

Hour() returns an Integer representing the hour of the day (24-hour clock). For instance, function Hour(Now) returns 15. This is the same value as returned from the function DatePart("h", Now).

Minute() returns an Integer representing the minute of the hour. For instance, function Minute(Now) returns 34. This is the same value as returned from the function DatePart("n", Now).

Second() returns an Integer representing the second of the minute. For instance, function Second(Now) returns 34. This is the same value as returned from the function DatePart("s", Now).

TimeSerial() returns a Date value representing a specified hour, minute, and second, with the date information set relative to January 1 of the year 1. Its general format is

TimeSerial(hour, minute, second)

For example, the function TimeSerial(5, 30, 45) returns 1/1/0001 5:30:45 AM. As in the case for the DateSerial() function, integer values are combined and converted into a Date type.

TimeValue() returns a Date type from a String value representing a time. This function works as a converter to a Date data type. For example, the function call TimeValue("4:35:17 PM") returns 1/1/0001 4:35:17 PM.

Some of the date and time functions appear trivial in isolation. However, you will find them crucial when you begin combining them in applications that relay on accurate reporting of and calculations involving dates and times.

Mathematical Functions

Popular mathematical functions are summarized in the following table. Note that certain functions do not require the Math. prefix.

Random Numbers

The Rnd() function returns a random number between 0 and 1. More likely, the need is to generate a number within a particular range, between a given low and high number. This is accomplished with the following formula.

For instance, to generate a random number between 0 and 10 the formula becomes

Math.floor((10 - 0 + 1) * Rnd() + 0)

I find I use these all of the time

String Functions

Several built-in string functions perform string manipulations to augment simple concatenation with the "&" operator. These functions are summarized in the following table.

I've done this a few times at work and I always seem to forgot how I did it previously.  In this case, I needed to remove all of the text from a SQL statement from the ORDER BY clause forward.

So a typical SQL statement like this:

SELECT * FROM tableA ORDER BY ID

needed to look like this:

SELECT * FROM tableA

strSqlText = "SELECT * FROM tableA ORDER BY ID"

'This returns the ordinal position (first character of ORDER BY
intOrderByCount = InStr(strSqlText, "ORDER BY")

'This removes the O in ORDER BY
intOrderByCount = intOrderByCount - 1

'This takes only the Left part of the SQL statement and removes everything after intOrderByCount
strSqlText = Left(strSqlText, intOrderByCount)

I'm sure there are better ways of doing this but this way worked for me

A coworker asked me how to do this so I thought I would add it here

mylabel.Text = myDataSet.Tables[0].Rows[0].item["column"];

C#

mylabel.Text = myDataSet.Tables[0].Rows[0]["column"].ToString();

I had a problem recently where my CSS in my pages was out of whack and couldn't figure out why.  It turned out that my document type declaration was in the wrong place and was causing the problem.  This document type declaration is the VS 2005 default for new .aspx pages.

I was doing this:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>

<script runat="server">

.....

</script>

and structuring it this way corrected the problem

I really like this example because it illustrates just how powerful client-side and server-side technology used in conjunction can be.  My javascript function, saveSelectedtoDatabase, passes a few parameters to my .aspx page saveSelected.aspx.  The string passed is in the form saveSelected.aspx?UserID=2447&BenchmarkName=Benchmark1&CompanyName=IBM*Ford Motor*AMD&CompID=14575*17411*41522

saveSelected.aspx takes the querystring value passed to it and splits the CompanyName and CompID values by * and then saves each record to a SQL Server database.

<script runat="server">

Sub Page_Load()
If Not IsPostBack Then

Dim intUserID As Integer
Dim strBenchmarkName As String
Dim strCompID As String
Dim strCompanyName As String

'These values are coming from the AJAX function savedSelectedtoDatabase()
intUserID = Request.QueryString("UserID")
strBenchmarkName = Request.QueryString("BenchmarkName")
strCompID = Request.QueryString("CompID")
strCompanyName = Request.QueryString("CompanyName")

'Remove trailing * characters
strCompID = strCompID.Substring(0, strCompID.Length - 1)
strCompanyName = strCompanyName.Substring(0, strCompanyName.Length - 1)

'Place these values into an array
Dim arrCompID As String() = Nothing
arrCompID = strCompID.Split("*")

Dim arrCompanyName As String() = Nothing
arrCompanyName = strCompanyName.Split("*")

'Get the length of one element of the array so we now how many times
'to loop through the database with updates/inserts
Dim i As Integer
Dim myArrayLength As Integer = (arrCompID.Length - 1)

'Insert new record into TBenchmarker and output parameter as BenchmarkID
'TBenchmarker requires UserID and BenchmarkName

Dim strSQLConn As SqlConnection
Dim cmd As SqlCommand
Dim SqlText As String

strSQLConn = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            
Try    
strSQLConn.Open()
'Insert new record into TBenchmarker
SqlText = "benchmarkerAddRecord"
cmd = New SqlCommand(SqlText, strSQLConn)
cmd.CommandType = CommandType.StoredProcedure

'input parameters for the sproc
cmd.Parameters.AddWithValue("@UserID", intUserID)
cmd.Parameters.AddWithValue("@BenchmarkName", strBenchmarkName)

'Output parameter from sproc benchmarkerAddRecord which is SCOPE_IDENTITY
'Create a SqlParameter object to hold the output parameter value
Dim intNewBenchmarkID As New SqlParameter("@RETURN_VALUE", SqlDbType.Int)

'Set Direction as Output
intNewBenchmarkID.Direction = ParameterDirection.ReturnValue

'Finally, add the parameter to the Command's Parameters collection
cmd.Parameters.Add(intNewBenchmarkID)

'Execute the sproc
cmd.ExecuteScalar()

'Now you can grab the output parameter's value...
Dim intNewBenchmarkIDValue As Integer = Convert.ToInt32(intNewBenchmarkID.Value)

'Insert new record(s) into TBenchmarkerSaved
For i = 0 To myArrayLength
SqlText = "saveBenchmarkValues"
cmd = New SqlCommand(SqlText, strSQLConn)
cmd.CommandType = CommandType.StoredProcedure

'input parameters for the sproc
'intNewBenchmarkIDValue is the value returned from the sproc benchmarkerAddRecord
cmd.Parameters.AddWithValue("@BenchmarkID", intNewBenchmarkIDValue)
cmd.Parameters.AddWithValue("@CompID", arrCompID(i))
cmd.Parameters.AddWithValue("@CompanyName", arrCompanyName(i))
cmd.ExecuteNonQuery()
Next i
            
strSQLConn.Close()

'This value gets returned back to savedSelectedtoDatabase and added
'as an option to ddlSavedBenchmarks dropdown
Response.Write(strBenchmarkName & "#" & intNewBenchmarkIDValue)

Catch ex As Exception
Response.Write("There was an error when saving these companies. Please try again.")
Finally
        
End Try

End If        
End Sub

</script>

I've been working on a project which inolved a lot of javascript and one requirement was to grab a list of industry values and their primary keys, place them into an array or string and then return those back to my javascript function.   In this first example I use the StringBuilder() method which I guess from user feedback is much more efficient than creating a ArrayList object.

<script runat="server">
Sub Page_Load()
Dim strSQLConn As SqlConnection
Dim strSqlText As String
Dim cmd As SqlCommand
'Dim myList As New ArrayList
Dim dr As SqlDataReader
Dim count As Integer
Dim mySB As New StringBuilder()

strSQLConn = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
strSqlText = "getIndustries"
cmd = New SqlCommand(strSqlText, strSQLConn)
cmd.CommandType = CommandType.StoredProcedure

Try
strSQLConn.Open()
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

With dr
If dr.HasRows Then
While dr.Read
mySB.Append("{")
mySB.Append(dr.GetInt32(0))
mySB.Append("#")
mySB.Append(dr.GetString(1))
mySB.Append("}")
count += 1
End While
End If
End With

Finally
dr.Close()
strSQLConn.Close()
End Try

Dim strResults As String
strResults = mySB.ToString().TrimEnd(",".ToCharArray())
Response.Write(strResults)
End Sub

In this second example, I do the identical thing except that I use an ArrayList

Tim Mackey's Weblog entry explains this nicely

http://tim.mackey.ie/HowtoUseMySqlWithNet.aspx

SELECT <KEY_COLUMN>, COUNT(*)
FROM <TABLE>
GROUP BY <KEY_COLUMN>
HAVING COUNT(*) > 1

I use this on signup forms to prevent a user from registering a second account with the same email address.

CREATE Procedure sp_AddSubscriber
@Name as nvarchar(50),
@Email as nvarchar(50),
@AddSubscriber varchar(255) OUTPUT

AS
 IF (SELECT COUNT(Email)
                  FROM TSubscribers
                  WHERE Email = @Email) = 0
  
  BEGIN INSERT TSubscribers
  (Name, Email)  Values  (@Name, @Email)
SET @AddSubscriber = "True"                           
END              
ELSE
     SET @AddSubscriber = "The email address you have entered has already been registered in our database."
GO

I've been working on a project that's involved a lot of javascript and AJAX development.  This basic idea was to call a .aspx page which queried a database, placed the results in an array, returned the array to the client and then split out the array and populated the select box.  This is how I did it.

First create the XMLHTTPObject

function getIndustries()
{
xmlHttp=GetXmlHttpObject()
if (xmlHttp==null)
{
alert ("Your browser does not support the XMLHttpRequest object.")
return
}

var url="getIndustries.aspx"
xmlHttp.onreadystatechange=stateChanged
xmlHttp.open("GET",url,true)
xmlHttp.send(null)
}

This displays a 'Please Wait message and icon to the user while the transaction is occuring.

function stateChanged()
{ 
  if (xmlHttp.readyState == 0)
  {
document.getElementById("lblResults").innerHTML = "<img src='mozilla_blu.gif' alt='loading..please wait'> Please wait"; //loading
  }
  else if(xmlHttp.readyState == 1)
  {
document.getElementById("lblResults").innerHTML = "<img src='mozilla_blu.gif' alt='loading..please wait'> Please wait"; //loaded
  }
  else if(xmlHttp.readyState == 2)
  {
document.getElementById("lblResults").innerHTML = "<img src='mozilla_blu.gif' alt='loading..please wait'> Please wait"; //interactive
  }
  else if(xmlHttp.readyState == 3)
  {
document.getElementById("lblResults").innerHTML = "<img src='mozilla_blu.gif' alt='loading..please wait'> Please wait";
  }
  else if (xmlHttp.readyState==4 || xmlHttp.readyState=="complete")
  {
document.getElementById("lblResults").innerHTML = "";  
 
  // Clear the available listbox of any previous options
  document.choiceForm.available.length = 0; 

// Split the delimited response into an array  
  var s = xmlHttp.responseText;
  var re = new RegExp('{([^{}]*)}','g');
  var x = [];
  while (re.exec(s)){ 
   x.push(RegExp.$1.split('#'));
  }
 
 // Add the array options to the selectbox available
  var sel = document.forms['choiceForm'].elements['available'];
 for (var i=0, len=x.length; i<len; i++){
    sel.options[i] = new Option(x[i][1], x[i][0]);
    }
 
  }
}

function GetXmlHttpObject()
{
  var objXMLHttp=null
 
  try {
    objXMLHttp = new ActiveXObject("Msxml2.XMLHTTP"); //later IE
  } catch (e) {
  try {
    objXMLHttp = new ActiveXObject("Microsoft.XMLHTTP"); //earlier IE
  } catch (e) {
  objXMLHttp = null;
  }
  }
 
  if (objXMLHttp==null)
  {
    objXMLHttp=new XMLHttpRequest() //IE7, Firefox, Safari
  }
  return objXMLHttp
}

getIndustries.aspx

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>

<script runat="server">
    Sub Page_Load()
        Dim strSQLConn As SqlConnection
        Dim strSqlText As String
        Dim cmd As SqlCommand
        Dim myList As New ArrayList
        Dim dr As SqlDataReader
        Dim count As Integer
        Dim mySB As New StringBuilder()
               
        strSQLConn = New SqlConnection(ConfigurationSettings.AppSettings("CorpLibConnectionStringCurr"))
        strSqlText = "getIndustries"
        cmd = New SqlCommand(strSqlText, strSQLConn)
        cmd.CommandType = CommandType.StoredProcedure
       
        Try
            strSQLConn.Open()
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)                       
            
                        
            With dr
                If dr.HasRows Then
                    While dr.Read
                        mySB.Append("{")
                        mySB.Append(dr.GetInt32(0))
                        mySB.Append("#")
                        mySB.Append(dr.GetString(1))
                        mySB.Append("}")
                        count += 1
                    End While
                End If
            End With
           
        Finally
            dr.Close()
            strSQLConn.Close()
        End Try

    Dim strResults As String
    strResults = mySB.ToString().TrimEnd(",".ToCharArray())   
    Response.Write(strResults)
    End Sub

</script>

I was recently working on a project where I had to return the larger of two values in my T-SQL code.  COALESCE would not work because often one of the two values was not NULL which COALESCE requires.  The solution ended up being relatively simple by using a CASE-WHEN statement like below.

CASE
  WHEN CEOAnnualBonus > CEOBonus THEN CEOAnnualBonus
  WHEN CEOBonus IS NULL THEN CEOAnnualBonus
  ELSE CEOBonus
END 
AS CEOBonusCombined,
CASE
  WHEN CEOOtherAnnualComp > CEOAllOtherCompensation THEN CEOOtherAnnualComp
  WHEN CEOAllOtherCompensation IS NULL THEN CEOOtherAnnualComp
  ELSE
  CEOAllOtherCompensation
  END 
AS CEOAnnualCompCombined 

Also, I learned about using ISNULL to return a different value when you encounter a NULL value in your data.  This helps clean it up at run time.

ISNULL(dbo.TBenchmarkTemp.ChairIndependent, '-') AS ChairIndependent

I'll be using my Weblog mostly for personal use as a code library. Thanks for looking around.