Dailycode.info

Short solution for short problems

MSSQL: convert datetime to time

I needed to show the time between a start and end datetime in one record in the DB. This could be done using the CONVERT function:

CONVERT(char(10),  EndTime - StartTime, 114) as TotalTime

Then the result looked like this:



.Net: Parse 24 hour data time from utc to central europe time

While I was handling time format differences, I noticed different things. For example, if the time or date comes in as 1/1/2016 1:1:1.

Then if you specified 'dd/MM:yyyy hh:mm:ss' it will not work. You will get an exception because the time format is not known. Change the format to 'd/m/yyyy h:m:s' and it will work. 

Just one problem still, of you get a time above 12, eg 1/1/2016 13:1:1 it will again fail saying time format not known. Because of the 'h' as hour format. Changing this into capital 'H' will handle the 24 hour format.

So my code for a deserialization of a property looks like this:

[OnDeserialized]

void OnDeserializing(StreamingContext context)

{

    if (this.SyncTimestampString == null)

        this.SyncTimestamp = null;

    else

    {

        try

        {

            //2/03/2016 15:40:47

            var result = DateTime.ParseExact(this.SyncTimestampString, "d/M/yyyy H:m:s", CultureInfo.InvariantCulture);

            TimeZoneInfo cstZone = TimeZoneInfo.FindSystemTimeZoneById("Central European Standard Time");

            this.SyncTimestamp = TimeZoneInfo.ConvertTimeFromUtc(result, cstZone);

        }

        catch (Exception er)

        {

            this.SyncTimestamp = null;

            LoggingComponent.LogError(this.ToString(), "SyncTimestampStringToSyncTime", er);

        }

    }

}

 



.Net WebAPI: add time zone suffix to datetime in JSON

On iOS, my JSON formatter needed to have the UTC format for date times. But My .Net WebAPI by default just formatted the date like this:

"timeStamp":"2015-01-04T00:00:00"

now just by adding these lines to the WebApiConfig.cs:

var json = GlobalConfiguration.Configuration.Formatters.JsonFormatter;

json.SerializerSettings.DateTimeZoneHandling = Newtonsoft.Json.DateTimeZoneHandling.Utc;

the date times got the correct format:

"timeStamp":"2015-01-04T00:00:00Z"