SQL Server DateDiff Function

–Date Difference Function
–Number of days between two dates
select datediff(day,getdate(),’09/01/2020′)

–Number of months between two dates
select datediff(month,getdate(),’09/01/2020′)

–Number of years between two dates
select datediff(year,getdate(),’09/01/2020′)


Tagged with: , , , , ,
Posted in SQL Server

SQL Server Date Formatting and Date Examples

–Get Current Date and Time
select getdate()

–Date Format MMM DD YYYY
select convert(nvarchar(12),getdate(),100)

–Date Format MM/DD/YYYY
select convert(nvarchar(10),getdate(),101)

–Date Format YYYY.MM.DD
select convert(nvarchar(10),getdate(),102)

–Date Format DD/MM/YYYY
select convert(nvarchar(10),getdate(),103)

–Date Format DD.MM.YYYY
select convert(nvarchar(10),getdate(),104)

–Date Format DD-MM-YYYY
select convert(nvarchar(10),getdate(),105)

–Date Format DD MMM YYYY
select convert(nvarchar(11),getdate(),106)

–Date Format MMM DD, YYYY
select convert(nvarchar(12),getdate(),107)

–Date Format hh:mm:ss (24hr)
select convert(nvarchar(10),getdate(),108)

–Date Format hh:mm:ss:mmm (AM PM)
select convert(nvarchar(30),getdate(),109)

–Date Format MM-DD-YYYY (AM PM)
select convert(nvarchar(10),getdate(),110)

–Date Format YYYY/MM/DD
select convert(nvarchar(10),getdate(),111)

–Date Format YYYYMMDD
select convert(nvarchar(10),getdate(),112)

–Date Format DD MMM YYYY hh:mm:ss:mmm(24h)
select convert(nvarchar(24),getdate(),113)

–Date Format hh:mm:ss:mmm(24h)
select convert(nvarchar(12),getdate(),114)

–Date Format hh:mm:ss:mmm(24h)
select convert(nvarchar(19),getdate(),120)

–Date Format YYYY-MM-DD hh:mm:ss:mmm(24h)
select convert(nvarchar(23),getdate(),121)

–Date Format YYYY-MM-DDThh:mm:ss:mmm(24h)
select convert(nvarchar(24),getdate(),126)

–Time Format hh:mm PM
select substring(convert(varchar(20), GetDate(), 109), 13, 5) + ‘ ‘ + substring(convert(varchar(30), GetDate(), 109), 25, 2)

–Time Format hh:mm:ss PM
select substring(convert(varchar(20), GetDate(), 109), 13, 8) + ‘ ‘ + substring(convert(varchar(30), GetDate(), 109), 25, 2)

–Date Format YYYY.MM.DD
select convert(nvarchar(10),getdate(),102)

–Date Get Month
select month(getdate())

–Date Get Numerical Day of Month
select day(getdate())

–Date Get Year
select year(getdate())

–Date Get Last Day of Current Month
select convert(nvarchar(10),dateadd(day,-1,convert(nvarchar(2),month(dateadd(month,1,getdate()))) + ‘/1/’ + convert(nvarchar(4),year(dateadd(month,1,getdate())))),101)

–Date Get First Day of Current Month
select convert(nvarchar(10),cast(convert(varchar(2),month(getdate())) + ‘/01/’ + convert(varchar(4),year(getdate())) as datetime),101)

–Date Get Last Day of Current Year
select ’12/31/’ + convert(nvarchar(4),year(dateadd(month,1,getdate())))

–Date Get First Day of Current Year
select ’01/01/’ + convert(varchar(4),year(getdate()))

Tagged with: , , , , , ,
Posted in SQL Server

Visual Studio: Display A File List In A DataGridView Control

This example also shows how to extract an icon image from a file in a windows directory and display the icon in a DataGridView Control.  One advantage of displaying a list of files in the DataGridView Control, is that you can take advantage of the built in sort function and the automatic ability to Select All and Copy built into the DataGridView Control.  The functionality can be accessed by clicking ‘Ctl + A’
and ‘Ctl + C’.  After you have copied the list, you can paste into Excel or Notepad.

First Create a new Visual Studio C# Windows Application Solution.
Drag and Drop a DataGridView Control onto the form.
Right Click on the DataGridView Control and Click Properties.
Select ‘Docking’ in the Properties and select the middle square.
Click on the arrow on the DataGridView Control and Uncheck:

Enable Adding
Enable Deleting
Enable Editing

Display A Directory List In A DataGridView Control

