Formatting Date Output in SQL

Formatting dates for output is quite easy in SQL using the CONVERT method.  The hardest part is remembering the code to use so you get the format you require. Below is an example of each format available (taken from MSSQL)

SELECT GETDATE() -- 2012-05-11 12:15:17.813
SELECT CONVERT(VARCHAR(30), GETDATE(), 100) -- May 11 2012 12:15PM
SELECT CONVERT(VARCHAR(30), GETDATE(), 101) -- 05/11/2012
SELECT CONVERT(VARCHAR(30), GETDATE(), 102) -- 2012.05.11
SELECT CONVERT(VARCHAR(30), GETDATE(), 103) -- 11/05/2012
SELECT CONVERT(VARCHAR(30), GETDATE(), 104) -- 11.05.2012
SELECT CONVERT(VARCHAR(30), GETDATE(), 105) -- 11-05-2012
SELECT CONVERT(VARCHAR(30), GETDATE(), 106) -- 11 May 2012
SELECT CONVERT(VARCHAR(30), GETDATE(), 107) -- May 11, 2012
SELECT CONVERT(VARCHAR(30), GETDATE(), 108) -- 12:15:17
SELECT CONVERT(VARCHAR(30), GETDATE(), 109) -- May 11 2012 12:15:17:813PM
SELECT CONVERT(VARCHAR(30), GETDATE(), 110) -- 05-11-2012
SELECT CONVERT(VARCHAR(30), GETDATE(), 111) -- 2012/05/11
SELECT CONVERT(VARCHAR(30), GETDATE(), 112) -- 20120511
SELECT CONVERT(VARCHAR(30), GETDATE(), 113) -- 11 May 2012 12:15:17:813
SELECT CONVERT(VARCHAR(30), GETDATE(), 114) -- 12:15:17:813

To change the year value from 4 (yyyy) digits to 2 digits (yy) deduct 100 from the style identifier.

SELECT CONVERT(VARCHAR(30), GETDATE(), 1) -- 05/14/12

This does not change all formats as some dates would be confusing to read if the year part was just the 2 digits so it defaults to 4 digits year.

SELECT CONVERT(VARCHAR(30), GETDATE(), 0) -- May 14 2012 12:25PM
SELECT CONVERT(VARCHAR(30), GETDATE(), 9) -- May 14 2012 12:25:20:993PM
SELECT CONVERT(VARCHAR(30), GETDATE(), 13) -- 14 May 2012 12:25:20:993

It is also worth noting that normal UNION rules apply even though a style has be specified. If you union the previous statements you will now get the following.

SELECT CONVERT(VARCHAR(30), GETDATE(), 100) -- 2012-05-11 12:17:00.000
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 101) -- 2012-05-11 00:00:00.000
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 102) -- 2012-05-11 00:00:00.000
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 103) -- 2012-11-05 00:00:00.000
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 104) -- 2012-11-05 00:00:00.000
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 105) -- 2012-11-05 00:00:00.000
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 106) -- 2012-05-11 00:00:00.000
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 107) -- 2012-05-11 00:00:00.000
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 108) -- 1900-01-01 12:17:23.000
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 109) -- 2012-05-11 12:17:23.200
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 110) -- 2012-05-11 00:00:00.000
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 111) -- 2012-05-11 00:00:00.000
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 112) -- 2012-05-11 00:00:00.000
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 113) -- 2012-05-11 12:17:23.200
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 114) -- 1900-01-01 12:17:23.200
UNION SELECT GETDATE() -- 2012-05-11 12:17:23.2

The last thing to note is there may be localised settings that which influence how dates are formatted.

C# Arrays

Firstly some points to note about arrays in C#:

  • C# arrays are zero indexed, meaning 0 is the first index.
  • Array elements are referenced using square brackets [].
  • In declarations the square brackets ([]) come after the type, not the identifier. e.g. string[] cites; and not string cities[];
  • An array can hold other arrays or objects as well as common data objects.
  • Initialisation can be done at the same time as declaration.

Below is a simple console application that demonstrates Single Dimension arrays, Multiple Dimension arrays and Arrays of arrays, also known as Ragged arrays or Jagged Arrays.

using System;

