none
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?

    Saturday, March 11, 2006 3:11 AM

Answers

  • 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).

    Sunday, March 12, 2006 3:56 AM

All replies

  • 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).

    Sunday, March 12, 2006 3:56 AM
  • 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

    • Proposed as answer by GoldenGoose Wednesday, May 13, 2009 1:56 PM
    Monday, September 25, 2006 5:44 AM
  • Thanx for the examples! It helped a lot.

    Sunday, April 27, 2008 1:34 PM
  • 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.

    Thursday, June 26, 2008 3:06 AM
  •  

    Thanks very much for this. Great!!
    Wednesday, October 15, 2008 9:28 AM
  • 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(); 
     
            } 
        } 

           
    • Proposed as answer by X-Sharkk Thursday, February 26, 2009 5:56 PM
    Thursday, February 26, 2009 5:50 PM
  • 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

     

     

    Thursday, April 15, 2010 8:41 AM
  • 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!

     

    • Proposed as answer by Webmonkeymon Wednesday, March 02, 2011 9:18 AM
    Wednesday, March 02, 2011 9:18 AM