C# converts T-SQL date datatype to DateTime datatype by default

Question

When transfering a table from a database to a datagrid (WPF), the DATE datatype from T-SQL is converted to DateTime, but I don't want that. I want only the .Date part to show up in my datagrid.

Here's the piece of code in c#:

cmd = new SqlCommand("SELECT * FROM bibliotvguia.get_progtv();", con);

adap = new SqlDataAdapter(cmd);

dt = new DataTable();
adap.Fill(dt);
programs_datagrid.ItemsSource = dt.DefaultView;

And the XAML piece of code:

<DataGrid SelectionMode="Single" Name="programs_datagrid" Height="340"
                      AutoGenerateColumns="False" RowHeaderWidth="0" IsReadOnly="True" CanUserResizeColumns="True"
                      CanUserReorderColumns="False">
                    <DataGrid.Columns >
                        <DataGridTextColumn Header="Canal de TV" Binding="{Binding canal_media}"></DataGridTextColumn>
                        <DataGridTextColumn Header="Dia" Binding="{Binding dia_media}"></DataGridTextColumn>
                        <DataGridTextColumn Header="Hora" Binding="{Binding hora_media}"></DataGridTextColumn>
                        <DataGridTextColumn Header="Produção Audiovisual" Binding="{Binding titulo}"></DataGridTextColumn>
                    </DataGrid.Columns>
                </DataGrid>

The dia_media column is the one that has a DateTime type.

Thanks for your help.

The best for you.

Instead of select *, select all the fields but the date one. For that, use the convert function to put it into a string in the format you want. — Dan Bracuk
– Dan Bracuk, Commented May 31, 2014 at 12:58
DATE column type is for datetimes. Why don't you want this? If you want to show only .Date property, then you can bound their .Date properties to your datagrid. Of you can use "d" standart pattern. What is bibliotvguia.get_progtv() for exactly by the way? — Soner Gönül
– Soner Gönül, Commented May 31, 2014 at 12:59
Given the time part is going to be zero, why do you care? Having it there is far less of an issue than storing dates as strings which is a baaaaaaaaaaaaad idea anytime. — Tony Hopkinson
– Tony Hopkinson, Commented May 31, 2014 at 12:59
Soner, the get_progtv() is an UDF to extract a table from my schema — amportugal
– amportugal, Commented May 31, 2014 at 13:03

Steve · Accepted Answer · 2014-05-31 13:09:45Z

4

There is only the DateTime type in NET, so you can't really have a variable with only the Date part. This kind of problem should not be resolved changing the storage of your datetime values or inserting complicate SQL function to extract the value as string or to remove the time part.

It is only just a matter on how do you format your data for presentation.

I suppose that you need something like this in your XAML

  <DataGridTextColumn Header="Dia" Binding="{Binding dia_media, 
                                 StringFormat=dd MM yyyy}"/>

edited May 31, 2014 at 13:09

answered May 31, 2014 at 13:04

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

amportugal Over a year ago

It worked but there's some aditional characters ()dd-mm-yyyy

Tony Hopkinson Over a year ago

Just change string format to dd-MM-yyyy then. You didn't specify the output format, so Steve's guess is as good as any.

Andrei · Accepted Answer · 2014-05-31 13:23:50Z

-1

You can use a converter in your binding

XAML

       <Window.Resources>
         <locl:DateTimeConverter x:Key="converter"/>
       </Window.Resources>
     ...
        <DataGrid ItemsSource="{Binding Dates}">
            <DataGrid.Columns>
                <DataGridTextColumn Header="Date" 
                                    Binding="{ Binding DateProperty,
                                               Converter={ StaticResource converter}}"/>
            </DataGrid.Columns>
        </DataGrid>

The converter class:

 public class DateTimeConverter:IValueConverter
    {
        public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
        {
            DateTime dateValue = (DateTime)value;

            return dateValue.ToString("yyyy/MM/dd/");
        }

        public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
        {
            throw new NotImplementedException();
        }
    }

and the result :

enter image description here

I hope it helps

answered May 31, 2014 at 13:23

Andrei

3413 silver badges12 bronze badges

3 Comments

Tony Hopkinson Over a year ago

Wasn't me who marked you down, but converting dates to strings is the last thing you do to them. Any chance you might want to convert them back to a date is a potential bugfest.

Andrei Over a year ago

I thought he just wanted to present the date in that format. Steve's answer is better for the scenario you describe. What I wanted to do is just offer an alternative means to customize the way data can be presented using converters

Tony Hopkinson Over a year ago

I didn't figure you were going out of your way to lead the OP wrong mate, but have a think on this. Once you convert a date to a string, "This is not a date" is a valid entry in the "Date" column in the grid...

Collectives™ on Stack Overflow

C# converts T-SQL date datatype to DateTime datatype by default

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related