How to seperate date and time while retrieving from sqlserver 2005

Question

Hi Experts... I have stored DateTime format in the database but i need only date at the time of retrive. How to do this? Please help me.. I have the following code SqlConnection con = new SqlConnection(Integrated Security=SSPI;Persist Security Info=False;User ID=CRYSYS1-DESKTOP\Student;Initial Catalog=StdLab;Data Source=CRYSYS1-DESKTOP\SQLEXPRESS); con.Open(); SqlCommand cm = new SqlCommand(SELECT MAX(DateTime) as dd from TB_StudentLogincon); SqlDataReader dr = cm.ExecuteReader(); dr.Read();

Soft Corner · Answer

Hi u can do this by using function CONVERT select CONVERT(VARCHAR(10) DateTimeColumn 101) FROM TableName; Build this query into your code..it will work

Soft Corner · Answer

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

Ankit Nandekar · Answer

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

Mayur Gujrathi · Answer

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

Mayur Gujrathi · Answer

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

Shivaraj Poojary · Answer

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

Mayur Gujrathi · Answer

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable{mso-style-name:Table Normal;mso-tstyle-rowband-size:0;mso-tstyle-colband-size:0;mso-style-noshow:yes;mso-style-priority:99;mso-style-qformat:yes;mso-style-parent:;mso-padding-alt:0in 5.4pt 0in 5.4pt;mso-para-margin:0in;mso-para-margin-bottom:.0001pt;mso-pagination:widow-orphan;font-size:11.0pt;font-family:Calibrisans-serif;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:Times New Roman;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;mso-bidi-font-family:Times New Roman;mso-bidi-theme-font:minor-bidi;} 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 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 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); } } }

Ankit Nandekar · Answer

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.