namespace ArraysSample
{
    class Program
    {
        static void Main(string[] args)
        {
            // single dimension
            int[] ages = new int[5];
            ages[0] = 3;
            ages[1] = 6;
            ages[2] = 20;
            ages[3] = 34;
            ages[4] = 67;

            Console.WriteLine("Single Dimensional Array");
            for (int ageLoop = 0; ageLoop < ages.Length; ageLoop++)
            {
                Console.WriteLine(string.Format("Item {0}: {1}", ageLoop, ages[ageLoop]));
            }

            Console.Write("\n\nMulti Dimensional Array\n");

            // multi dimensional
            string[,] names = new string[2, 2];
            names[0, 0] = "Andrew";
            names[0, 1] = "Bobby";
            names[1, 0] = "Susan";
            names[1, 1] = "Peter";

            Console.WriteLine("Row\tCol\tValue");
            for (int row = 0; row < names.GetLength(0); row++)
            {
                for (int col = 0; col < names.GetLength(row); col++)
                {
                    Console.WriteLine(String.Format("{0}\t{1}\t{2}", row, col, names[row, col]));
                }
            }

            Console.Write("\n\nArray of Arrays\n");

            // Array of arrays
            string[][] days = new string[2][];

            days[0] = new string[2];
            days[0][0] = "Monday";
            days[0][1] = "Wednesday";

            days[1] = new string[4];
            days[1][0] = "Tuesday";
            days[1][1] = "Friday";
            days[1][2] = "Saturday";
            days[1][3] = "Sunday";

            Console.WriteLine("Row\tCol\tValue");
            for (int row = 0; row < days.GetLength(0); row++)
            {
                for (int col = 0; col < days[row].GetLength(0); col++)
                {
                    Console.WriteLine(String.Format("{0}\t{1}\t{2}", row, col, days[row][col]));
                }
            }

            if (System.Diagnostics.Debugger.IsAttached)
            {
                System.Diagnostics.Debugger.Break();
            }
        }
    }
}

Output from the above application when run is:

Single Dimensional Array
Item 0: 3
Item 1: 6
Item 2: 20
Item 3: 34
Item 4: 67
Multi Dimensional Array
Row     Col     Value
0       0       Andrew
0       1       Bobby
1       0       Susan
1       1       Peter
Array of Arrays
Row     Col     Value
0       0       Monday
0       1       Wednesday
1       0       Tuesday
1       1       Friday
1       2       Saturday
1       3       Sunday

In the above exmaples the array elements have been set long hand, an element at a time.  It is possible to initialise an array at the same time as the declaration.  Obviously we would only do this if the values were fixed and we knew them from the start.

// single dimension array
int[] ages = new int[4] = {1, 2, 3, 4};
string[] names = new string[4] = {"Andrew", "Bobby", "Susan", "Peter"};

// multi dimension array
int[,] numbers = new int{1, 9] { {2, 8}, {3, 7}, {4, 6}, {5, 5} };
string[,] siblings = new string[2, 2] { {"Mike","Amy"}, {"Mary","Albert"} };

// array of Arrays
int[][] days = new int[2][] { new int[] {1, 2, 3}, new int[] {98, 99, 100} };

Instead of using a for loop to iterate an arry a foreach loop can be used.  This is not as useful on multi dimension arrays because the whole array is treated as one single list.

// single dimension array
foreach (int age in ages)
{
    Console.WriteLine(string.Format("Item {0}", age));
}

// multi dimension array
foreach (string name in names)
{
    Console.WriteLine(name);
}

// array of arrays
foreach (string[] d in days)
{
    foreach (string day in d)
    {
        Console.WriteLine(day);
    }
}

You will notice in the top example the single dimensional array example uses the Length property to get the length of the array.  In the other examples the GetLength method has been used.  The Length property will give the total number of elements in an array  which is 5 in the ages array and 4 in the names array.  The GetLength method accepts an array index (integer) and will return the length of the particular element which in the days array is 2 for the first element and 4 for the second.

ages.Length //5 - Full length of array
ages.GetLength(0) //5 - single dimension so full length of array
ages.GetUpperBound(0) //4 - Index range is 0 to 4
names.Length // 4 - Full length of array
names.GetLength(0) // 2 - Length of elements in first dimension
names.GetUpperBound(0)// 1 - first dimensions index range is 0 to 1
days.Length // 2 - Length of main array	2
days.GetLength(0) // 2 - Length of array in first element
days.GetUpperBound(0) // 1 - Array index range is 0 to 1
days[0].GetUpperBound(0) // 1 - First element array index range is 0 to 1
days[1].GetUpperBound(0) // 3 - Second element array index range is 0 to 3
days[1].Length // 4 - Length of array in First element
days[1].GetUpperBound(0) // 3 - Second element array index range is 0 to 3

