Calculating with dates in SQL Server 2005 can in different ways. SQL Server 2005 has several features that you can use when you start working with dates. This article describes a number of practical examples that you practice you will encounter when working with databases and dates. For example describes how you can count back in time, how the difference between various dates in days and how you can calculate the month, determine a date.
DateAdd ()
This feature you use to determine a date before or after the specified date lies. For example, you know the date that is two months from today is whether you want to know the date which is two days before August 1.
This feature you use to determine a date before or after the specified date lies. For example, you know the date that is two months from today is whether you want to know the date which is two days before August 1.
Syntax
DateAdd (date, area, number, date, value)
Examples
DateAdd (date, area, number, date, value)
Examples
The date of the next day
DATEADD ( dd, 2 , GETDATE ( ) ) SELECT DateAdd (dd, 2, GETDATE ())
In the above example, we say that he must count days (dd), 2 indicates that he must count 2 days ahead. The third argument specifies the date when he must rely, in this case today (GETDATE ()). By GETGATE () you can give a date, eg '2008-12-05 '. Note that this must be between quotes!
The date of two months ago
DATEADD ( mm, -2 , GETDATE ( ) ) SELECT DateAdd (mm, -2, GETDATE ())
mm represents months. -2 Indicates that he must go back two months and GETDATE () returns the date of today.
Below is a table of values you can enter as first argument:
PART DATE ()
With this feature allows you to days, months, years time and determine a date value. You want some months of the date 05/12/2008 have, in this case 12. You also need the date of August 12, 2007, 12 So.
Syntax
Part Date (date portion, Date value)
Examples
Today
The number of years now
Below is a table of values you can enter as first argument:
DateDiff ()
Use datediff to calculate an interval between two dates. So you can determine that the number of days between two dates is.
Syntax
DateDiff (date portion, datumwaarde1, datumwaarde2)
Examples
The number of days difference between today and December 1, 2008 (December 5, 2008)
Below is a table of values you can enter as first argument:
NAME DATE ()
Date Name is very similar to dating part. The difference is that this function returns a string (some string) and returns a dating part number. This feature allows the day of the week in text or return the name of the month.
PART DATE ()
With this feature allows you to days, months, years time and determine a date value. You want some months of the date 05/12/2008 have, in this case 12. You also need the date of August 12, 2007, 12 So.
Syntax
Part Date (date portion, Date value)
Examples
Today
DATEPART ( dd, GETDATE ( ) ) PART SELECT DATE (dd, GETDATE ())
The number of years now
DATEPART ( yy, GETDATE ( ) ) PART SELECT DATE (yy, GETDATE ())
Below is a table of values you can enter as first argument:
DateDiff ()
Use datediff to calculate an interval between two dates. So you can determine that the number of days between two dates is.
Syntax
DateDiff (date portion, datumwaarde1, datumwaarde2)
Examples
The number of days difference between today and December 1, 2008 (December 5, 2008)
DATEDIFF ( dd, '2008-12-01' , GETDATE ( ) ) SELECT DateDiff (dd, '2008-12-1 ', GETDATE ())
Below is a table of values you can enter as first argument:
NAME DATE ()
Date Name is very similar to dating part. The difference is that this function returns a string (some string) and returns a dating part number. This feature allows the day of the week in text or return the name of the month.
Syntax
Name Date (date portion, Date value)
Examples
The day today
The day number of the year today
Below is a table of values you can enter as first argument:
A limitation of the function that the function returns the English names. Therefore I recommend to always use a date dimension. The big advantage of a date dimension is that you know for any date in any week he is, what year, what quarter, etc. In addition you should keep in mind that January 1 in week 52 of last year could fall. The year of January 1 in this case for example in 2008 but the year of the week number is 2007.
Below is an example of a date dimension.
Name Date (date portion, Date value)
Examples
The day today
DATENAME ( dw, GETDATE ( ) ) SELECT NAME DATE (dw, GETDATE ())
The day number of the year today
DATENAME ( dy, GETDATE ( ) ) SELECT NAME DATE (dy, GETDATE ())
Below is a table of values you can enter as first argument:
A limitation of the function that the function returns the English names. Therefore I recommend to always use a date dimension. The big advantage of a date dimension is that you know for any date in any week he is, what year, what quarter, etc. In addition you should keep in mind that January 1 in week 52 of last year could fall. The year of January 1 in this case for example in 2008 but the year of the week number is 2007.
Below is an example of a date dimension.
Tags 
