El TecnoBaúl de Kiquenet

Kiquenet boring stories

Posts Tagged ‘datetime’

DateTime .NET and SQL Server

Posted by kiquenet en 19 septiembre 2014

DateTime Extensions




Date Formatting in WPF Datagrid

<tk:DataGridTextColumn Binding="{Binding StartDate, StringFormat=\{0:dd.MM.yy HH:mm:ss\}}" />

If you have some custom format for every culture then you better move that logic to Converter or property getter. Otherwise you can look into some more options with custom date formatting

You can do it by setting the AutoGeneratingColumn handler in XAML:

<DataGrid AutoGeneratingColumn="OnAutoGeneratingColumn"  ..etc.. />

And then in behind code do something like this:

private void OnAutoGeneratingColumn(object sender, DataGridAutoGeneratingColumnEventArgs e)
    if (e.PropertyType == typeof(System.DateTime))
        (e.Column as DataGridTextColumn).Binding.StringFormat = "dd/MM/yyyy";


Try setting/binding this as your Converter:

[ValueConversion(typeof(DateTime), typeof(String))]
public class DateConverter : IValueConverter
    private const string _format = "dd-MM-yy";

    public object Convert(object value, Type targetType, object parameter, CultureInfo culture)
        DateTime date = (DateTime)value;

        return date.ToString(_format);

    public object ConvertBack(object value, Type targetType, object parameter, CultureInfo culture)
        return DateTime.ParseExact((string) value, _format, culture);


Then set up your XAML like this:

    <wpfDataGridMisc:DateConverter x:Key="dateConverter"/>

<DataGridTextColumn Binding="{Binding Date, Converter={StaticResource dateConverter}}"/

SQL Server Date and Time


Convert Column from Date to Datetime Sql Server


ALTER TABLE Tab ALTER COLUMN LastModified datetime2 not null

(I chose datetime2 over datetime, since the former is recommended for all new development work http://msdn.microsoft.com/en-us/library/ms187819.aspx, and since the column is currently date, I know you’re on SQL Server 2008 or later)

SQL Server Management Studio I get the following error.

Saving changes is not permitted. The changes you have made require the following table to be dropped and re-created.

You can’t change the type of a column in place.  You need to create a new column, copy of the values over, and then drop the original column.

SQL Management Studio usually accomplishes this by creating a temporary table with the new column name, copying the values over, dropping the original table with the old column, and then renaming the new temporary table to the new name.  Often it does this without people even realizing it.

However, this can be a very invasive approach, especially if you already have a lot of rows in the table, so you may want to just write a SQL script add the new column to the table, copy the values over, drop the original column, and then use sp_rename to change the new temporary column name back to the original column name.  This is the same idea as what SQL Management Studio is doing, except they are dropping and recreating the whole table, and you are just dropping and recreating the column.

However, if you DO want to let SQL Manangement Studio do it this way, you can turn off that error message.  I believe it was originally added because people did not wantdrop and recreate the table by default.  To turn this message off, go to *Tools->Options-?Designers*, and uncheck the **"Prevent saving changes that require table re-creation"**, then you should be able to save your changes in the designer.

[Prevent saving changes that require table re-creation]


DECLARE @date1 date= ’11-21-38′;

DECLARE @date2 date= ’12-29-33 23:20:51′; — will take only the date 
SELECT @date1 AS ‘date1’, @date2 AS ‘date2’

DECLARE @smalldatetime1 smalldatetime = ‘1955-12-13 12:43:31’; — will round to next minute

DECLARE @smalldatetime2 smalldatetime = ‘1955-12-13 12:43:29’; — will not round to next minute 
SELECT @smalldatetime1 AS ‘@smalldatetime1’, @smalldatetime2 AS ‘@smalldatetime2’; 

DECLARE @datetime1 datetime = ’12-23-35′;

DECLARE @time1 time(4) = ’11:10:05.1234′;

DECLARE @datetime2 datetime = @time1  
DECLARE @datetime3 datetime = ‘1955-12-13 12:43:31’;

SELECT @datetime1 AS ‘datetime1’, @datetime2 AS ‘datetimevar2’  , @datetime3 AS ‘datetimevar3’ 

DECLARE @datetime2_1 datetime2(4) = ’12-13-25 12:32:10.1234′;

DECLARE @datetime2_2 datetime2 = ’12-13-25 11:32:10.1234567′;

DECLARE @datetime2_3 datetime2 = ’12-13-25′;  
SELECT @datetime2_1 AS ‘datetime2_1’, @datetime2_2 AS ‘datetime2_2’, @datetime2_3 AS ‘datetime2_3’;


Posted in .NET | Etiquetado: , , , , , , | Leave a Comment »