SQL Server: basic functions (part 4) - Dates

Tweet

Dates! Working with dates in Sql Server queries might be fun... or not... It's a fact that sooner or later everybody will end up dealing with date manipulation. Sql Server comes with different date functions which can help us through the process. In this article we are going to see:

• datepart
• day, month, year
• datename
• getdate
• dateadd
• datediff

Datepart

Datepart returns a part of a specified date (would you expect something different?). Use this function when you want to extract a part from a date. The syntax is:

DATEPART ( datepart , your_date )

The datepart parameter (and relative abbreviation) is taken from the following list:

• year (yy, yyyy)
• quarter (qq, q)
• month (mm, m)
• dayofyear (dy, y)
• day (dd, d)
• week (wk, ww)
• weekday (dw)
• hour (hh)
• minute (mi, n)
• second (ss, s)
• millisecond (ms)

** take a few seconds to understand the list because it is used in almost every function here explained **

As an example, you can extract the day part from a date with:

DATEPART(day, '03/12/2010') as Day_Part

The result will be:

3

The same will be if you use d instead of day as datepart parameter.

Day, Month, Year

Day, Month, Year are just synonyms for Datepart. Specifically:

Day is like Datepart(dd, your_date);
Month is like Datepart(mm, your_date);
Year is like Datepart(yy, your_date)

So the following two examples will give the same result (3):

DATEPART(day, '03/12/2010') as Day_Part_DatePart
DAY('03/12/2010') as Day_Part_Day

Simple as that! Now someone could argue why Sql Server has two identical functions. Good question! I leave the answer to your imagination.

Datename

The function returns a string which represent a part of a given date.

DATENAME ( datepart , your_date )

The datepart parameter is the same as in the Datepart function (see list above).

DATENAME(mm, '03/12/2010') as Month_Name

will return:

december

In case you want to get only the first 3 letters of the month (dec in our example), you could use the Left function:

LEFT(DATENAME(mm, '03/12/2010'), 3) as Month_Abbreviated

Getdate

The Getdate function is used to obtain the actual date:

GETDATE() as Today

will return the date and hour:

2010-12-03 05:55:38.763

That is exactly when I run the query.
If you use it in conjunction with Datepart, Datename, Day, Month or Year, you can obtain part of the actual date and hour.

Dateadd and Datediff

Dateadd is utilized to add a certain value to a date, while Datediff returns a value representing the "interval" between two datetimes.
Let's see them in action.

DATEADD ( datepart , number , your_date )

datepart is again a parameter from the above list (remember? I told you it was important!), number is a value used to increase your_date.

DATEADD(dd, 1, GETDATE()) as Tomorrow, GETDATE() as Today, DATEADD(dd, -1, GETDATE()) as Yesterday

If you use the above functions in a query you will get the dates for tomorrow, today and yesterday. For today we used the Getdate function, for tomorrow we added a day to today, for yesterday we subtracted a day to today.

Datediff has different parameters:

DATEDIFF ( datepart , startdate , enddate )

Again, datepart is the usual parameter, startdate indicates the starting date and enddate the ending date. Let's see an example which will clarify everything:

DATEDIFF(dd, '01/12/2010', '05/12/2010') as Days_Between_dates

will give the result:

4

which are the days between the two dates.

Please leave a comment if you wish.