Formating dates in SQL Server 2008

Problem
There are many instances when dates and times don't show up at your doorstep in the format you'd like it to be, nor does the output of a query fit the needs of the people viewing it. One option is to format the data in the application itself. Another option is to use the built-in functions SQL Server provides to format the date string for you.

Solution
SQL Server provides a number of options you can use to format a date/time string. One of the first considerations is the actual date/time needed. The most common is the current date/time using getdate(). This provides the current date and time according to the server providing the date and time. If a universal date/time is needed, then getutcdate() should be used. To change the format of the date, you convert the requested date to a string and specify the format number corresponding to the format needed. Below is a list of formats and an example of the output:

DATE FORMATS
Format #	Query (current date: 12/30/2006)	Sample
1	select convert(varchar, getdate(), 1)	12/30/06
2	select convert(varchar, getdate(), 2)	06.12.30
3	select convert(varchar, getdate(), 3)	30/12/06
4	select convert(varchar, getdate(), 4)	30.12.06
5	select convert(varchar, getdate(), 5)	30-12-06
6	select convert(varchar, getdate(), 6)	30 Dec 06
7	select convert(varchar, getdate(), 7)	Dec 30, 06
10	select convert(varchar, getdate(), 10)	12-30-06
11	select convert(varchar, getdate(), 11)	06/12/30
101	select convert(varchar, getdate(), 101)	12/30/2006
102	select convert(varchar, getdate(), 102)	2006.12.30
103	select convert(varchar, getdate(), 103)	30/12/2006
104	select convert(varchar, getdate(), 104)	30.12.2006
105	select convert(varchar, getdate(), 105)	30-12-2006
106	select convert(varchar, getdate(), 106)	30 Dec 2006
107	select convert(varchar, getdate(), 107)	Dec 30, 2006
110	select convert(varchar, getdate(), 110)	12-30-2006
111	select convert(varchar, getdate(), 111)	2006/12/30
TIME FORMATS
8 or 108	select convert(varchar, getdate(), 8)	00:38:54
9 or 109	select convert(varchar, getdate(), 9)	Dec 30 2006 12:38:54:840AM
14 or 114	select convert(varchar, getdate(), 14)	00:38:54:840

You can also format the date or time without dividing characters, as well as concatenate the date and time string:

Sample statement	Output
select replace(convert(varchar, getdate(),101),'/','')	12302006
select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','')	12302006004426

Next Steps

• The formats listed above are not inclusive of all formats provided. Experiment with the different format numbers to see what others are available
• These formats can be used for all date/time functions, as well as data being served to clients, so experiment with these data format conversions to see if they can provide data more efficiently

Generate Random password SQL Server 2008

Well I was transferring users from one table to another and had to add random passwords to the user table.

Could not figure out a way to save my life and then in desperation came up with this methord


SELECT [PERSON_ID]  ,'IAI'+ SUBSTRING(convert(varchar(50),NEWID()),0,6) as password from Persons


Hope the next time I dont spend hours trying to randomize characters and strings :)

javascript confirm C# button

In all good web applications, the user is asked to confirm whether he/she wants to delete something in case the delete button was pressed accidentally.

Although it seems like a pain to do, it is actually really easy if you find it acceptable to use javascript's “confirm” statement, which will popup a dialog box with a particular question with “ok” and “cancel” buttons. You have no control of the title of the popup, but in IE it says “Microsoft Internet Explorer“ and I believe it says “[Javascript Application]“ or similar in Firebird.





The javascript code for it is simple:


function confirm_delete()
{
  if (confirm("Are you sure you want to delete the custom search?")==true)
    return true;
  else
    return false;
}



and 



       < asp:Button ID="btnRemoveSchool" runat="server" CssClass="btn" Text="Remove the School/Organization" OnClientClick="javascript:return confirm_delete();" / >

Add Item to DropDownList After DataBind

