Date Conversion in SQL

SELECT convert(datetime,'15/03/18',3) -- 2018-03-15 00:00:00.000

SELECT convert(datetime,'15.03.18',4) -- 2018-03-15 00:00:00.000

SELECT convert(varchar,getdate()) -- Mar 15 2018 10:35AM

SELECT convert(varchar,getdate(),0) -- Mar 15 2018 10:35AM

SELECT convert(varchar,getdate(),1) -- 03/15/18

SELECT convert(varchar,getdate(),2) -- 18.03.15

SELECT convert(varchar,getdate(),3) -- 15/03/18

SELECT convert(varchar,getdate(),4) -- 15.03.18

SELECT convert(varchar,getdate(),5) -- 15-03-18

SELECT convert(varchar,getdate(),6) -- 15 Mar 18

SELECT convert(varchar,getdate(),7) -- Mar 15, 18

SELECT convert(varchar,getdate(),8) -- 10:39:39

SELECT convert(varchar,getdate(),9) -- Mar 15 2018 10:39:48:373AM

SELECT convert(varchar,getdate(),10) -- 03-15-18

SELECT convert(varchar,getdate(),11) -- 18/03/15

SELECT convert(varchar,getdate(),15) -- 180315

SELECT convert(varchar,getdate(),13) -- 15 Mar 2018 10:41:07:590

SELECT convert(varchar,getdate(),14) -- 10:41:25:903

SELECT convert(varchar,getdate(),20) -- 2018-03-15 10:43:56

SELECT convert(varchar,getdate(),21) -- 2018-03-15 10:44:04.950

SELECT convert(varchar,getdate(),22) -- 03/15/18 10:44:50 AM

SELECT convert(varchar,getdate(),23) -- 2018-03-15

SELECT convert(varchar,getdate(),24) -- 10:45:45

SELECT convert(varchar,getdate(),25) -- 2018-03-15 10:46:11.263

SELECT convert(varchar, getdate(), 100) -- Oct 23 2016 10:22AM (or PM)
SELECT convert(varchar, getdate(), 101) -- 10/23/2016

SELECT convert(varchar, getdate(), 102) -- 2016.10.23

SELECT convert(varchar, getdate(), 103) -- 23/10/2016

SELECT convert(varchar, getdate(), 104) -- 23.10.2016

SELECT convert(varchar, getdate(), 105) -- 23-10-2016

SELECT convert(varchar, getdate(), 106) -- 23 Oct 2016

SELECT convert(varchar, getdate(), 107) -- Oct 23, 2016

SELECT convert(varchar, getdate(), 108) -- 09:10:34

SELECT convert(varchar, getdate(), 109) -- Oct 23 2016 11:10:33:993AM (or PM)

SELECT convert(varchar, getdate(), 110) -- 10-23-2016

SELECT convert(varchar, getdate(), 111) -- 2016/10/23

SELECT convert(varchar, getdate(), 112) -- 20161023

SELECT convert(varchar, getdate(), 113) -- 23 Oct 2016 06:10:55:383

SELECT convert(varchar, getdate(), 114) -- 06:10:55:383(24h)

SELECT convert(varchar, getdate(), 120) -- 2016-10-23 06:10:55(24h)

SELECT convert(varchar, getdate(), 121) -- 2016-10-23 06:10:55.383

SELECT convert(varchar, getdate(), 126) -- 2016-10-23T06:10:55.383

SELECT [Date] = CAST('20120228' AS date) -- 2012-02-28

SELECT [Datetime] = CAST('20120228' AS datetime) -- 2012-02-28 00:00:00.000

SELECT [Datetime] = CAST('20120228' AS smalldatetime) -- 2012-02-28 00:00:00

DECLARE @sDate varchar(10)

SET @sDate = '2010/03/15'

-- DATEDIFF (delta) between two dates in months

SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), @sDate)

SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), CAST(@sDate as datetime))

SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), CONVERT(datetime,@sDate))

SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), CONVERT(datetime,@sDate,111))

-- Seme results for above: 2008-12-29 11:04:51.097 15

-- SQL convert to datetime with wrong style (111 correct, 112 incorrect)

SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), CONVERT(datetime,@sDate,112))

Above SQL query is very much helpfull for me. I refer this from sqlusa.com

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Today i tried to restore the backup Database to another SQL Server 2008 R2, but i got the error as

"An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)"

Additional information:
The media family on device 'Backupfile.bak' is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)

Reason For Failur

The backup was corrupted. The file was transferred using FTP in text mode rather than binary mode.

Format Data in the Windows Forms DataGridView Control

Useful coding for formation the cells in datagridview in MSDN site

To enable wordwrap in text-based cells

this.dataGridView1.DefaultCellStyle.WrapMode =     DataGridViewTriState.True;

To specify the text alignment of DataGridView cells
this.dataGridView1.Columns["CustomerName"].DefaultCellStyle     .Alignment = DataGridViewContentAlignment.MiddleRight;
To customize the display of null database values
this.dataGridView1.DefaultCellStyle.NullValue = "no entry";
To format currency and date values
this.dataGridView1.Columns["UnitPrice"].DefaultCellStyle.Format = "c"; this.dataGridView1.Columns["ShipDate"].DefaultCellStyle.Format = "d";

And also as below
  dataGridView1.Columns[0].DefaultCellStyle.Format = "dd'/'MM'/'yyyy";
            //Get 13/12/2007
            dataGridView1.Columns[1].DefaultCellStyle.Format = "dd'/'MM'/'yyyy hh:mm:ss tt";
            //Get 13/12/2007 5:00:11 PM

validate Datagridview column

If you want to validate the column in the datagridview then you can choose "CellValidating"

Sample Codeing is as below

 private void dataGridView1_CellValidating(object sender, DataGridViewCellValidatingEventArgs e)
       {
           DataGridViewTextBoxCell cell = dataGridView1[e.ColumnIndex, e.RowIndex] as DataGridViewTextBoxCell;

           if (cell != null)
           {
               char[] chars = e.FormattedValue.ToString().ToCharArray();
               foreach (char c in chars)
               {
                   if (char.IsDigit(c) == false)
                   {
                       MessageBox.Show("You have to enter digits only");
                     
                       e.Cancel = true;
                       break;
                   }
               }
           }
       }

Suppose if you want to validate after pressing enter, then you can use as below

protected override bool ProcessDialogKey(Keys keyData)
    {
        if (keyData == Keys.Enter)
        {
            DataGridViewCell currentCell = CurrentCell;
            EndEdit();
            CurrentCell = null;
            CurrentCell = currentCell;
            return true;
        }
        return base.ProcessDialogKey(keyData);
    }

else you can also use EditControlShowing event

void dataGridView1_EditingControlShowing(object sender,
                 DataGridViewEditingControlShowingEventArgs e)
            {
                if (this.dataGridView1.CurrentCell.ColumnIndex == 0)
                {
                    if (e.Control is TextBox)
                    {
                        TextBox tb = e.Control as TextBox;
                        tb.KeyPress -= new KeyPressEventHandler(tb_KeyPress);
                        tb.KeyPress += new KeyPressEventHandler(tb_KeyPress);
                    }
                }
            }
 
            void tb_KeyPress(object sender, KeyPressEventArgs e)
            {
                if (!(char.IsDigit(e.KeyChar)))
                {
                    Keys key = (Keys)e.KeyChar;
 
                    if (!(key == Keys.Back || key == Keys.Delete))
                    {
                        e.Handled = true;
                    }
                }
            }
        }