Copy and paste the code below into the load event of the form:

            dataGridView1.AllowUserToAddRows = false;
            dataGridView1.RowHeadersVisible = false;

            DataGridViewImageColumn colIcon = new DataGridViewImageColumn();
            colIcon.Name = "colIcon";
            colIcon.HeaderText = "";
            colIcon.ImageLayout = DataGridViewImageCellLayout.Zoom;
            colIcon.DefaultCellStyle.SelectionBackColor = Color.White;
            colIcon.Width = 18;
            //colIcon.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            dataGridView1.Columns.Add(colIcon);

            DataGridViewColumn colHold = new DataGridViewTextBoxColumn();
            colHold.Name = "colFileName";
            colHold.HeaderText = "Name";
            colHold.DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopLeft;
            colHold.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            dataGridView1.Columns.Add(colHold);

            colHold = new DataGridViewTextBoxColumn();

            colHold.Name = "colFileSize";
            colHold.HeaderText = "Size";
            colHold.DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopRight;
            colHold.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            dataGridView1.Columns.Add(colHold);

            colHold = new DataGridViewTextBoxColumn();

            colHold.Name = "colFileType";
            colHold.HeaderText = "Extension";
            colHold.DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopLeft;
            colHold.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            dataGridView1.Columns.Add(colHold);

            colHold = new DataGridViewTextBoxColumn();

            colHold.Name = "colDateModified";
            colHold.HeaderText = "Date Modified";
            colHold.DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopRight;
            colHold.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            dataGridView1.Columns.Add(colHold);

            dataGridView1.RowTemplate.Height = 18;

            //Replace the directory below with a directory on your computer.
            string strDirLocal = @"C:\documents";
            int nRow = 0;
            string sFileName = "";
            decimal nFileSize = 0;
            string sExt = "";
            string sDateModified = "";

            if (System.IO.Directory.Exists(strDirLocal))
            {                
                foreach (string sPath in System.IO.Directory.GetFiles(strDirLocal, "*.*"))
                {
                    //http://msdn.microsoft.com/en-us/library/ms404308.aspx
                    Icon FileIcon = SystemIcons.WinLogo;
                    FileIcon = Icon.ExtractAssociatedIcon(sPath);
                    //Add the file to the TextBox, and remove the Path from the sPath string,
                    //leaving the file name.
                    dataGridView1.Rows.Add();
                    nRow = dataGridView1.Rows.Count - 1;
                    sFileName = sPath.Replace(strDirLocal + @"\", "");
                    sDateModified = System.IO.File.GetLastWriteTime(sPath).ToString();
                    //sFileSize = System.IO.File.ReadAllBytes(sPath).ToString();
                    nFileSize = System.IO.File.ReadAllBytes(sPath).Length;
                    nFileSize = nFileSize / 1024;
                    nFileSize = System.Math.Round(nFileSize);
                    if (nFileSize < 1)
                    {
                        nFileSize = 1;
                    }
                    if (System.IO.File.ReadAllBytes(sPath).Length == 0)
                    {
                        nFileSize = 0;
                    }
                    string[] sExtHold = sPath.Split('.');
                    sExt = sExtHold[sExtHold.Count()-1];
                    //http://www.codeproject.com/KB/grid/ImagePreviewDataGridView.aspx

                    dataGridView1.Rows[nRow].Cells[0].Value = FileIcon;                                        
                    dataGridView1.Rows[nRow].Cells[1].Value = sFileName;
                    dataGridView1.Rows[nRow].Cells[2].Value = nFileSize;
                    dataGridView1.Rows[nRow].Cells[3].Value = sExt;
                    dataGridView1.Rows[nRow].Cells[4].Value = sDateModified;                    
                }
            }

Please note, if a file is open in the directory, you will receive an error.  You should add error handling to account for this error.

Related Links:

http://msdn.microsoft.com/en-us/library/ms404308.aspx

http://www.codeproject.com/KB/grid/ImagePreviewDataGridView.aspx

Export To A Text File From A DataGridView Control

Create a HTML File From a DataGridView Control

Display A Text File In A DataGridView

Display A Directory List In A TextBox Control

Tagged with: , , , , , , ,
Posted in Visual Studio Code Examples

Visual Studio String Functions-CSharp


string sHold = “”;
int nIndex = 0;


//Replaces a string with a specified string.
sHold = “String Test”;
sHold = sHold.Replace(“String”, “Replace”);
//Returns “Replace Test”

//Removes a specified number of characters from a string
//The first number is the starting position, and the last
//number is the ending position.
sHold = “String Test”;
sHold = sHold.Remove(0, 3);
//Result = “ing Test”

//Returns the last index of a specified string.
sHold = “String Test”;
nIndex = sHold.LastIndexOf(“s”);
//Result = 10

//Inserts a string in the specified position.
sHold = “String Test”;
sHold = sHold.Insert(sHold.Length, ” IndexOf”);
//Result = “String Test IndexOf”

//Return the index of a specified string.
sHold = “String Test”;
nIndex = sHold.IndexOf(“r”);
//Result = 3

//Convert To Upper Case
sHold = “String Test”;
sHold = sHold.ToUpper();
//Result = “STRING TEST”

//Convert To Lower Case
sHold = “String Test”;
sHold = sHold.ToLower();
//Result = “string test”

//PadLeft adds specified characters to the beginning of the string.
//The number represents the total length of the resulting string
//including the number of padding characters.
sHold = “String Test”;
sHold = sHold.PadLeft(sHold.Length + 5, Convert.ToChar(” “));
//Result = ” String Test”

//PadRight adds specified characters to the end of the string.
//The number represents the total length of the resulting
string
//including the number of padding characters.
sHold = “String Test”;
sHold = sHold.PadLeft(6, Convert.ToChar(” “));
//Result = “String Test”

sHold = “String Test”;
sHold = sHold.PadRight(sHold.Length + 5, Convert.ToChar(” “));
//Result = “String Test ”

sHold = “String Test”;
sHold = sHold.PadRight(6, Convert.ToChar(” “));
//Result = “String Test”

//EndsWith Determines if a string ends with a specified string and returns
//true or false.
bool bTest = false;
sHold = “String Test”;
bTest = sHold.EndsWith(“Test”);
//Result = true

//StartsWith Determines if a string begins with a specified string and returns
//true or false.
bTest = false;
sHold = “String Test”;
bTest = sHold.StartsWith(“String”);
//Result = true

//Substring returns a string based on the starting index and ending index.
//If the starting or ending specified falls outside the length
of the string,
//an error will be generated.
sHold = “String Test”;
sHold = sHold.Substring(2, 4);
//Result = ring

//Contains determines if a string contains the specified string.
sHold = “String Test”;
bTest = sHold.Contains(“ring”);
//Result = true

//Trims all spaces from beginning and end of specified string.
sHold = ” String Test “;
sHold = sHold.Trim();
//Result = “String Test”

Tagged with: , , , , ,
Posted in Visual Studio Code Examples

Search Engine Features, You May Not Know About

Search engines can be used for more than just searching.  There three major search engines, Google, Bing, and Yahoo, and these search engines can also be used to perform calculations, conversions, return instant weather results, define words, get local time around the world, and return instant stock quotes.

Calculations

If you need to solve a math problem quickly, just navigate to one of the search engines, and type in your math problem, and press the enter key.  The search engine will return the answer.

Listed below are some of the equation symbols:

* = multiplication
/ = division
cos = cosine
sin = sine
sqrt = square root
^ = raised to the power of (5^2 = 25)

Instant Weather Results

If you would like to receive instant weather results, just type weather, the name of the city, and press the Enter Key.

 

Conversions

If you need to convert one unit of measure to another, I.E. km to miles, cups to gallons, etc.  Just type in the conversion you need and press the Enter Key.

Define Words

If you need to know the definition of word, you do not have to go to dictionary.com, just type define and the name of the word you would like to have defined.

Get Local Time Around The World

If you would like to know the current local time of any location in the world, just type local time, the name of the location, and press the Enter Key.

Instant Stock Quotes

If you want to get the current value of a stock, just type in the stock symbol and press the Enter Key.  The current value of the stock will be returned along with a graph of activity for the current day.

Tagged with: , , , , ,
Posted in Search Engine Tips

Display A Directory List In A TextBox Control

Display A Directory List In A TextBox Control

First complete the following steps:

Create a new C# Windows Application in Visual Studio.
Add a button control to the form.
Add a TextBox Control to the form.
Change the Multiline Property of the TextBox Control to ‘True’.
Change the ScrollBars Property of the TextBox Control to ‘Vertical’.
Resize the TextBox Control to fit the Form.

Double Click on the Button Control, and add the code below to the Click Event.


private void button1_Click(object sender, EventArgs e)
{
    //Replace the directory below with a directory on your computer.
    string strDirLocal = @"C:\documents\CodeExamples";
    if (System.IO.Directory.Exists(strDirLocal))
    {
        foreach (string sPath in System.IO.Directory.GetFiles(strDirLocal, "*.*"))
        {
            //Add the file to the TextBox, and remove the Path from the sPath string,
            //leaving the file name.
            textBox1.Text = textBox1.Text + sPath.Replace(strDirLocal + @"\","") + "\r\n";
        }
    }
}
Tagged with: , , , , , ,
Posted in Visual Studio Code Examples

Create a HTML File From a DataGridView Control

First complete the following steps:

Create a new C# Windows Application in Visual Studio.
Add two button controls to the form.
Add a DataGridView Control to the form.


Double Click on the Form and add the code below to the Load Event of the Form:



private void frmDataGridExportHTML_Load(object sender, EventArgs e)
{
    //If you manually add rows to a DataGridView, you must disable the
    //AllowUserToAddRows function.  The function can be enabled after
    //you have added the rows.
    dataGridView1.AllowUserToAddRows = false;

    //The code below adds Columns to the DataGridView control

    DataGridViewColumn colHold = new DataGridViewTextBoxColumn();
    colHold.Name = "col1";
    colHold.HeaderText = "FIELD1";
    dataGridView1.Columns.Add(colHold);

    colHold = new DataGridViewTextBoxColumn();

    colHold.Name = "col2";
    colHold.HeaderText = "FIELD2";
    dataGridView1.Columns.Add(colHold);

    colHold = new DataGridViewTextBoxColumn();

    colHold.Name = "col3";
    colHold.HeaderText = "FIELD3";
    dataGridView1.Columns.Add(colHold);

    colHold = new DataGridViewTextBoxColumn();

    colHold.Name = "col4";
    colHold.HeaderText = "FIELD4";
    dataGridView1.Columns.Add(colHold);

    //The code below adds rows and fills cells with values to be exported.

    dataGridView1.Rows.Add();

    dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[0].Value = "1";
    dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[1].Value = "2";
    dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[2].Value = "3";
    dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[3].Value = "4";

    dataGridView1.Rows.Add();

    dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[0].Value = "5";
    dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[1].Value = "6";
    dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[2].Value = "7";
    dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[3].Value = "8";
}

Double Click on the ‘button1’ Control and add the code below to the Click Event of the button.
This code displays the data from the DataGridView Control in a table format.



private void button1_Click(object sender, EventArgs e)
{
    string sLine = "<table border=" + "\"" + "1" + "\"" + ">";            
    //This line of code creates a html file for the data export.
    System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\HTMLFile1.html");
    file.WriteLine(sLine);
    sLine = "";
    try
    {   
        sLine = "<tr>";
        file.WriteLine(sLine);
        //This for loop places the column headers into the first row of the HTML table.
        for (int c = 0; c <= dataGridView1.Columns.Count - 1; c++)
        {
            sLine = "<td>" + dataGridView1.Columns[c].HeaderText + "</td>";
            file.WriteLine(sLine);
        }
        sLine = "";

        //This for loop loops through each row in the DataGridView.
        for (int r = 0; r <= dataGridView1.Rows.Count - 1; r++)
        {
            sLine = sLine + "<tr>";
            file.WriteLine(sLine);
            sLine = "";
            //This for loop loops through each column, and the row number
            //is passed from the for loop above.
            for (int c = 0; c <= dataGridView1.Columns.Count - 1; c++)
            {
                sLine = "<td>" + dataGridView1.Rows[r].Cells[c].Value + "</td>";
                file.WriteLine(sLine);
            }                                        
            //The exported text is written to the html file, one line at a time.
            sLine = "</tr>";

            file.WriteLine(sLine);
            sLine = "";
        }
        sLine = "</table>";

        file.WriteLine(sLine);
        file.Close();
        System.Windows.Forms.MessageBox.Show("Export Complete.", "Program Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    catch (System.Exception err)
    {
        System.Windows.Forms.MessageBox.Show(err.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        file.Close();
    }
}

Double Click on the ‘button2’ Control and add the code below to the Click Event of the button.
This code displays the data in the DataGridView Control in a list format.


private void button2_Click(object sender, EventArgs e)
{
    string sLine = "";
    //This line of code creates a html file for the data export.
    System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\HTMLFile2.html");            
    sLine = "";
    try
    {                
        for (int r = 0; r <= dataGridView1.Rows.Count - 1; r++)
        {                    
            sLine = "";
            for (int c = 0; c <= dataGridView1.Columns.Count - 1; c++)
            {
                sLine = dataGridView1.Columns[c].HeaderText + "<br>";
                file.WriteLine(sLine);
                sLine = System.Convert.ToString(dataGridView1.Rows[r].Cells[c].Value) + "<br>";
                file.WriteLine(sLine);
            }                                        
            sLine = "<p>";
            file.WriteLine(sLine);
        }                
        file.Close();
        System.Windows.Forms.MessageBox.Show("Export Complete.", "Program Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    catch (System.Exception err)
    {
        System.Windows.Forms.MessageBox.Show(err.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        file.Close();
    }
}

Tagged with: , , , , , ,
Posted in Visual Studio Code Examples
%d bloggers like this: