How to seperate date and time while retrieving from sqlserver 2005

Hi Shivaraj,
   There are several patterns available to get out format for date in Convert function ,
   For your query you need to change 101 to 106 :
  
    select CONVERT(VARCHAR(20),DateTimeColumn,106) FROM TableName;

   Check this out SQL Server CONVERT() Function

shivraj just try this

 Label1.Text = dat.ToString("dd- MMM- yyyy");//here date is a variable where u r storing DateTime value.

Shivaraj if query get resolved then mark as accepted answer so that others get help

use following functions it is in vb.net you can convert it into c#, you can tool easily on google easily

follow my article same for this
http://www.vbdotnetheaven.com/UploadFile/5eae74/6995/?ArticleID=418a3200-8c1f-4866-a8d7-e25113fde87a

Public Class ClsDate
  Shared Function GetFormatedDate(ByVal strdate As String, ByVal format As String) As String
  Dim Splitstrdate As String()
  If strdate.Trim <> "" Then
  Select Case format
  Case "YYYYMMDD"
  GetFormatedDate = GetMonth(strdate.Substring(4, 2)) & " " & strdate.Substring(6, 2) & " " & strdate.Substring(0, 4)
  Case "DDMMYYYY"
  GetFormatedDate = GetMonth(strdate.Substring(2, 2)) & " " & strdate.Substring(0, 2) & " " & strdate.Substring(4, 4)
  Case "DD-MM-YYYY"
  Splitstrdate = Split(strdate, "-")
  GetFormatedDate = GetMonth(Splitstrdate(1)) & " " & Splitstrdate(0) & " " & Splitstrdate(2)
  Case "DD/MM/YYYY"
  Splitstrdate = Split(strdate, "/")
  If Left(Splitstrdate(0), 1) = "0" Then
  GetFormatedDate = GetMonth(Splitstrdate(1)) & "  " & Right(Splitstrdate(0), 1) & " " & Splitstrdate(2)
  Else
  GetFormatedDate = GetMonth(Splitstrdate(1)) & " " & Splitstrdate(0) & " " & Splitstrdate(2)
  End If
  End Select
  Else
  GetFormatedDate = ""
  End If
  End Function


Shared Function GetMonth(ByVal strmonth As String) As String
  Select Case strmonth
  Case "1", "01"
  GetMonth = "Jan"
  Case "2", "02"
  GetMonth = "Feb"
  Case "3", "03"
  GetMonth = "Mar"
  Case "4", "04"
  GetMonth = "Apr"
  Case "5", "05"
  GetMonth = "May"
  Case "6", "06"
  GetMonth = "Jun"
  Case "7", "07"
  GetMonth = "Jul"
  Case "8", "08"
  GetMonth = "Aug"
  Case "9", "09"
  GetMonth = "Sep"
  Case "10"
  GetMonth = "Oct"
  Case "11"
  GetMonth = "Nov"
  Case "12"
  GetMonth = "Dec"
  End Select
  End Function

Hello sir thanks a lot....m getting the output but i want it in this format 27-Apr-11..i tried but its not getting please help me out

Some date time operations use this

using System;

namespace Utilities
{
    public static partial class DateTimeExtensions
    {       
        public static DateTime FirstDayOfYear(this DateTime dt)
        {
            return new DateTime(dt.Year, 1, 1);
        }

        public static DateTime FirstDayOfYear(this DateTime dt, DayOfWeek dow)
        {
            return dt.FirstDayOfYear().NextDay(dow, true);
        }

        public static DateTime LastDayOfYear(this DateTime dt)
        {
            return dt.FirstDayOfYear().AddYears(1).AddDays(-1);
        }

        public static DateTime LastDayOfYear(this DateTime dt, DayOfWeek dow)
        {
            return dt.LastDayOfYear().PreviousDay(dow, true);
        }

        public static DateTime FirstDayOfMonth(this DateTime dt)
        {
            return new DateTime(dt.Year, dt.Month, 1);
        }

        public static DateTime FirstDayOfMonth(this DateTime dt, DayOfWeek dow)
        {
            return dt.FirstDayOfMonth().NextDay(dow, true);
        }

        public static DateTime LastDayOfMonth(this DateTime dt)
        {
            return dt.FirstDayOfMonth().AddMonths(1).AddDays(-1);
        }

        public static DateTime LastDayOfMonth(this DateTime dt, DayOfWeek dow)
        {
            return dt.LastDayOfMonth().PreviousDay(dow, true);
        }

        public static DateTime PreviousDay(this DateTime dt)
        {
            return dt.Date.AddDays(-1);
        }

        public static DateTime PreviousDay(this DateTime dt, DayOfWeek dow)
        {
            return dt.PreviousDay(dow, false);
        }

        public static DateTime PreviousDay(this DateTime dt, DayOfWeek dow, bool includeThis)
        {
            int diff = dt.DayOfWeek - dow;
            if ((includeThis && diff < 0) || (!includeThis && diff <= 0)) diff += 7;
            return dt.Date.AddDays(-diff);
        }

        public static DateTime NextDay(this DateTime dt)
        {
            return dt.Date.AddDays(1);
        }

        public static DateTime NextDay(this DateTime dt, DayOfWeek dow)
        {
            return dt.NextDay(dow, false);
        }

        public static DateTime NextDay(this DateTime dt, DayOfWeek dow, bool includeThis)
        {
            int diff = dow - dt.DayOfWeek;
            if ((includeThis && diff < 0) || (!includeThis && diff <= 0)) diff += 7;
            return dt.Date.AddDays(diff);
        }

        public static int DaysInYear(this DateTime dt)
        {
            return (dt.LastDayOfYear() - dt.FirstDayOfYear()).Days + 1;
        }

        public static int DaysInYear(this DateTime dt, DayOfWeek dow)
        {
            return (dt.LastDayOfYear(dow).DayOfYear - dt.FirstDayOfYear(dow).DayOfYear) / 7 + 1;
        }

        public static int DaysInMonth(this DateTime dt)
        {
            return (dt.LastDayOfMonth() - dt.FirstDayOfMonth()).Days + 1;
        }

        public static int DaysInMonth(this DateTime dt, DayOfWeek dow)
        {
            return (dt.LastDayOfMonth(dow).Day - dt.FirstDayOfMonth(dow).Day) / 7 + 1;
        }

        public static bool IsLeapYear(this DateTime dt)
        {
            return dt.DaysInYear() == 366;
        }

        public static DateTime AddWeeks(this DateTime dt, int weeks)
        {
            return dt.AddDays(7 * weeks);
        }
    }
}

use either shrikant's query or when u a retreiving Datatime into any variable use .ToShortDateString(); function which is inbuid function in c#. u can use this function like

Label1.Text  = dat.ToShortDateString();//here dat is a varible where u r storing datatime values coming from sqlserver.