Date/ Time Functions in SQL- Part-1

Hello friends, we talked about different date formats in my earlier article, today I thought it would be a good topic to talk about Date/Time functions available in SQL Server 2008. There are more than 20 Date/Time functions available in SQL Server 2008, but we will talk only about those functions which are being used frequently.

GETDATE():-GETDATE function is the most frequently used function by the database developers. It has no parameters and it returns the date and time value of the system where SQL Server instance is running.

GETUTCDATE():-GETUTCDATE function has no parameters and it returns the date time value of the system where SQL Server instance is running, the only difference between GETDATE and GETUTCDATE is that GETUTCDATE returns the time as UTC time(Coordinated Universal Time).

Lets execute below SQL statement to see the results.

Select GETDATE(),GETUTCDATE()

DATEDIFF(Datepart,StartDate,EndDate):-DATEDIFF function take three parameters as input and returns the difference between the two dates in terms of date part.

Below are the valid Datepart values:-

DayàD, DD, Day

Monthà M, MM, Month

Yearà Year, Y, YY, YYYY

Weekà Week, WK

Minuteà Minute, MI

SecondàSecond, SS

Millisecond àMillisecond, MS

NanosecondàNanosecond, NS

StartDate: - This should be the date which comes earlier.

EndDate: - This should be the date which comes later.

Let’s execute below query to understand this.

Select DATEDIFF(Day,'30-June-2005','31-July-2005')

In the above screenshot, we can see as a result we are getting 31, which is correct. We have specified “Day” as date_part parameter, it means we want the difference between two dates in terms of days.

DATEADD(Date_Part,Number,Date):- DATEADD function takes three parameters as an input and returns the date after adding the number(specified in the input parameter) in date_part supplied as input parameter.

To understand how DATEADD function works, lets execute below query and see the result.

Select DATEADD(Day,10,'31-July-2005')

In the above screenshot, we can see that we passed “Day” as date_part value and 10 as Number value, it means, we are adding no of days into the date supplied. Date value supplied to the DATEADD function was “31 –July-2005” and as a result, we got 10^th Aug 2005 which is correct.

Keep reading this blog and keep posting your queries as a comments, I will definitely try to answer your questions