You are here
Home > Sql Server > Date and Time Functions in sql

Date and Time Functions in sql

Date and Time Functions

Date and Time Functions

  • T-SQL supports a many date and time functions that allow you to manipulate your date and time data.

Current date and time

  • T-SQL supports built- in functions like GETDATE, GETUTCDATE, CURRENT_TIMESTAMP, SYSDATETIME, SYSUTCDATETIME and SYSDATETIMEOFFSET that returns the current date and time.

CURRENT_TIMESTAMP:

  • Returns the current date and time.

GETDATE:

  • It returns a current date and time.
  • Example: SELECT GETDATE();

GETUTCDATE:

  • It returns a current date and time in UTC terms.
  • Example: SELECT GETUTCDATE();

SYSDATETIME:

  • It returns a datetime(2) value that contains the data and time of the computer on which the SQL is running.
  • Example: SELECT SYSDATETIME();

SYSUTCDATETIME:

  • It returns a datetime value that contains the data and time of the computer on which the SQL is running and the date and time is returned as UTC time.
  • Example: SELECT SYSUTCDATETIME();

SYSDATETIMEOFFSET:

  • It returns a datetimeoffset value that contains the date and time of the computer on which sql running. The timezone offset is included.
  • Example: SELECT SYSDATETIMEOFFSET();

 

Date and time parts

  • T-SQL supports a many date and time functions that either extract a part from date and time value or construct date and time value from parts.

DATEPART:

  • It returns a specified part of a given date as an integer value.
  • Syntax: DATEPART (datepart, date)
  • Example: SELECT DATEPART(YEAR,’20160808′);
  • Outpue: 2016

DAY:

  • It returns the day of the month for a given date.
  • Syntax: DAY(date)
  • Example: SELECT DAY(‘20160214’);
  • Output: 14

MONTH:

  • It returns the month for a given date.
  • Syntax: MONTH(date)
  • Example: SELECT MONTH(‘20160214’);
  • Output: 2

YEAR:

  • It returns the year for a given date.
  • Syntax: YEAR(date)
  • Example: SELECT YEAR(‘20160214’);
  • Output: 2016

DATENAME:

  • It returns a specified part of a given date as a string value.
  • It is language dependent.
  • Syntax: DATENAME(datepart, date)
  • Example: SELECT DATENAME(MONTH,’20160808′);
  • Output: August

DATEFROMPARTS:

  • It returns a date value for the specified year, month and day.
  • Syntax: DATEFROMPARTS(year, month, day)
  • Example: SELECT DATEFROMPARTS(2016,08,08);
  • Output: 2016-08-08

DATETIME2FROMPARTS:

  • It returns a datetime2 value for the specified date and time with specified precision.
  • Syntax: DATETIME2FROMPARTS(year, month, day, hour, minute, second, fraction, precision)
  • Example: SELECT DATETIME2FROMPARTS(2016,08,08,4,12,48,1,4);
  • Output: 2016-08-08 04:12:48.0001

DATETIMEFROMPARTS:

  • It returns a datetime value for the specified date and time.
  • Syntax: DATETIMEFROMPARTS(year, month, day, hour, minute, second, millisecond)
  • Example: SELECT DATETIMEFROMPARTS(2016,08,08,4,12,48,444);
  • Output: 2016-08-08 04:12:48.443

DATETIMEOFFSETFROMPARTS:

  • It returns a datetimeoffset value for the specified date and time and with the specified offset and precision.
  • Syntax: DATETIMEOFFSETFROMPARTS(year, month, day, hour, minute, second, fraction, houroffset, minuteoffset, precision)
  • Example: SELECT DATETIMEOFFSETFROMPARTS(2016,08,08,4,12,48,1,-8,0,4);
  • Output: 2016-08-08 04:12:48.0001 -08:00

SMALLDATETIMEFROMPARTS:

  • It returns a small date and time as a result.
  • Syntax: SMALLDATETIMEFROMPARTS(year, month, day, hour, minute)
  • Example: SELECT SMALLDATETIMEFROMPARTS(2016,08,08,4,12);
  • Output: 2016-08-08 04:12:00

TIMEFROMPARTS:

  • It returns a time value for the specified time and with the specified precision.
  • Syntax: TIMEFROMPARTS(hour, minute, second, fraction, precision)
  • Example: SELECT TIMEFROMPARTS(4,12,48,2,4);
  • Output: 04:12:48.0002

EOMONTH:

  • It returns end of the month date for the input date and time value.
  • Example: SELECT EOMONTH(‘20160402’);
  • Output: 2016-04-30

 

Add and diff functions

  • T-SQL supports difference and addition date and time functions called DATEDIFF and DATEADD.

DATEDIFF:

  • It returns the difference between two date values based on the interval specified.
  • It returns a value of an INT type.
  • Syntax: DATEDIFF(datepart, startdate, enddate)
  • Example: SELECT DATEDIFF(YEAR,’20140402′,’20160604′);
  • Output: 2

DATEDIFF_BIG:

  • It returns the difference between two date values based on the interval specified.
  • It returns the result as a BIGINT type.
  • Syntax: DATEDIFF_BIG(datepart, startdate, enddate)
  • Example: SELECT DATEDIFF_BIG(YEAR,’20140402′,’20160604′);
  • Output: 2

DATEADD:

  • Using this function, you can add a requested number of units of a specified part to a specified date and time value.
  • Syntax: DATEADD(datepart, numberofunits, date)
  • Example: SELECT DATEADD(YEAR,2,’20160402′);
  • Output: 2018-04-02 00:00:00.000

 

Offset

  • T-SQL supports three functions related to date and time values with an offset. The functions are SWITCHOFFSET, TODATETIMEOFFSET, and ATTIMEZONE.

SWITCHOFFSET:

  • It returns the input datetimeoffset value adjusted to requested target offset from the UTC time zone.
  • Syntax: SWITHOFFSET(datetimeoffset, timezone)
  • Example: SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(),’-04:00′);
  • Output: 2018-08-31 10:44:41.8892762 -04:00

TODATETIMEOFFSET:

  • It returns the input datetimeoffset value that is translated from a datetime2 expression.
  • Syntax: TODATETIMEOFFSET(expression, timezone)
  • Example: SELECT TODATETIMEOFFSET(‘20140402 12:00:00.0000000′,’-04:00′);
  • Output: 2014-04-02 12:00:00.0000000 -04:00

ATTIMEZONE:

  • It can be used instead of both the TODATETIMEOFFSET and the SWITCHOFFSET functions and that uses named time zones instead of offsets.
  • Example-1: SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(),’-08:00′)AT TIME ZONE ‘PACIFIC STANDARD TIME’;
  • Output: 2018-08-31 07:52:23.5686891 -07:00
  • Example-2: DECLARE @DT AS DATETIME = ‘20140402 12:00:00’;

SELECT @DT AT TIME ZONE ‘PACIFIC STANDARD TIME’;

  • Output: 2014-04-02 12:00:00.000 -07:00

 

Keep visiting my site ask2tech.com for upcoming top technical article

Leave a Reply

Top