5.6. DATE Functions
Using the DATETIME and SMALLDATETIME data type, SQL Server
gives you the opportunity to use several date functions like DAY, MONTH,
YEAR, DATEADD, DATEDIFF, DATEPART, and GEtdATE for
extracting and manipulating dates (adding dates, taking the differences between dates, finding the
day/month/year from dates, and so on).
Before we start discussing date functions, we will create a table,
DateTable, using the SMALLDATETIME data type. Then we will discuss date formats
and formatting dates.
5.6.1. Creating a Table with the DATETIME Data Type
Suppose that you define SMALLDATETIME types in a table like this:
CREATE TABLE DateTable (birthdate SMALLDATETIME,
school_date SMALLDATETIME,
names VARCHAR(20))
Data can now be entered into the birthdate and school_date columns, which
are both SMALLDATETIME columns, and into the names column. Inserting dates is
usually done by using an implicit conversion of character strings to dates. Following would be an
example of an INSERT into DateTable:
INSERT INTO DateTable
VALUES ('10-oct-01', '12/01/2006', 'Mala Sinha')
You will get:
(1 row(s) affected)
Note that single quotes are required around date values. As SMALLDATETIME is
not really a character column, the character strings representing date are implicitly converted
provided that the character string is in a form recognizable by SQL Server.
Now if you type:
SELECT *
FROM DateTable
The following appears in the DateTable table:
birthdate school_date names
--------------------- ----------------------- --------------------
2001-10-10 00:00:00 2006-12-01 00:00:00 Mala Sinha
(1 row(s) affected)
The DateTable table has not been created for you. Create it and insert the
following data into it:
birthdate school_date names
----------------------- ----------------------- ------------------
2001-10-10 00:00:00 2006-12-01 00:00:00 Mala Sinha
2002-02-02 00:00:00 2006-03-02 00:00:00 Mary Spencer
2002-10-02 00:00:00 2005-02-04 00:00:00 Bill Cox
1998-12-29 00:00:00 2004-05-05 00:00:00 Jamie Runner
1999-06-16 00:00:00 2003-03-03 00:00:00 Seema Kapoor
(5 row(s) affected)
5.6.2. Default Date Formats and Changing Date Formats
By default, SQL Server reads and displays the dates in the yyyy/mm/dd format. We can change the format in which SQL Server reads in dates by using SET DATEFORMAT. DATEFORMAT
controls only how SQL Server interprets date constants that are entered by you, but does not
control how date values are displayed. For example, to have SQL Server first read the day, then
month, and then year, we would type:
SET DATEFORMAT dmy
SELECT 'Format is yyyy/mon/dd' = CONVERT(datetime, '10/2/2003')
And we will get:
Format is yyyy/mon/dd
-----------------------
2003-02-10 00:00:00.000
(1 row(s) affected)
In SQL Server , if incorrect dates are used, we will get an out-of-range error.
For example, if we tried to do the following insert with the 32nd day of a month:
INSERT INTO DateTable
VALUES ('10-oct-01', '32/01/2006', 'Mita Sinha')
We would get the following error message:
Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-
range smalldatetime value.
The statement has been terminated.
In SQL Server , if two-digit year dates are entered, SQL Server's default
behavior is to interpret the year as 19yy if the value is greater
than or equal to 50 and as 20yy if the value is less than 50.
5.6.3. Date Functions
In this section we discuss some useful SQL Server date
functions--DATEADD,
DATEDIFF, DATEPART, YEAR, MONTH, DAY, and
GEtdATE.
5.6.3.1. The DATEADD function
The DATEADD function produces a date by adding a specified number to a
specified part of a date.
 |
