SQL Server: Displaying Dates and Times in Different Formats

Following article is a copy paste of the Part 2 article from Gregory A. Larsen

Working with SQL Server Date/Time Variables: Part Four - Date Math and Universal Time - 6/3/03
Working with SQL Server Date/Time Variables: Part Three - Searching for Particular Date Values and Ranges - 5/21/03
Working with SQL Server Date/Time Variables: Part Two - Displaying Dates and Times in Different Formats - 5/07/03
Working with SQL Server Date/Time Variables - 4/16/03

This article discusses date/time data by exploring how to use different SQL Server functions to display dates and times in different formats.

Depending on your environment, your needs, and/or the audience of your application, the format for displaying date and time might vary. Internationally we have many difference ways to represent a given date and/or time. Here are a few examples of different ways we might display the date January 22, 2003 with or without a time of 10:31 PM.

• 2003/01/22 10:31PM
• 2003/01/22 22:31
• 22-01-2003
• 22 January 2003
• Jan 22 2003 10:13PM
• January 22, 2003

Let's review the SQL Server functions that can be used to display these date formats, starting with the CONVERT function. The CONVERT function is provided to help with converting a DATETIME or SMALLDATETIME variables, or any other string that holds a valid date, into different date/time display formats. The CONVERT function is called using the following syntax:

               CONVERT ( data_type [ ( length) ] , expression [ , style ] )  

Where data_type [(length)] is the target data type format and length, expression is any valid Microsoft expression that represents the date/time you want to display, and style specifies the output format for the data/time.

Using the CONVERT function, with different styles, allows you to display date and time data in many different formats. Let's look at what I am taking about. The easiest way to demonstrate how to use the CONVERT function is to review some TSQL code that displays the current time in a few different display formats. The following script uses only the CONVERT function to display the different formats.

PRINT '1) HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>' + 

CONVERT(CHAR(19),GETDATE())  

PRINT '2) HERE IS MM-DD-YY FORMAT ==>' + 

CONVERT(CHAR(8),GETDATE(),10)  

PRINT '3) HERE IS MM-DD-YYYY FORMAT ==>' + 

CONVERT(CHAR(10),GETDATE(),110) 

PRINT '4) HERE IS DD MON YYYY FORMAT ==>' + 

CONVERT(CHAR(11),GETDATE(),106)

PRINT '5) HERE IS DD MON YY FORMAT ==>' + 

CONVERT(CHAR(9),GETDATE(),6) 

PRINT '6) HERE IS DD MON YYYY HH:MM:SS:MMM(24H) FORMAT ==>' + 

CONVERT(CHAR(24),GETDATE(),113)

Here is the output from the above script:

1) HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>Feb  5 2003  5:54AM

2) HERE IS MM-DD-YY FORMAT ==>02-05-03

3) HERE IS MM-DD-YYYY FORMAT ==>02-05-2003

4) HERE IS DD MON YYYY FORMAT ==>05 Feb 2003

5) HERE IS DD MON YY FORMAT ==>05 Feb 03

6) HERE IS DD MON YYYY HH:MM:SS:MMM(24H) FORMAT ==>05 Feb 2003 05:54:39:567

As you can see, this script displays the current date in many different formats. Some formats have two digit years, while others have four digit years. Some displays have 24 hour or AM/PM time formats. Still others have the month displayed as a numeric value, while others have a month abbreviation. Some of the displays also have the date displayed in DD MON YYYY format. I suggest you review Books Online for a description of all the formats that the CONVERT function can display using different styles.

Even though the CONVERT function has a number of data/time output styles, it still might not have the exact display format you need to display. Sometimes you will need to use other TSQL functions as well, to get the display format you desire.

Another important date/time function worth describing is DATEPART. This function will take a date/time expression and return a single part of the date, such as hour, minute, month, day, etc. A call to this function has the following form:

               DATEPART(datepart, date)>

Where the datepart is one of the following: Year, yy, yyyy, quarter, qq, q, month, mm, m, dayofyear, dy, y, day, dd, d, week, wk, ww, weekday, dw,hour, hh, minute, mi, n, second, ss, s, millisecond, or ms. And date is a valid date expression. This function will return an integer representing the particular date part requested.

Let's review how the CONVERT and/or DATEPART functions can be used to display January 22, 2003 with a time of 10:31 PM to meet all of the display formats I showed at the top of this article.

Some of the formats can be created using the different "style" options on the CONVERT statement. Although a number of these formats above will not only require the CONVERT and/or DATEPART functions, but other TSQL functions like SUBSTRING, RIGHT, and CAST to build the desired display format. If you are unfamiliar with these additional functions, read Books Online for more information. To show you how to create each of these display formats I will build a simple script for each of the different formats.

The first example displays the date in 2003/01/22 10:13PM format. This can be done with the following simple script that uses the CONVERT and SUBSTRING functions. Note this example uses the 111 style format of the CONVERT function.

DECLARE @DT DATETIME

SET @DT = '2003-01-22 10:31 PM'

SELECT CONVERT(CHAR(11),@DT,111) + 

SUBSTRING(CONVERT(CHAR(19),@DT,100),13,19)

Normally there is always more than one way to get the same results. Here is another way to get the same date displayed by using the DATEPART, CAST and RIGHT functions:

DECLARE @DT DATETIME

SET @DT = '2003-01-22 10:31 PM'

SELECT CAST(DATEPART(YYYY,@DT) AS CHAR(4)) + '/' 

     + RIGHT(CAST(100+DATEPART(MM,@DT) AS CHAR(3)),2) + '/'

     + RIGHT(CAST(100+DATEPART(DD,@DT) AS CHAR(3)),2) + ' '

     + CASE WHEN DATEPART(HH,@DT) < 13 

            THEN RIGHT(CAST(100+DATEPART(HH,@DT) AS CHAR(3)),2) 

            ELSE CAST(DATEPART(HH,@DT)-12 AS CHAR(2)) 

            END + ':'

     + RIGHT(CAST(100+DATEPART(MI,@DT) AS CHAR(3)),2)     

     + CASE WHEN DATEPART(HH,@DT) < 13

            THEN 'AM'

            ELSE 'PM'

            END

The next example will display the date in 2003/01/22 22:31 format. This example uses the REPLACE function to convert the dashes, of CONVERT style 120, to slashes.

DECLARE @DT DATETIME

SET @DT = '2003-01-22 10:31 PM'

SELECT REPLACE(CONVERT(CHAR(16),@DT,120),'-','/')

The following example will display the date in 22-01-2003 format, without the time portion. To display only the date portion and truncate the time part, I specified an output data type and length. In the example below a CHAR(10) was used for data type and length. This will cause the CONVERT function to display only the first 10 characters of style 105.

DECLARE @DT DATETIME

SET @DT = '2003-01-22 10:31 PM'

SELECT CONVERT(CHAR(10),@DT,105)

The next example displays the date using 22 January 2003 format. To produce this date output, I will introduce a new function, DATENAME. The DATENAME function returns a character string representing the requested part of the date. The DATENAME function uses the following syntax:

               DATENAME( datepart , date )

Where the datepart is one of the following: Year, yy, yyyy, quarter, qq, q, month, mm, m, dayofyear, dy, y, day, dd, d, week, wk, ww, weekday, dw,hour, hh, minute, mi, n, second, ss, s, millisecond, or ms, and date is a valid date expression. This function will return a character string representing the particular date part requested. Be aware that the "weekday" parm returns the day name, like Sunday, Monday, Tuesday, etc, and the "month" parm returns the month name, like January, February, March, etc. This example uses DATENAME to return the DAY, MONTH and YEAR of the variable @DT. The month portion of the date will be returned with the spelled out version of the month, or in this case "January."

DECLARE @DT DATETIME

SET @DT = '2003-01-22 10:31 PM'

SELECT DATENAME(DAY,@DT) + ' ' + DATENAME(MONTH,@DT) + ' ' + DATENAME(YEAR, @DT)

The CONVERT function alone allows me to output a date with Jan 22 2003 10:13PM format. By using a style of 100, the CONVERT function displays the date format needed.

DECLARE @DT DATETIME

SET @DT = '2003-01-22 10:31 PM'

SELECT CONVERT(CHAR(19),@DT,100)

To display a date in January 22, 2003 format I will again use the DATENAME function, I just change the output formatting, by rearranging the calls for each date part.

DECLARE @DT DATETIME

SET @DT = '2003-01-22 10:31 PM'

SELECT DATENAME(MONTH,@DT) + ' ' + DATENAME(DAY,@DT) + ', ' + DATENAME(YEAR, @DT)

Three more SQL Server functions that might help you with formatting date output are worth mentioning. These functions are DAY, MONTH, and YEAR. Each of these functions accepts a date expression, and returns an integer value representing the day, month or year depending on which function is being called. Here is an example of how these functions are used to return a date in 01/22/2003 format.

DECLARE @DT DATETIME

SET @DT = '2003-01-22 10:31 PM'

SELECT '0' + CAST(MONTH(@DT) AS CHAR(1)) + '/' + CAST(DAY(@DT) AS 

CHAR(2)) + '/' + CAST(YEAR(@DT) AS CHAR(4))

Conclusion

There are many different way to display a given date and time value. Because of this, SQL Server provides the CONVERT function to format date/time values into a number of preset output formats. Also by using the CONVERT function along with one or more additional SQL Server functions, such as DATEPART you should be able to display the date any way you need.

My next article in this date/time series will discuss searching SQL Server tables to find records based on a date and time value.

» See All Articles by Columnist Gregory A. Larsen

IE+JScript Performance Recommendations Part 3: JavaScript Code Inefficiencies

this article is from Peter Gurevich, Performance PM for IE. I am copying and pasting this good article in my blog for reference.

We have gotten a lot of good feedback from our first posts on IE + JavaScript Performance Recommendations Part 1 and Part 2, so I am eager to hear what you think of our third installment.

JScript Code Inefficiencies – Chapter 2

Here we’ll be concentrating on specific inefficiencies related to closures and object oriented programming.

Avoid Closures if Possible

Closures (functions that refer to free variables in their lexical context) are both extremely powerful and extremely dangerous. There are certain programming techniques that demand their use and once you learn how to use them they can quickly become one of the most overused features of the JScript language. Currently the misuse of closures in connection with the IE DOM and various COM components are the number one cause of heightened memory usage within IE. Also, while they are useful, this is a performance article and there are not really any scenarios where a closure is going to provide more performance throughput than a bare function or inline code.

Closures are most often used when attaching event handlers. The purpose in this case is to encapsulate some of the scope for the currently running function into a brand new function that can later be used when the event handler is invoked. The trouble with this approach is that circular references between the scoped variables and the closure are nearly invisible to the eye. The extra memory pressure of leaving these objects around means extra garbage collection pressure, possibly extra work for IE, or more bookkeeping for other hosts. APIs for handling the retrieval of remote data are useful as an example.

function startDownload(url)
{
      var source = new ActiveXObject(“Ficitious.UrlFetcher”);
      source.ondataready = new function() { source.ondataready = null; }
      source.startRequest(url);
}

The above example is pretty basic. The script doesn't do any real work, but sets up a mock scenario of a closure. What happens when the ondataready event never fires? We expect it to, but it might not. If it doesn't fire, the closure in this case has a reference to the source object and the source object back to the closure. This is an implicit circular reference, hard to spot, and IE leaks memory. This is never good for performance. Furthermore, every time the startDownload function is called, it means allocating a new object to store all of this state. Rewriting the sample to not use closures is very specific to your application. If you only allow a single instance of the object to exist, then you could take advantage of a global singleton. If you allow multiple requests, then a pooling mechanism is probably more appropriate along with a method for dispatching and handling all incoming requests even if a particular request is signaling it is ready. For additional information on closures, please see the Closures section in Understanding and Solving Internet Explorer Leak Patterns.

Don’t use Property Accessor Functions

A common technique in object oriented programming is to use property accessor functions. An example would be in the form of [get/set]_PropertyName (or many others depending on the style). The basic premise is a local member variable for some class followed by two methods for either retrieving or setting the property. These methods are often used for controlling member visibility, but in JScript everything is visible. Further, most object oriented languages optimize the property methods away to direct variable access during compilation, not so with interpreted JScript. A quick attempt at coding a simple Car object might produce code with property accessors:

