Conversion of Dates in SQL

The following is a table of constants used in converting date values within SQL. This list is taken from the site of W3C. More information can be found on MSDN.

Style ID	Style Format
100 or 0	mon dd yyyy hh:miAM (or PM)
101	mm/dd/yy
102	yy.mm.dd
103	dd/mm/yy
104	dd.mm.yy
105	dd-mm-yy
106	dd mon yy
107	Mon dd, yy
108	hh:mm:ss
109 or 9	mon dd yyyy hh:mi:ss:mmmAM (or PM)
110	mm-dd-yy
111	yy/mm/dd
112	yymmdd
113 or 13	dd mon yyyy hh:mm:ss:mmm(24h)
114	hh:mi:ss:mmm(24h)
120 or 20	yyyy-mm-dd hh:mi:ss(24h)
121 or 21	yyyy-mm-dd hh:mi:ss.mmm(24h)
126	yyyy-mm-ddThh:mm:ss.mmm(no spaces)
130	dd mon yyyy hh:mi:ss:mmmAM
131	dd/mm/yy hh:mi:ss:mmmAM

They are used as follows:

CONVERT(VARCHAR(24),GETDATE(),113)

Difference between CAST and CONVERT

In SQL SERVER we have both the CAST keyword, and the CONVERT keyword. Are there any differences?

The difference is simply one of naming.
CONVERT is local to SQL Server, and has great flexibility when using it with date, time, fractional numbers and money values.

CAST is the more ANSI-standard, making it more portable. Use CAST to convert between decimal and numeric values, since it is more loyal to precision.

The beauty of standards, is how many of them there are.

Find which Process is locking a file in Windows XP

Sometimes, frustratingly, you go to delete a file, but the OS will not let you delete it. It keeps telling you that the file is currently in use, even though you can swear that you have closed anything which is currently using it.
This has just happened to me...

Process Explorer to the rescue. Using this very powerful tool, you simply run Process Explorer, and then search for any references to the locked resource. It will very quickly tell you who the culprit is. Kill that process, and you can safely delete the file.

The culprit in my case? Skype!

Difference between Union and Union All

In SQL we have both UNION and UNION ALL.

Are there any differences?

Union simply returns all rows, however with no repetitions. So, in this sense, it works just like a set.

UNION ALL will return all rows, included any repeated ones.

So,

(1,2) (3,4) (4,5) UNION (1,2) (6,7) (8,9)
will return
(1,2) (3,4) (4,5)  (6,7) (8,9) 

while

(1,2) (3,4) (4,5) UNION ALL (1,2) (6,7) (8,9) 
will return
(1,2) (3,4) (4,5) (1,2) (6,7) (8,9) 

UNION ALL is obviously faster than UNION, since it does not do DISTINCT SORT, but please be careful, since their functionality is different.

Open Explorer from Powershell in current folder

When working in powershell, it is very easy to open the current folder to be able to better work with the files within it.

Simply type in

Explorer .
(The dot should be typed also.)

And even shorter way is by typing

ii .

where the ii is short for Invoke-Item

Execution Policy in PowerShell

When you go to run a powershell script for the first time, it might fail. The reason is that by default the Execution Policy is set to not execute scripts (for security).

To check the current policy, type:

Get-ExecutionPolicy

The execution policies you can use are:

Restricted - Scripts won’t run.

RemoteSigned - Scripts created locally will run, but those downloaded from the Internet will not (unless they are digitally signed by a trusted publisher).

AllSigned - Scripts will run only if they have been signed by a trusted publisher.

Unrestricted - Scripts will run regardless of where they have come from and whether they are signed.

You can set PowerShell’s execution policy by using the following cmdlet:

To change the current policy, simply type:

Set-ExecutionPolicy <policy name>

Special Powershell commands

Measure-Object = Size of Result

Get-Member = Get object methods

Get-Help = Man Pages