Tag Archives

2 Articles

MSSQL datediff in seconds

The following command on a Microsoft SQL Server calculates the difference in seconds between two datetimes:

DATEDIFF(SECOND, date1, date2)

Example query:

  SELECT 
	  date1,
	  date2,
	  DATEDIFF(SECOND, date1, date2) / 1000  as difference_in_seconds
  FROM [TEST].[dbo].[TestTableDatetime]

Result:

If you need the result as a text and also want to add 3 digits after the seconds you can use the following example:

SELECT 
	date1,
	date2,
	CONVERT(VARCHAR(12),  DATEDIFF(MILLISECOND, date1, date2) / 1000)  + ','   
	+ RIGHT('000' + CONVERT(VARCHAR(4), DATEDIFF(MILLISECOND, date1, date2) % 1000), 3) 
		as difference_in_seconds_as_text
FROM [TEST].[dbo].[TestTableDatetime]

Result:

C# useful DateTime extensions

Here are some useful C# DateTime extensions, i hope you enjoy it:

// <copyright file="DateTimeExtensions.cs" company="palow UG">
// Copyright (c) palow UG. All rights reserved.
// </copyright>

using System;

namespace Palow.Library.Extensions
{
    /// <summary>
    /// Extensions for DateTime.
    /// </summary>
    public static class DateTimeExtensions
    {
        /// <summary>
        /// Elasped seconds in comparsion to this DateTime.
        /// </summary>
        /// <param name="dt">This DateTime.</param>
        /// <returns>Timespan of elapsed seconds.</returns>
        public static TimeSpan Elapsed(this DateTime dt)
        {
            return DateTime.Now - dt;
        }

        /// <summary>
        /// Gets the first day of a month from DateTime.
        /// </summary>
        /// <param name="dt">DateTime.</param>
        /// <returns>First day of the month.</returns>
        public static DateTime FirstDayOfMonth(this DateTime dt)
        {
            return new DateTime(dt.Year, dt.Month, 1);
        }

        /// <summary>
        /// Gets the start of the day.
        /// </summary>
        /// <param name="dt">DateTime.</param>
        /// <returns>DateTime to start of day.</returns>
        public static DateTime ToStartOfDay(this DateTime dt)
        {
            return new DateTime(dt.Year, dt.Month, dt.Day, 0, 0, 0, 0);
        }

        /// <summary>
        /// Gets the start of the hour.
        /// </summary>
        /// <param name="dt">DateTime.</param>
        /// <returns>DateTime to start of hour.</returns>
        public static DateTime ToStartOfHour(this DateTime dt)
        {
            return new DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, 0, 0, 0);
        }

        /// <summary>
        /// Gets the last day of a month from DateTime.
        /// </summary>
        /// <param name="dt">DateTime.</param>
        /// <returns>Last day of the month.</returns>
        public static DateTime LastDayOfMonth(this DateTime dt)
        {
            return new DateTime(dt.Year, dt.Month, 1).AddMonths(1).AddDays(-1);
        }
    }
}

You can call it from any class like this little example, don’t forget to add the using in the top of you class.

            DateTime dateTime = DateTime.Now;
            var startOfDay = dateTime.ToStartOfDay();
            var firstDayOfMonth = dateTime.FirstDayOfMonth();
            var startOfHour = dateTime.ToStartOfHour();
            var lastDayOfMonth = dateTime.LastDayOfMonth();

Elapsed

The function Elapsed returns the time elapsed between now and the DateTime object. If you need a simple stopwatch, you can create a DateTime object, run your functions and use the elapsed function to show how long the operation takes. Be care it has not the accuracy as the stopwatch class, but for longer operations (> 500ms) you should be fine.

FirstDayOfMonth

The function first day of month returns an DateTime object from the given DateTime object from the start of the month. For example: 2018-04-03 converts to 2018-04-01.

ToStartOfDay

Its like the first day of month function, it returns the start of the day at 0:00

ToStartOfHour

Same functionality as start of day, it returns a new DateTime object to the start of the hour.

LastDayOfMonth

It returns from the given DateTime the last day in the month.