function Car()
{
      this.m_tireSize = 17;
      this.m_maxSpeed = 250; // One can always dream!
      this.GetTireSize = Car_get_tireSize;
      this.SetTireSize = Car_put_tireSize;
}
function Car_get_tireSize()
{
      return this.m_tireSize;
}
function Car_put_tireSize(value)
{
      this.m_tireSize = value;
}
var ooCar = new Car();
var iTireSize = ooCar.GetTireSize();
ooCar.SetTireSize(iTireSize + 1); // An upgrade

The above is pretty good object oriented but terrible JScript. The extra indirection in accessing our local members is really hurting the performance of the application. Unless we need to do validation of the incoming values, the code should never add extra indirection and should be as precise as possible. Rewriting the above is an exercise in removing as much extra code as possible.

function Car()
{
      this.m_tireSize = 17;
      this.m_maxSpeed = 250; // One can always dream!
}
var perfCar = new Car();
var iTireSize = perfCar.m_tireSize;
perfCar.m_tireSize = iTireSize + 1; // An upgrade

We’ve removed two extra expando properties on the object, a couple of functions, some extra work while retrieving and setting our properties, and a few name binds. In short, try to stay away from any extra indirection.

For a more complete sample, we can also add prototypes. Note that prototypes will actually be slower, since the instance will be searched first, then the prototype, and so functional look-ups occur more slowly. This will make our naïve sample slower for sure. If you are creating thousands of instances of the class, the prototypes start to become more efficient. They start by reducing the size of each object since extra properties are not added per instance that point to the same global functions. Further, object instantiation can be much faster since the extra property assignments are not needed to set up all of the functions. For completeness, here is a full sample. As an extra challenge, try to find scenarios where the prototype car wins over the slow car.

<script>
// Slow Car definition
function SlowCar()
{
      this.m_tireSize = 17;
      this.m_maxSpeed = 250; // One can always dream!
      this.GetTireSize = SlowCar_get_tireSize;
      this.SetTireSize = SlowCar_put_tireSize;
}
function SlowCar_get_tireSize()
{
      return this.m_tireSize;
}
function SlowCar_put_tireSize(value)
{
      this.m_tireSize = value;
}
</script>

<script>
// Faster Car, no more property accessors
function FasterCar()
{
      this.m_tireSize = 17;
      this.m_maxSpeed = 250; // One can always dream!
}
</script>

<script>
// Prototype Car, use the language features!
function PrototypeCar()
{
      this.m_tireSize = 17;
      this.m_maxSpeed = 250; // One can always dream!
}

PrototypeCar.prototype.GetTireSize = function() { return this.m_tireSize; };
PrototypeCar.prototype.SetTireSize = function(value) { this.m_tireSize = value; };
</script>

<script>
function TestDrive()
{
      var slowCar = new SlowCar(); // Safe and reliable, probably not fast
      var fasterCar = new FasterCar(); // Lacks air-bags, probably faster
      var protoCar = new PrototypeCar(); // Can technology win the day?

      var start = (new Date()).getTime();
      for(var i = 0; i < 100000; i++) { slowCar.SetTireSize(slowCar.GetTireSize() + 1); }
      var end = (new Date()).getTime();
      output.innerHTML += "Slow Car " + (end - start) + "<br>";
     

      start = (new Date()).getTime();
      for(var i = 0; i < 100000; i++) { fasterCar.m_tireSize += 1; }
      end = (new Date()).getTime();
      output.innerHTML += "Faster Car " + (end - start) + "<br>";

      start = (new Date()).getTime();
      for(var i = 0; i < 100000; i++) { protoCar.SetTireSize(protoCar.GetTireSize() + 1); }
      end = (new Date()).getTime();
      output.innerHTML += "Prototype Car " + (end - start) + "<br>";
}
</script>

<button onclick="TestDrive();">Test Drive Cars!</button>
<div id="output"></div>

That’s all for Part 3.

Thanks,

Peter Gurevich
Program Manager

Justin Rogers
Software Development Engineer