Friday, 3 December 2010

SQL Server: basic functions (part 4) - Dates


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 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:
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.

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:
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

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:
which are the days between the two dates.

Please leave a comment if you wish.

0 thoughts:

Post a Comment

Comments are moderated. I apologize if I don't publish comments immediately.

However, I do answer to all the comments.