How do I display dates in gridview column in format of mm/yyyy?

Question

As far as I found on net the statement

SELECT DATEPART(MONTH,columnName),DATEPART(YEAR,columnName)

doing it and listing the result in 2 columns. What I need is do display date from database (MS SQL) in single column in format like mm/yyyy.

Is there any way to display it in one column?

Can you clarify, your title says `mm-yyyy` but the question body says `mm/yyyy`. It's not a big difference but we want the answers to be accurate and to reflect what you're ultimately after. — Aaron Bertrand, Apr 23 '12 at 20:04
@Aron,It is mm/yyyy. I solved my problem with the following statement: SELECT RIGHT(CONVERT(VARCHAR(10),columnName,103),7)[DATE1] FROM tableName. My project requirement was to display date column stored in MS SQL by sending date as a parameter. So I created combobox and stored dates as a string in format dd/MM/yyyy. With query I've wrote above and dates populated in this format I can retrieve result from database in my application, but I am not able to do it if I send date as a parameter in format MM/yyyy. I am confused about that,since I retrieve data from sql server in desired format? — black12, Apr 23 '12 at 20:15
You need to separate presentation from data. For example you should be displaying the date in whatever regional format makes sense, but you should be sending safe and unambiguous dates (e.g. YYYYMMDD) to SQL Server. Surely your combobox control can help you dictate the format of the string you send back to SQL Server... — Aaron Bertrand, Apr 23 '12 at 20:21

Tim Schmelter · Answer 1 · 2012-04-23T18:04:23.420

1

Since you've asked explicitely for a GridView, it's good practise to format a datetime at the very last place. So i would recommend to use a .NET method as String.Format or ToString:

String formatted = date.ToString("MM-yyyy");

Or with a BoundField's DataFormatString:

<asp:GridView ID="GridView1" runat="server">
    <Columns>
       <asp:BoundField DataFormatString="{0:MM-yyyy}" DataField="DateField" HeaderText="Date" ReadOnly="True" />
    </Columns>
</asp:GridView>

edited Apr 23 '12 at 18:04

answered Apr 23 '12 at 16:12

Tim Schmelter

450,073
74
686
939

Is it `mm` or `MM`? In SQL Server 2012's version of `FORMAT()` (which is derived from the .NET version), `mm` gives me minutes, not months. – Aaron Bertrand Apr 23 '12 at 16:15
@AaronBertrand: As i've noted, i wouldn't use the dbms to format or convert a datetime field at all. Hence above is C#. Perhaps he needs the datetime at another place in the program so you would often need to convert the string. – Tim Schmelter Apr 23 '12 at 16:19
1

But in C# is `mm` really month number and not minutes? I understand your objection to formatting in the database, but sometimes it is appropriate (e.g. think multiple consumers of the same query, written in different languages and maintained by different teams). – Aaron Bertrand Apr 23 '12 at 16:21
Yes, `DateTime.Now.ToString("mm-yyyy")` always returns `04-2012` independent on the client. Even if you want to format the datetime according to the user's language it would be better to store the user's culture and format it on serverside by providing that culture info. – Tim Schmelter Apr 23 '12 at 17:40
I'm asking because SQL Server 2012 added `FORMAT()` so you can use the same formatting rules within the database, including culture (I've already used this in a case where the requirement was that culture was specified by a user's settings, set in the database, not based on the web browser settings). And inside SQL Server it treats `mm` (minutes) and `MM` (month) differently. I'm surprised that `mm` in C# behaves differently than `mm` within SQL Server. – Aaron Bertrand Apr 23 '12 at 17:44
1

@AaronBertrand: Actually you're right. I was sure that i've tested above, maybe at 19:04;) So `DateTime.Now.ToString("mm-yyyy")` actually returns _minute-2012_. Edited my answer accordingly. Thanks. – Tim Schmelter Apr 23 '12 at 18:05

Aaron Bertrand · Answer 2 · 2012-04-23T20:12:53.670

1

SELECT RIGHT('0' + CONVERT(VARCHAR(2), MONTH(columnName)), 2)
  + '/' + CONVERT(CHAR(4), YEAR(columnName))
FROM dbo.tableName;

You can also say something like:

SELECT RIGHT(CONVERT(CHAR(10), columnName, 103), 7)
  FROM dbo.tableName;

In SQL Server 2012 you will be able to say:

SELECT FORMAT(columnName, 'MM/yyyy') FROM dbo.table;

edited Apr 23 '12 at 20:12

answered Apr 23 '12 at 16:13

Aaron Bertrand

272,866
37
466
490

score 0 · Answer 3 · answered Apr 23 '12 at 16:18

Hey you can set format in Gridview as like below

<asp:TemplateField >
     <ItemTemplate>
             <asp:Label Text='<%# Convert.ToDateTime(DataBinder.Eval(Container.DataItem,  "Created_Date ")).ToString("mm-yyyy")%>'
   ID="lblCreatedDate" runat="server"></asp:Label>
    </ItemTemplate>
 </asp:TemplateField>

How do I display dates in gridview column in format of mm/yyyy?

3 Answers3