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

Microsoft SQL Server Training Online Learning Classes Built-in functions Date

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

1 comment: