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:
- day, month, year
DatepartDatepart 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:
The datepart parameter (and relative abbreviation) is taken from the following list:
DATEPART ( datepart , your_date )
- 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)
As an example, you can extract the day part from a date with:
The result will be:
DATEPART(day, '03/12/2010') as Day_Part
The same will be if you use d instead of day as datepart parameter.
Day, Month, YearDay, 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):
Simple as that! Now someone could argue why Sql Server has two identical functions. Good question! I leave the answer to your imagination.
DATEPART(day, '03/12/2010') as Day_Part_DatePart
DAY('03/12/2010') as Day_Part_Day
DatenameThe function returns a string which represent a part of a given date.
The datepart parameter is the same as in the Datepart function (see list above).
DATENAME ( datepart , your_date )
DATENAME(mm, '03/12/2010') as Month_Name
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
GetdateThe Getdate function is used to obtain the actual date:
will return the date and hour:
GETDATE() as Today
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 DatediffDateadd 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.
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 ( datepart , number , your_date )
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.
DATEADD(dd, 1, GETDATE()) as Tomorrow, GETDATE() as Today, DATEADD(dd, -1, GETDATE()) as Yesterday
Datediff has different parameters:
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 ( datepart , startdate , enddate )
will give the result:
DATEDIFF(dd, '01/12/2010', '05/12/2010') as Days_Between_dates
which are the days between the two dates.
Please leave a comment if you wish.