An example console application is available from Github here

Converting an image to byte array and vice versa

If you want to store images in a database you will need to convert the image into a byte array. Here are a couple of methods to assist in converting an image to and from a byte array.

The first uses the System.Drawing.Image.Save method to save the image to a memorystream. The memorystream can then be used to return a byte array using the ToArray() method in the MemoryStream class.

public byte[] imageToByteArray(System.Drawing.Image imageIn)
{
    MemoryStream ms = new MemoryStream();
    imageIn.Save(ms,System.Drawing.Imaging.ImageFormat.Gif);
    return ms.ToArray();
}

This next method uses the Image.FromStream method in the Image class to create a method from a memorystream which has been created using a byte array. The image created is returned in this method.

public Image byteArrayToImage(byte[] byteArrayIn)
{
    MemoryStream ms = new MemoryStream(byteArrayIn);
    Image returnImage = Image.FromStream(ms);
    return returnImage;
}

LUA Tables as HashTables and a little bit more

Tables can be used as Hash Tables where each element is associated by a name.

local dimens = {
    height = 10,
    width = 5
}

dimens.depth = 2

You will notice above an additional element is added without having to resize the table or pre-define the element.

print( dimens.depth ) -- 2
print( dimens["depth"] ) -- 2

Advanced Collections
As well as numbers and string a table can hold functions, references to functions as well as other tables.

local products = {
    {code = "Prod1", description = "bulb" , price = 32.50, quantity = 22},
    {code = "Prod2", description = "door" , price = 10.02, quantity = 2}
}

print (products[2]["code"])  -- Prod2
print (products[2].description)   -- door
print (products[2].price)  -- 10.02
print (products[2]["quantity"])  -- 2

Tables can be mixed (Hash Table and Array)
A table can hold array and hash table structures.

local mixed = {
    "alpha",
    "beta",
    ["one"] = "uno",
    ["two"] = "dos"
}

Iterating elements

for key, value in pairs[mixed] do
    print(key, value)
end

The output from the above is:

1, alpha
2, beta
one, uno
two, dos

Tables can contain function references

local function helloWorld()
    print( "Hello World!" )
end

local myTable = {
    name = "Bob",
    func = helloWorld}

myTable.func() -- output: Hello World!

----------

local function helloWorld()
    print( "Hello World!" )
end

local myTable = { 100, 100, helloWorld, true }

myTable[3]()    -- output: Hello World!

Tables can contain actual functions

local myTable = {
    100,
    100,
    function() print( "Hello World!" ); end,
    true
}

myTable[3]    -- output: Hello World!

LUA Tables as Arrays

First of all some points about LUA tables:

  • You do not need to declare their size.
  • They can be used like arrays.
  • They also fill the role of dictionaries (associative arrays)
  • You can have mixed data types in tables, similar to structures in other languages.

Tables can be used like arrays

local shapes1 = {"circle", "square", "triangle", "hexagon" }

local shapes2 = {}
shapes2[1] = "circle"
shapes2[2] = "square"
shapes2[3] = "triangle"
shapes2[4] = "hexagon"

local shapes3 = {
[1] = "circle",
[2] = "square",
[3] = "triangle",
[4] = "hexagon"
}

Elements in each of the above table declarations can be accessed by numerical index just like any other array. The following will all have the same output.

print(shapes1[2] )
print(shapes2[2] ) -- square
print(shapes3[2] ) -- square

Built in functions

In LUA there is a namespace which contains functionality for adding elements, removing elements and sorting an array.

Adding Elements

table.insert(table_name, [pos, ] value)

The position parameter is optional, if omitted the element is added at the end as the last element.

Removing Elements

value = table.remove(table_name [, pos] )

The position parameter is optional, if ommitted the last element in the array will be removed.
The remove function returns removed element.

Sorting Elements

table.sort(table_name [, comp])

By default an alpha numeric sort is performed on the element value.

Getting the length of the array
To get the length of the array simply prepend # to the name of the array

#shapes1

Iterating Arrays
Iterating an array can be done with a simple for loop. The structure of a for..loop is

for variablename = start_value, end_value, step_value do
...
end

You can also use the iparis function which returns an iterator function to iterate the array.

for loopCounter = 1, #shapes1 do
  print(shapes1[loopCounter])
end

for index, value in ipairs(shapes1) do
  print(index, value)
end

output of the above is

circle
square
triangle
hexagon

1 circle
2 square
3 triangle
4 hexagon