We often want the first option in a drop-down list to not default to the first item in the list. Instead we would prefer the option of adding a –Select Item– at the top of theasp:DropDownList. After the DataBind, execute an Item.Insert.

ddlAuthor.DataTextField = "FullName";
ddlAuthor.DataValueField = "AuthorID";
ddlAuthor.DataSource = authorDB.GetAuthors();
ddlAuthor.DataBind();
ddlAuthor.Items.Insert(0, "-- Select Author --");

Page flicker on postback

I was working on a form where one dropdownlist is populated based on values of selected item in another dropdown and it caused the page to flicker each time.

So, Robicool from work gave me this cool piece of code to stop the annoyng page flickers


 protected void Page_PreInit(object sender, EventArgs e)
    {

        if (Request.Browser.IsBrowser("IE"))
        {
            string v = Request.Browser.Version;
            if (v.Length > 0)
                v = v.Substring(0, 1);
            if ((v == "7") || (v == "6"))
            {
                SmartNavigation = true;
                MaintainScrollPositionOnPostBack = false;
            }
            else
            {
                SmartNavigation = false;
                MaintainScrollPositionOnPostBack = true;
            }
        }
        else
        {
            SmartNavigation = false;
            MaintainScrollPositionOnPostBack = true;
        }
    }

Potato Pattis Reciepe

5 medium potatoes
A bunch of fresh coriander leaves, chopped
1 tsp jeera / cumin
2 green chillies, chopped
1/2 tsp red chilli powder
1 to 2 tbsp all-purpose flour
1/4 cup - Onion, chopped finely (optional....I dont use it)
1/2 tsp - Ginger, grated (optional....I dont use it)
Salt to taste

• Boil the potatoes and peel off the skin.
• After cooling mash it.. Divide into two equal parts 
• First part of potatoes get slow stir fried with cumin, chillies, coriander, chilli powder and salt. Cool down the mix and divide it into 10-12 small portions and roll into balls
  
• add flour to second half of potatoes and prepare it like a dough. Pls dnt add any water.
  
• To make final pattis , take flour dough in hand, make a small circle out of it, put the spiced potato ball inside it and close the ball. make sure the spiced potatos are completely covered by floured potatos else it explodes while frying....
  

• Heat oil in pan and deep fry the pattis till light brown colour.

• Serve it hot with ketch up.

Flush Socket in .NET or C# to ensure send of acknowledgement

Today, I faced a problem regarding synchronizing a C# TCP/IP Client with a JAVA TCP/IP server. 

The problem was, client sends data and waits for acknowledgment. Server listens for data and when it gets data, it sends acknowledgment to the client and start listening for data again. Now when server starts listening for data again it gets old data i.e. it was not getting blocked on the read() function which it should, as client hasn't send any more data.


