SQL Server Date Formats

One of the most frequently asked questions in SQL Server forums is how to format a datetime value or column into a specific date format.  Here’s a summary of the different date formats that come standard in SQL Server as part of the CONVERT function.  Following the standard date formats are some extended date formats that are often asked by SQL Server developers.

It is worth to note that the output of these date formats are of VARCHAR data types already and not of DATETIME data type.  With this in mind, any date comparisons performed after the datetime value has been formatted are using the VARCHAR value of the date and time and not its original DATETIME value.

Standard Date Formats

SQL Statement

Sample Output

SELECT CONVERT(VARCHAR(20), GETDATE(), 100)

Jan 1 2005 1:29PM 1

SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]

11/23/98

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]

11/23/1998

SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]

72.01.01

SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]

1972.01.01

SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]

19/02/72

SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]

19/02/1972

SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]

25.12.05

SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]

25.12.2005

SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]

24-01-98

SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]

24-01-1998

SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]

04 Jul 06

SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]

04 Jul 2006

SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]

Jan 24, 98

SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]

Jan 24, 1998

SELECT CONVERT(VARCHAR(8), GETDATE(), 108)

03:24:53

SELECT CONVERT(VARCHAR(26), GETDATE(), 109)

Apr 28 2006 12:32:29:253PM

SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]

01-01-06

SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]

01-01-2006

SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]

98/11/23

SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]

1998/11/23

SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]

980124

SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]

19980124

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

28 Apr 2006 00:34:55:190

SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]

11:34:23:013

SELECT CONVERT(VARCHAR(19), GETDATE(), 120)

1972-01-01 13:42:24

SELECT CONVERT(VARCHAR(23), GETDATE(), 121)

1972-02-19 06:35:24.489

SELECT CONVERT(VARCHAR(23), GETDATE(), 126)

1998-11-23T11:25:43:250

SELECT CONVERT(VARCHAR(26), GETDATE(), 130)

28 Apr 2006 12:39:32:429AM

SELECT CONVERT(VARCHAR(25), GETDATE(), 131)

28/04/2006 12:39:32:429AM

Display exactly 2 digits after decimal without Rounding on SQL Server

let say, we had a value 7.35642132 on sql query return, then we want to display it only exactly 2 digits after decimal without Rounding, here is the way :

to get only two digits after decimal for a value 7.35642132
Select LTRIM(RTRIM(STR(7.35642132,10,2)))

the results is 7.35

Run CGI program under IIS 7.0

Looking around I didn’t find a good documentation on how to get good old CGI’s running on IIS 7 or 7.5. Here is a quick walkthrough:

1. Let’s write a quick CGI:
Take the following code and save it as simplecgi.cs in the directory c:\inetpub\wwwroot\cgi

using System;
using System.Collections;

class SimpleCGI
{
    static void Main(string[] args)
    {
        Console.WriteLine("\r\n\r\n");
        Console.WriteLine("<h1>Environment Variables</h1>");
        foreach (DictionaryEntry var in Environment.GetEnvironmentVariables())
            Console.WriteLine("<hr><b>{0}</b>: {1}", var.Key, var.Value);
    }
}

2. Change into the C:\inetpub\wwwroot\cgi directory and compile the source by using the following command-line:

%windir%\Microsoft.NET\Framework\v2.0.50727\csc.exe SimpleCGI.cs

Read more of this post

Excel 2003 Add-in: XML Tools Add-in

Instructions

To install the add-in, complete the following steps:

    1. Start Excel.
    2. On the Tools menu, click Add-Ins.
    3. Click the Browse button and navigate to the .xla file.
    4. Select it and then click OK.
    5. Click Yes to any file copy or overwrite prompts.

 

Unlocking files that are in use

Sometimes you cannot delete or rename a file that is currently in use. You might receive an access violation error, or simply a message telling you that your action could not be completed because the file is open in another program.

image

You may have already come across the Unlocker freeware tool that lets you “unlock” files that are in use by some application.

Here is another way (let’s call it the ‘techie’ way) to unlock files that are in use. It makes use of the Process Explorer tool from Windows SysInternals.

  • Download the Process Explorer tool. Execute procexp.exe
  • Choose Find Find Handle or DLL option

image

  • Type the name of the file you want to unlock and hit Search.

image

  • The process EXE locking the file and the path to the file are listed. Double click on the result.

image

  • The file handle will be highlighted. Right-click on it and choose Close Handle.

Your file is now unlocked and can now be deleted, moved or renamed.

A little disclaimer here, closing handles might cause data inconsistency, loss and/or other undesirable effects. Make sure you understand what you’re doing before you do it.

Add RunAs to Explorer Context Menu in Vista and Server 2008

As a Domain Admin for a small or large corporation, it’s NEVER a good idea to login as the Domain Admin (or other privileged user) to read email or surf the Internet.  After all, if you pickup a nasty bug/virus, from Outlook or Internet Explorer your logged in credentials will have the ability to spread the virus to every system in the company…  Not a good thing!

 

That’s where the RunAs command (XP and Server 2003) came in handy over the years.  You could login as a non-privileged user but if you needed to do something which required Admin rights, you could hold down Shift and Right Click on the application in Windows Explorer and start the application using your Admin credentials.

 

With the release of Windows Vista and Server 2008, this all changed.  True, you could still use the RunAs.exe from the command line however, if you wanted to do it with Windows Explorer.  Fortunately Mark Russinovich has written a new tool called  which adds the RunAs feature to Windows Explorer.

 

Follow the below Step by Step to get it rolling on your box:

 

 

Add Run As Different User to Windows Explorer Context Menu for Vista and Server 2008

1) After you download ShellRunAs from here, copy it to your WindowsSystem32 folder,

 

2) Open a command line and Runshellrunas /reg

Add Run As Different User to Windows Explorer Context Menu for Vista and Server 2008 :: groovyPost.com

 

3) Click OK to confirm Install completed

Add Run As Different User to Windows Explorer Context Menu for Vista and Server 2008 :: groovyPost.com

 

To test, hold down SHIFT and Right-Click on an application.  You should now see the Run as different user in your context menu.

 

Before:

Add Run As Different User to Windows Explorer Context Menu for Vista and Server 2008 :: groovyPost.com

 

After:

Add Run As Different User to Windows Explorer Context Menu for Vista and Server 2008 :: groovyPost.com

Backing up System Databases

System databases contain information about user databases as well as meta-data about SQL Server, SQL Server Agent, jobs, alerts, DTS packages and more. Therefore, it is crucial to have valid backups of the following databases:

  1. Master
  2. MSDB
  3. Model
  4. Distribution (if using replication)

Another system database, tempdb, is rebuilt each time SQL Server is started. Therefore tempdb does not need to be backed up. The Model database serves as a blue-print for creating new user databases. This database should not have any activity associated with it, other than the database administrator adding or removing objects required in all user databases. Therefore, you should typically use the SIMPLE recovery model for the Model database and only perform full backups for it.

Read more of this post