DataFormatString for DateTime column

Question

I have a GridView with a sortable column bound to a SQL Server datetime field.  I want to display the dates as month abbreviation and two digit day.  For example, I have a datetime of

2/24/2006 3:25:45 PM

and what I want to display instead is

Feb 24

or if 2/24/2006 were TODAY

3:25pm

Any suggestions?

Answer 1

A few ideas for you. 

#1. You could create a caculated column in your SQL statement to return these values, for example:

     select convert(varchar(max),getdate(),107) as calculatedDate

See "cast and convert" help topic in MSDN for formats allowed.  With this you could have your normal date column plus this calculatedDate used for display purposes.

#2. You could twiggle this with a client side function.  Add a handler for the CellFormatting event for the DataGridView (this is how you do it, google around for CellFormatting for examples).

Answer 2

I had the same question - found this post - read the answer given - thought - that sounds too hard - surely it is easier than that? (bare in mind I am talking about .NET 2)

In GridView properties I played around a little and set the DateFormatString to the following (which worked)

DataFormatString examples:

{0:dd MMMM yyyy}    -    gives 24 February 2006
{0:MMM dd}    -    gives Feb 24 (substitue MMM with MMMM for the full month name instead of abbreviation)
{0:dd/MM/yy}    -    gives 24/02/06
{0:dd/MM/yyyy}    -    gives 24/02/2006

Hope it helps,

Noeleo

Answer 3

Thanx for the examples! It helped a lot.

Answer 4

Thank you for this.  The book I have was completely erroneous on how to set the DataFormatString.  This works.  And you can change it around to get the exact format you want.

Answer 5

 

Thanks very much for this. Great!!

Answer 6

Try This out. . . Remember to update cell numbers and gridview id

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)

{

if (e.Row.RowType == DataControlRowType.DataRow)

{

string Date = e.Row.Cells[5].Text;

DateTime dt = new DateTime();

DateTime.TryParse(Date, out dt);

if (dt.Date == DateTime.Now.Date)

e.Row.Cells[5].Text = dt.ToShortTimeString();

else

e.Row.Cells[5].Text = dt.ToShortDateString();

}

}

       

Answer 7

Friends i also have same type of question but related with DAtabase Search

 

My Search Date sql Query is not working plz help any body.

i set my Date formate dd/mm/yyyy in control panel, and in Access i set coulumns field as shortDate,

 

I used Folowing Sql Quries but non of them works

 

[CODE]

 

PurchaseSql = "Select * from PUrchase where PurchaseDate => '" & DatePurchaseFrm.Value.ToString("dd-MM-yyyy") & "' order By PUrchaseID"

 

       PurchaseSql = "Select * from PUrchase where PurchaseDate =@05/04/2010 order By PUrchaseID"

 

       PurchaseSql = "SELECT * FROM purchase WHERE PUrchaseDate between '" & Format(Me.DatePurchaseFrm.Value, "dd/dd/yyyy") & "' AND '" & Format(Me.DatePurchaseTo.Value, "dd/MM/yyyy") & "'"

 

       PurchaseSql = "Select * from PUrchase where PurchaseDate ='" & DatePurchaseFrm.Value & "' order By PUrchaseID"

 

       PurchaseSql = "Select * from PUrchase where CONVERT(CHAR(10),PurchaseDate) =>" & DatePurchaseFrm.Value.ToString("dd-mm-yyyy") & " order By PUrchaseID"

 

       PurchaseSql = "SELECT * FROM Purchase WHERE PurchaseDate>= DateValue('" & DatePurchaseFrm.Value & "')" & " ORDER BY PurchaseID"

 

       PurchaseSql = "select * from Purchase"

 

'yes i also used rowfilter function as

 

PurchaseDt.DefaultView.RowFilter = "PurchaseDate >= DateValue('" & DatePurchaseFrm.Value & "')"

 

[/CODE]

 

' I think the problem is that the DateTime Field in access also add Time with Date, and i only want to search Date, so how to search only date

for further reference i m also attaching file, plz Help to solve

 

Please Find attachment at following link, (at end of message)

http://www.daniweb.com/forums/thread276115.html

 

 

Answer 8

this worked like a charm!  this code removed the seconds and added am and pm from a sql datetime field.  making my  start time 10:00 am  end time  1:30 pm 

 just what i needed.  

thanks!