Convert excel datetime text to a sort-able standard datetime string

Tags

,

Here are a few excel datetime strings in format like “m/d/yyyy h:mm:ss am/pm”, so the length of this string is different in different date/time:

  • 2/3/2017 8:2:32 AM
  • 12/23/2017 1:20:32 PM

Searched a little bit seems there’s no result to convert these examples to a standard sortable string in format like: “yyyy-mm-ddThh:mm:ss”, have to make one

Original DateTime Converted DateTime Formula
2/17/2017 7:13:00 AM 2017-02-17T07:13:00 =TEXT(DATEVALUE(TEXT(DATE(MID(A2,FIND(" ",A2,1)-4,4),MID(A2,1,FIND("/",A2,1)-1),MID(A2,FIND("/",A2,1)+1,FIND("/",A2,FIND("/",A2,1)+1)-FIND("/",A2,1)-1)),"yyyy-mm-dd"))+TIMEVALUE(MID(A2,FIND(" ",A2,1)+1,LEN(A2)-FIND(" ",A2,1))),"yyyy-mm-ddThh:mm:ss")
Advertisements

Send key-strokes Ctrl+Alt+Del through nested RDP sessions

Tags

, , , ,

Credit belongs to https://superuser.com/a/871037/136936

  • Ctrl+Alt+Del won’t work with nested RDP(remote desktop protocol) sessions
  • Ctrl+Alt+End won’t work with nested RDP sessions
  • The only way to make it work is to run “osk.exe”(On-Screen Keyboard) through admin command line and Hold the Ctrl and Alt key from your current physical keyboard then use mouse to click the Del key from OSK UI keyboard

Since this is the third time I need to search this special treatment, mark it down. 🙂

Actually this is pretty useful if you’d like to reset active directory password inside a VM of VM of VM…

Compare two lists of objects by specific properties

Tags

, ,

Credit goes: http://stackoverflow.com/questions/19790211/comparing-two-lists-according-to-specific-properties

.Net Except with IEqualityComparer

// define the Animal class
public class Animal
{
	public Guid Id { get; set; }
	public string AnimalName { get; set; }
	public bool HasTail { get; set; }
}

And then have the following, expect that these two list should be equal to each other, but actually not

var id = Guid.NewGuid();
var animalList1 = new List<Animal>{new Animal { Id=id, AnimalName="cat", HasTail=true}};
var animalList2 = new List<Animal>{new Animal { Id=id, AnimalName="cat", HasTail=true}};

var isSame = !animalList1.Except(animalList2).Any() // expect to be true, but it's false;

In this case, need to implement it’s own IEqualityComparer

public class AnimalComparer : IEqualityComparer<Animal>
{
    ///
<summary>
    /// Implement the Equals methods
    /// </summary>

    /// <param name="x"></param>
    /// <param name="y"></param>
    /// <returns></returns>
    public bool Equals(Animal x, Animal y)
    {
        return x.Id.Equals(y.Id) && x.AnimalName.Equals(y.AnimalName) && x.HasTail == y.HasTail;
    }

    ///
<summary>
    /// Implement its own GetHashCode method
    /// </summary>

    /// <param name="x"></param>
    /// <returns></returns>
    public int GetHashCode(Animal x)
    {
        unchecked
        {
            int hash = 17;
            hash = hash * 23 + x.Id.GetHashCode();
            hash = hash * 23 + x.AnimalName.GetHashCode();
            hash = hash * 23 + x.HasTail.GetHashCode();
            return hash;
        }
    }
}

And now if compare these two lists by using its own comparer, it will succeed

var isSame = !animalList1.Except(animalList2, new AnimalComparer()).Any(); // Now will be true

Fastest way to insert 1 million rows

Tags

,

Here is the fastest way to insert 1 million rows to a table in SQL Server

Credit goes this post: Generate and Insert 1 million rows into simple table

WITH
L0 AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)), -- 2^1
L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B), -- 2^2
L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B), -- 2^4
L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B), -- 2^8
L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B), -- 2^16
L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B), -- 2^32
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS k FROM L5)

insert into yourtable
select k as IdColumn, 'whatever' as otherColumnName
from nums
where k <= 1000000

Get table or view name by search column name keyword

Tags

Here is the script:

SELECT c.object_id,
 CASE
     WHEN OBJECTPROPERTY(c.object_id, 'isTable') = 1
     THEN 'Yes' ELSE 'No' END AS IsTable,
 CASE
     WHEN OBJECTPROPERTY(c.object_id, 'isView')  = 1
     THEN 'Yes' ELSE 'No' END AS IsView,
 OBJECT_SCHEMA_NAME(c.object_id) AS SchemaName,
 OBJECT_NAME(c.object_id) AS TableName,
 c.name AS ColumnName
 FROM sys.columns c
 WHERE c.name LIKE '%MyColumnName%'

Microsoft SQL Server – Search specified column or table in all database at a DB Server

Tags

,

In a development environment normally there are multiple databases installed at one DB Server, and there is a system store procedure called: “sp_msforeachdb” can help us to search a specified column or table easily in these different databases(or other tasks need to be done in different DBs), and here are the sample script:

Search a specified column name in all database at a DB Server

-- Search a specified column name in all database at a DB Server
sp_msforeachdb
'SELECT
''[''+ "?" +''].[''+SCHEMA_NAME(schema_id)+''].[''+t.name+'']''
as "Full table name",
"?" AS DBName,
SCHEMA_NAME(schema_id) AS Schema_Name,
t.name AS Table_Name,
col.name AS Column_Name
FROM [?].sys.tables AS t
INNER JOIN [?].sys.columns col ON t.OBJECT_ID = col.OBJECT_ID
WHERE col.name like ''%ColumnName_search_keyword%''
ORDER BY schema_name, table_name; '

Search a specified table name in all databases at a DB Server

-- Search a specified table name in all databases at a DB Server
sp_msforeachdb
'SELECT
''[''+ "?" +''].[''+SCHEMA_NAME(schema_id)+''].[''+t.name+'']''
as "Full Table Name",
"?" AS DBName,
SCHEMA_NAME(schema_id) AS Schema_Name,
t.name AS Table_Name
FROM [?].sys.tables AS t
WHERE t.name like ''%tablename_search_keyword%'';'