SqlTutorial-1 : Lesson-11 : Class-4 Microsoft SQL Server Training: Free Online-Learning Classes (OLC) for T-SQL Built-in Sql functions: Date functions

INDEX

1. Getting current Date and Time

• CURRENT_TIMESTAMP
• GETDATE()
• GETUTCDATE()

2. Extracting a part of the date.

• DATENAME(datepart, date)
• DATEPART(datepart, date)
• DAY(date), MONTH(date) and YEAR(date)

3. Finding time differences.

• DATEDIFF(datepart, starddate, enddate)

4. Modifying Date values.

• DATEADD(datepart, number, date)

5. Validating Date

• ISDATE(expression)

I-Getting Date and Time

1. CURRENT_TIMESTAMP:

Returns current date and time.

Syntax:

CURRENT_TIMESTAMP

Examples:

1. Getting Current LongDateTime

   SELECT CURRENT_TIMESTAMP  => 2008-08-17 13:20:57.503

2. Validating date

   CREATE TABLE MyTable (
    My_date Datetime NULL
           CHECK (my_date >=CURRENT_TIMESTAMP)
   )

3. Providing todays date and time as default value for a column

   CREATE TABLE TEMP(createtime datetime DEFAULT CURRENT_TIMESTAMP)

2. GETDATE() :

Returns the current system date and time in the SQL Server 2005 standard internal format for datetime values.

Syntax:

GETDATE();

Examples:

1. Getting Current Datetime

   SELECT GETDATE()  => 2009-8-17 13:13:00:710

2. Show to-days date and time in readable format

   SELECT CONVERT(varchar, GETDATE(),113)
   =>  17 Aug 2009 13:13:00:710

3.GETUTCDATE():

Returns GMT(Greenwich Mean Time) or Universal Time Coordinate(UTC) time.

Syntax:

GETUTCDATE();

Examples:

1. Getting Universal time or GMT-Time

   SELECT GETUTCDATE()

II-Extracting a part of the date.

1.DATENAME() :

Returns string/readable format of date part.

Syntax:

DATENAME ( datepart , date )

Table: DATE-Part

First Argument / Date part	Description	Value to be added /2nd parameter.
YY or Year	Year	1753 to 9999
QQ or Quarter	Quarter	1 to 4
MM or M	Month	1 to 12
DY or Y	Day of Year	1 to 366
DD or D	Day of month	1 to 31
WK/WW	Week of year	1 to 53
DW or Weekday	Week day	1 to 7 or Sun. to Sat.
HH or Hour	Hour	0 to 23 (military time)
MI or Minute	Minute	0 to 59
SS or Second	Second	0 to 59
MS or Millisecond	Millisecond	0 to 999

Examples:

1. Select DATENAME(DW,"10/01/97") => Wednesday
2. Select DATENAME(day, '2002-09-30 11:35:00') => 30
3. Select DATENAME(month, '2002-09-30 11:35:00') => September
4. Select DATENAME(year, '2002-09-30 11:35:00') =>2002
5. Select DATENAME(weekday, '2002-09-30 11:35:00') => Monday
6. Select DATENAME(week, '2002-09-30 11:35:00') => 40
7. Select DATENAME(dayofyear, '2002-09-30 11:35:00') =>273
8. Select DATENAME(minute, '2002-09-30 11:35:00') => 35

2. DARTPART():

Returns part of a date as an integer value. It is similar to DATENAME() except returns integer instead of readable string.

Syntax:

DATEPART(datepart,date)

// the datepart is specified in above table.

The functions DAY(), MONTH(); YEAR() are synonyms for DATEPART(dd,date), DATEPART(mm,date) and DATEPART(yy,date) respectively.

1. Extracting Month from given date

   Select DATEPART(MM, 'Oct 31 1997') => 10

2. Extracting Day from given date

   Select DATEPART(day, '2002-09-30 11:35:00') => 30

3. Extracting Month from given date

   Select DATEPART(month, '2002-09-30 11:35:00') => 09

4. Extracting year from Given date

   Select DATEPART(year, '2002-09-30 11:35:00') => 2002

5. Determining Quarter (Year/4) for given date

   Select DATEPART(quarter, '2002-09-30 11:35:00') => 3

6. Determining day of year i.e., 1-366

   Select DATEPART(dayofyear, '2002-09-30 11:35:00')=> 273

7. Extracting Day of Month (1-30) from given date

   SELECT DATEPART(dd, '7/5/00') => 7

8. Extracting Month from given Date

   Select DATEPART(m,'2002-09-30') => 9

9. To display records updated in January.

   SELECT * FROM titles
   WHERE DATEPART(month, Update) = 1

10. To display records updated in between January and march

    select * from titles
    Where DATEPART(month, updated) BETWEEN 1 AND 3

3. DAY(),MONTH() and YEAR() :