the .Net Socket Send page (http://msdn.microsoft.com/en-us/library/ms145160.aspx) has the following:

"There is also no guarantee that the data you send will appear on the network immediately. To increase network efficiency, the underlying system may delay transmission until a significant amount of outgoing data is collected. A successful completion of the Send method means that the underlying system has had room to buffer your data for a network send."

So, the problem was that the data was not being flushed. I searched for Flush method in System.Net.Sockets.Socket class but it was not there. There was a suggestion like:

1. Use the SetSocketOption function and set the value of ReceiveBuffer option to 0. It didn't work. Though the recivebuffer length was 0, the data was still being buffered.
2. Use the IOControl function to set the value for Flush option. But you won't find the integer value for Flush Option (atleast I was not able to)

I tried to use NetworkStream class. You create a new stream using socket and then you create StreamReader and StreamWrite object using NetworkStream object

NetworkStream stream = new NetworkStream(socket);
StreamReader sr = new StreamReader(stream);
StreamWriter sw = new StreamWriter(stream);

Then, use Flush() method of StreamWriter to flush the data. You can also set the property called AutoFlush of StreamWriter to automatically flush the stream after write operation. 
But Flush() method is more reliable according to their site...


This is my code in case you are really really curious


Instead of doing 


 //first send an acknowledgement to client  saying we are good...Now you can stop

          const string sendData = "< response status="\" > success < / response >";

                      byte[] bytes = Encoding.UTF8.GetBytes(sendData);
                      if (listenerSocket != null)
                      {
                          listenerSocket.Send(bytes, bytes.Length, 0);
                      }


I am doing ....



     NetworkStream ns = new NetworkStream(listenerSocket);
            StreamWriter w = new StreamWriter(ns);

          const string sendData = "< response status="\" > success < / response >";

            string message;
            try
            {
                w.Write(sendData);
                w.Flush();
                message = String.Format("\n\nPOST successful. bytes sent " + xml.Length);
            }
            catch (Exception ex)
            {
                message = String.Format("\n\nPOST failed. Received exception {0}", ex);
            }
            finally
            {
                w.Dispose();
                ns.Dispose();
            }

And yet the server does not receive any acknowledgment. I am very clueless at this point and any suggestion would be more than welcomed.

Update:

I think I have figured out a solution

This send code seems to be working in my case

 //--trying the asynchronous thing
IAsyncResult ar = listenerSocket.BeginSend(asen.GetBytes(xml), 0, xml.Length, SocketFlags.None, null, null);
listenerSocket.EndSend(ar);
string message = String.Format("\n\nPOST successful. bytes sent " +ar.ToString()+"\n"+ xml.Length);

Hope it works for you as well

Source Control Explorer Accidental deletion and file recovery

Sometimes, you do stupid things. But VSS is definitely more forgiving than real life and totally and completely saved me hours of rework.

1) Tried to copy files in Visual Studio 2010.
2) It moved the aspx file but not cs file
3) Deleted the original files

So, now I had a aspx file sitting around without a cs file... And I cried...

But then I tried this...

1) Shut down all VSS clients / VS IDEs.
2) Open Visual SourceSafe Explorer and login into VSS database.
3) Undo all the checkouts of any projects or the solution.
4) If the projects were modified by a check-in, roll it back.
5) Go to the parent folders, show properties, and undelete any accidentally deleted files.
6) Do a GET with VSS and make sure the project file as well as the "project's files" are correct in your working folder.
7) Shut down VSS explorer.
8) Try an Open of the project with Visual Studio.

And it worked... YES it totally did....

Where is my windows authentication in IIS 7 ?

Ahh... I installed II7 on windows server 2008 and realized that in the paranoia of making the server secure, I didnt install windows authentication...

But then I couldnt find the feature anywhere.. and trust me, I looked..

Finally, I figured it out...

So, putting a note here for future reference

Windows Server 2008 or Windows Server 2008 R2

1. On the taskbar, click Start, point to Administrative Tools, and then click Server Manager.
2. In the Server Manager hierarchy pane, expand Roles, and then click Web Server (IIS).
3. In the Web Server (IIS) pane, scroll to the Role Services section, and then click Add Role Services.
4. On the Select Role Services page of the Add Role Services Wizard, select Windows Authentication, and then click Next.
   
5. On the Confirm Installation Selections page, click Install.
6. On the Results page, click Close.

Chrome v/s IE and firefox

I was looking at my website usage logs for last month and compared it to the last year's and realized something that I knew already.

Chrome is definitely taking a chip of market share from IE and firefox.

Here are my numbers for the various browsers used to access my site

Even though chrome is still a small part of total traffic, given the current trend, the numbers are bound to pick up....

On a side note, am excited about IE9.

Hari Om Pawaar

I love this poetry...

How can I format a number to x decimal places?

If you have the number 28 and you want to display it as 28.00 here's how you can format it:

int val = 28;
string formatted = val.ToString("N2");

Of course, if you want to format it to 5 decimal places just use ToString("N5") instead.