Convert Date-Time format in SQL Server

Convert Date-Time format in SQL Server

SELECT CONVERT(VARCHAR(20), GETDATE(), 100)
Feb 18 2013 11:29AM (Mon DD YYYY HH:MM AM/PM)
SELECT CONVERT(VARCHAR(20), GETDATE(), 101)
02/18/2013 (MM/DD/YYYY)
SELECT CONVERT(VARCHAR(20), GETDATE(), 102)
2013.02.18 (YYYY.MM.DD)
SELECT CONVERT(VARCHAR(20), GETDATE(), 103)
18/02/2013 (DD/MM/YYYY)
SELECT CONVERT(VARCHAR(20), GETDATE(), 104)
18.02.2013 (DD.MM.YYYY)
SELECT CONVERT(VARCHAR(20), GETDATE(), 105)
18-02-2013 (DD-MM-YYYY)
SELECT CONVERT(VARCHAR(20), GETDATE(), 106)
18 Feb 2013 (DD Mon YYYY)
SELECT CONVERT(VARCHAR(20), GETDATE(), 107)
Feb 18, 2013 (Mon DD, YYYY)
SELECT CONVERT(VARCHAR(20), GETDATE(), 108)
11:37:14 (HH:MM:SS)
SELECT CONVERT(VARCHAR(20), GETDATE(), 109)
Feb 18 2013 11:37:53 (Mon DD YYYY HH:MM:SS)
SELECT CONVERT(VARCHAR(20), GETDATE(), 110)
02-18-2013 (MM-DD-YYYY)
SELECT CONVERT(VARCHAR(20), GETDATE(), 111)
2013/02/18 (YYYY/MM/DD)
SELECT CONVERT(VARCHAR(20), GETDATE(), 112)
20130218 (YYYYMMDD)
SELECT CONVERT(VARCHAR(20), GETDATE(), 113)
18 Feb 2013 11:40:18 (DD Mon YYYY HH:MM:SS)
SELECT CONVERT(VARCHAR(20), GETDATE(), 114)
11:40:54:040 (HH:MM:SS:MMM)24 hr
SELECT CONVERT(VARCHAR(20), GETDATE(), 120)
2013-02-18 11:53:32 (YYYY-MM-DD HH:MM:SS)

-- we can also use as follow

SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD]
13-02-18 (DD-MM-YYYY)
SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD]
13-02-18 (DD-MM-YYYY)

Duplicate Records in SQL Server

see the record without duplicate value

select name,emp_id,City from MyTable

Count Duplicate Records

Syntax:

SELECT YourColumn, COUNT(*) TotalCount

FROM YourTable

GROUP BY YourColumn

HAVING COUNT(*) > 1

ORDER BY COUNT(*) DESC

Example:

SELECT name, 

 COUNT(email) 

FROM users

GROUP BY email

HAVING ( COUNT(email) > 1 )

Delete Duplicate Records – Rows multipul column

DELETE

FROM MyTable

WHERE ID NOT IN

(

SELECT MAX(ID)

FROM MyTable

GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3 HAVING COUNT(*)>1))

Delete Duplicate Records – Rows Single column

DELETE

FROM MyTable

WHERE ID NOT IN

(

SELECT MAX(ID)

FROM MyTable

GROUP BY DuplicateColumn HAVING COUNT(*)>1))

How to fetch data from Excel file to Data Set in ASP .Net

 protected void Page_Load(object sender, EventArgs e)
    {


        if (!IsPostBack)
        {
            GenerateExcelData();
        }
    }


private void GenerateExcelData()
    {
        try
        {
            OleDbConnection oledbConn;
            string path = System.IO.Path.GetFullPath(@"C:\Users\user\Desktop\PriceAnalysis.xls");
            oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
           path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
         
            oledbConn.Open();
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet2$]", oledbConn);
            OleDbDataAdapter oleda = new OleDbDataAdapter();
            oleda.SelectCommand = cmd;
            DataSet ds = new DataSet();
            oleda.Fill(ds, "Table");
            //return ds.Tables[0];
        }
        catch
        {
            //return null;
        }
       

    }
}

Note : where Sheet2 is your sheet name.

How can use Split function in C#

I want split 2009-2010 in year format like: 2009

INPUT :   2009-2010  (financialYear)

OUTPUT: 2009


  char[] splitter1 = { '-' };
            string[] year1 = financialYear.Split(splitter1);
            int str1 = Convert.ToInt32(year1[0].ToString());
          
str1 give result: 2009

How to find list of recently execute store procedure

Select DB_Name(dest.[dbid]) As 'databaseName'
   , Object_Name(dest.objectid) As 'procName'
   , Max(deqs.last_execution_time) As 'last_execution'
From sys.dm_exec_query_stats As deqs
Cross Apply sys.dm_exec_sql_text(deqs.sql_handle) As dest

Group By db_name(dest.[dbid])
   , Object_Name(dest.objectid)
Order By databaseName
   , procName
Option (MaxDop 1);