The date parts are: dd for day, mm for
month, and yy for year.
|
|
The format for the DATEADD function is:
DATEADD(datepart, number, date_field)
datepart would be either dd, mm, or yy.
number would be the number that you want to add to the datepart.
date_field would be the date field that you want to add to.
For example, to add 2 days to the birthdate of every person in
DateTable we would type:
SELECT names, 'Add 2 days to birthday' = DATEADD(dd, 2, birthdate)
FROM Datetable
This query would give:
names Add 2 days to birthday
-------------------- -----------------------
Mala Sinha 2001-10-12 00:00:00
Mary Spencer 2002-02-04 00:00:00
Bill Cox 2002-10-04 00:00:00
Jamie Runner 1998-12-31 00:00:00
Seema Kapoor 1999-06-18 00:00:00
(5 row(s) affected)
You can also subtract two days from the birthdate of every person in
DateTable by adding a -2 (minus or negative 2) instead of a positive 2, as shown by the
following query:
SELECT names, 'Add 2 days to birthday' = DATEADD(dd, -2, birthdate)
FROM Datetable
This query would give:
names Add 2 days to birthday
-------------------- -----------------------
Mala Sinha 2001-10-08 00:00:00
Mary Spencer 2002-01-31 00:00:00
Bill Cox 2002-09-30 00:00:00
Jamie Runner 1998-12-27 00:00:00
Seema Kapoor 1999-06-14 00:00:00
(5 row(s) affected)
5.6.3.2. The DATEDIFF function
The DATEDIFF function returns the difference between two parts of a date.
The format for the DATEDIFF function is:
DATEDIFF(datepart, date_field1, date_field2)
Here again, datepart would be either dd, mm, or
yy. And, date_field1 and date_field2 would be the two date
fields that you want to find the difference between.
For example, to find the number of months between the two fields, birthdate and
school_date of every person in DateTable, we would type:
SELECT names, 'Months between birth date and school date' = DATEDIFF(mm, birthdate,
school_date)
FROM Datetable
This query would give:
names Months between birth date and school date
-------------------- -----------------------------------------
Mala Sinha 62
Mary Spencer 49
Bill Cox 28
Jamie Runner 65
Seema Kapoor 45
(5 row(s) affected)
5.6.3.3. The DATEPART function
The DATEPART function returns the specified part of the date requested. The
format for the DATEPART function is:
DATEPART(datepart, date_field)
Here too, datepart would be either dd, mm, or
yy. And, date_field would be the date field that you want to request the
dd, mm, or yy from.
For example, to find year from the birthdate of every person in
DateTable we would type:
SELECT names, 'YEARS' = DATEPART(yy, birthdate)
FROM Datetable
This query would give:
names YEARS
-------------------- -----------
Mala Sinha 2001
Mary Spencer 2002
Bill Cox 2002
Jamie Runner 1998
Seema Kapoor 1999
(5 row(s) affected)
5.6.3.4. The YEAR function
The YEAR(column) function will extract the year from a value stored as a
SMALLDATETIME data type. For example, to extract the year from the school_date
column of every person in DateTable, type:
SELECT names, YEAR(school_date) AS [Kindergarten Year]
FROM Datetable
This query produces the following output:
names Kindergarten Year
-------------------- -----------------
Mala Sinha 2006
Mary Spencer 2006
Bill Cox 2005
Jamie Runner 2004
Seema Kapoor 2003
(5 row(s) affected)
We can also use the YEAR function in date calculations. For example, if you
want to find the number of years between when a child was born (birthdate) and when the
child went to kindergarten (the school_date column) from DateTable, type the
following query:
SELECT names, YEAR(school_date)-YEAR(birthdate) AS [Age in Kindergarten]
FROM DateTable
This query produces the following output:
names Age in Kindergarten
-------------------- -------------------
Mala Sinha 5
Mary Spencer 4
Bill Cox 3
Jamie Runner 6
Seema Kapoor 4
(5 row(s) affected)
Here, the YEAR(birthdate) was subtracted from YEAR(school_date).
5.6.3.5. The MONTH function
The MONTH function will extract the month from a date. Then, to add six
months to the birth month of every person in DateTable, we can first extract the month by
MONTH(birthdate), and then add six to it, as shown here:
SELECT names, birthdate, MONTH(birthdate) AS [Birth Month], ((MONTH(birthdate)) + 6 )
AS [Sixth month]
FROM DateTable
This query produces the following output:
names birthdate Birth Month Sixth month
------------------ ----------------------- ----------- -----------
Mala Sinha 2001-10-10 00:00:00 10 16
Mary Spencer 2002-02-02 00:00:00 2 8
Bill Cox 2002-10-02 00:00:00 10 16
Jamie Runner 1998-12-29 00:00:00 12 18
Seema Kapoor 1999-06-16 00:00:00 6 12
(5 row(s) affected)
5.6.3.6. The DAY function
The DAY function extracts the day of the month from a date. For example, to
find the day from the birthdate of every person in DateTable, type the following
query:
SELECT names, birthdate, DAY([birthdate]) AS [Date]
FROM DateTable
which produces the following output:
names birthdate Date
-------------------- ----------------------- -----------
Mala Sinha 2001-10-10 00:00:00 10
Mary Spencer 2002-02-02 00:00:00 2
Bill Cox 2002-10-02 00:00:00 2
Jamie Runner 1998-12-29 00:00:00 29
Seema Kapoor 1999-06-16 00:00:00 16
(5 row(s) affected)
5.6.3.7. The GETDATE function
The GEtdATE function returns the current system date and time.
For example:
SELECT 'Today ' = GETDATE( )
will give:
Today
-----------------------
2006-01-17 23:17:52.340
(1 row(s) affected)
To find the number of years since everyone's birthdate entered in our
Datetable, and the current date, we could type:
SELECT names, 'Number of years ' = DATEDIFF(yy, birthdate, GETDATE( ))
FROM Datetable
This query will give us:
names Number of years
-------------------- ----------------
Mala Sinha 5
Mary Spencer 4
Bill Cox 4
Jamie Runner 8
Seema Kapoor 7
(5 row(s) affected)
5.6.3.8. Inserting the current date and time
Using the GETDATE( ) function, we can insert or update the current date and
time into a column. To illustrate this, we will add a new record (row) to our DateTable,
inserting the current date and time into the birthdate column of this row using the
GETDATE( ) function, and then add five years to the current date for the
school_date column of this new row. So type:
INSERT INTO DateTable
VALUES (GETDATE(), GETDATE( )+YEAR(5), 'Piyali Saha')
Then type:
SELECT *
FROM DateTable
This query produces the following output (note the insertion of the sixth row):
birthdate school_date names
--------------------- --------------------- ------------------
2001-10-10 00:00:00 2006-12-01 00:00:00 Mala Sinha
2002-02-02 00:00:00 2006-03-02 00:00:00 Mary Spencer
2002-10-02 00:00:00 2005-02-04 00:00:00 Bill Cox
1998-12-29 00:00:00 2004-05-05 00:00:00 Jamie Runner
1999-06-16 00:00:00 2003-03-03 00:00:00 Seema Kapoor
2006-01-17 23:19:00 2011-04-01 23:19:00 Piyali Saha
(6 row(s) affected)
|