These are synonyms for DATEPART(dd,date), DATEPART(mm,date) and DATEPART(yy,date) resp.

Examples:

1. SELECT DAY('01/02/2005') => 2
2. SELECT MONTH('2002-09-30') => 9
3. SELECT YEAR(GETDATE()) => 2009

III-Finding Date Differences.

1. DATEDIFF():

Subtracts the first date from the second date.

Syntax:

DATEDIFF(Datepart , Startdate , Enddate )

Examples:

• A: DATE DIFFERENCE
  
  1. Select DATEDIFF(DY,"08/30/97","10/01/97") => 32
  2. Select DATEDIFF(day, '2001-12-01', '2002-09-30') => 303
  3. Select DATEDIFF(day, '2002-09-30', '2001-12-01') => -303
  4. Select DATEDIFF(month, '2001-12-01', '2002-09-30') =>  9
  5. Select DATEDIFF(year, '2001-12-01', '2002-09-30') => 1
  6. Select DATEDIFF(yy, 'Jan 1, 1998', 'Dec 31, 1998') =>  0
  7. Select DATEDIFF(quarter, '2001-12-01', '2002-09-30') => 3
  8. Select DATEDIFF(week, '2001-12-01', '2002-09-30') => 44 
• B: TIME DIFFERENCE:
  
  1. Select DATEDIFF(hour, '06:46:45', '11:35:00') =>  5
  2. Select DATEDIFF(minute, '06:46:45', '11:35:00') => 289
  3. Select DATEDIFF(second, '06:46:45', '11:35:00') =>  17295

IV-Modifying Date values

1. DATEADD():

Adds or subtracts a number of days, months, or years from a specific date.

Syntax:

DATEADD(date_part, number, date)
-- datepart is shown in above table.

Examples:

1. Adding an hour

   Select DATEADD(hour, 1, '2002-09-30 11:35:00') 
   => 2002-09-30 12:35:00.000

2. Adding a minut

   Select DATEADD(minute, 1, '2002-09-30 11:35:00') 
   =>2002-09-30 11:36:00.000

3. Adding a second

   Select DATEADD(second, 1, '2002-09-30 11:35:00')  
   =>2002-09-30 11:35:01.000

4. Adding 1-quarter

   Select DATEADD(quarter, 1, '2002-09-30 11:35:00')  
   =>2002-12-30 11:35:00.000

5. Adding 1-Week

   Select DATEADD(week, 1, '2002-09-30 11:35:00') 
   =>2002-10-07 11:35:00.000

6. Removing 1-month

   Select DATEADD(month, -1, '2002-09-30 11:35:00')  
   =>2002-08-30 11:35:00.000

7. Adding One-and-Half year

   Select DATEADD(year, 1.5, '2002-09-30 11:35:00') 
   =>2003-09-30 11:35:00.000

8. Adding 14 days to current date

   Select DATEADD(day, 14, GETDATE())

9. Adding no. of days to any formatted date.

   1. SELECT DATEADD(day, 12, '1/2/95')
      => 1/14/1995 It is equals to
      SELECT CONVERT(datetime, '1/2/95') + 12.
   2. Select DATEADD(day, 1, '2002-09-30 11:35:00')
      => 2002-10-01 11:35:00.000
   3. SELECT DATEADD(dd, 12, '1 Jan 95')
      => 1/13/1995 12:00:00 AM

10. Subtracting no.of days from given date. Add negative no. to subtract

    1. Removing days from given date

       Select DATEADD(day, -12, '1/2/95')
       => 12/21/1994 12:00:00 AM

    2. Adding months

       Select DATEADD(month, 1, '2002-09-30 11:35:00') 
       =>  2002-10-30 11:35:00.000

    3. Adding year

       Select DATEADD (year, 1, '2002-09-30 11:35:00')  
       => 2003-09-30 11:35:00.000

    4. Getting date of after 3 quarter.

       SELECT CURRENT_TIMESTAMP, DATEADD(quarter, 3, CURRENT_TIMESTAMP)
       => 6/3/2009 2:14:06 PM 3/3/2010 2:12:03 PM

2. DATE COMAMNDS

1. DATEFIRST:

   Changes the first day value, you can use the SET DATAFIRST command.
   SET DATEFIRST 7
   SELECT @@DATEFIRST 'First Day of the Week' => 7

2. DATEFORMAT :

   Changes date format for retrieving.
   SET DATEFORMAT ymd

V-Validating Date

1. ISDATE() :

Returns 1(true) or false(0)

1. Select ISDATE('2002-09-30') =>1
2. Select ISDATE('2002-09-31') =>0
3. Select ISDATE('2002-02-29') =>0  - - b’coz Feb last day 28th
4. Select ISDATE('23:99:99')  =>0
5. Select ISDATE('23:59:59')  =>1