Functions that return system date and time

• Common mistakes when working with dates and time

  • Inconsistent date time formats or patterns
  • Arithmetic Operations
  • Issues with time zones

• Time Zones in SQL Server

  • Local Time Zone
  • UTC Time Zone (Universal Time Coordinate)

• Functions that return the date and time of the operatins system

  • Higher-precision
    • SYSDATETIME()
    • SYSUTCDATETIME()
    • SYSDATETIMEOFFSET()
  • Lower-precision
    • GETDATE()
    • GETUTCDATE()
    • CURRENT_TIMESTAMP

• Higher-precision functions example

        SELECT
            SYSDATETIME() AS [SYSDATETIME],
            SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET],
            SYSUTCDATETIME() AS [SYSUTCDATETIME];
  

• Lower-precision functions example

        SELECT
            CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP],
            GETDATE() AS [GETDATE],
            GETUTCDATE() AS [GETUTCDATE];
  

• Retrieving only the date

        SELECT
            CONVERT(date, SYSDATETIME()) AS [SYSDATETIME],
            CONVERT(date, SYSDATETIMEOFFSET()) AS [SYSDATETIMEOFFSET],
            CONVERT(date, SYSUTCDATETIME()) AS [SYSUTCDATETIME],
            CONVERT(date, CURRENT_TIMESTAMP) AS [CURRENT_TIMESTAMP],
            CONVERT(date, GETDATE()) AS [GETDATE],
            CONVERT(date, GETUTCDATE()) AS [GETUTCDATE;
  

• Retrieving only the time

        SELECT
            CONVERT(time, SYSDATETIME()) AS [SYSDATETIME],
            CONVERT(time, SYSDATETIMEOFFSET()) AS [SYSDATETIMEOFFSET],
            CONVERT(time, SYSUTCDATETIME()) AS [SYSUTCDATETIME],
            CONVERT(time, CURRENT_TIMESTAMP) AS [CURRENT_TIMESTAMP],
            CONVERT(time, GETDATE()) AS [GETDATE],
            CONVERT(time, GETUTCDATE()) AS [GETUTCDATE;
  

---

Functions returning date and time parts

• YEAR(date)

  • Returns the year from the specified date

      SELECT
          first_name,
          first_vote_date,
          YEAR(first_vote_date) AS first_vote_year
      FROM voters;
    

• MONTH(date)

  • Returns the month from the specified date

      SELECT
          first_name,
          first_vote_date,
          YEAR(first_vote_date) AS first_vote_year,
          MONTH(first_vote_date) AS first_vote_month
      FROM voters;
    

• MONTH(date)

  • Returns the day from the specified date

      SELECT
          first_name,
          first_vote_date,
          YEAR(first_vote_date) AS first_vote_year,
          MONTH(first_vote_date) AS first_vote_month,
          DAY(first_vote_date) AS first_vote_day
      FROM voters;
    

• DATENAME(datepart, date)

  • Returns a character string representing the specified date part of the given date

  datepart	abbreviations
  year	yy,yyyy
  month	mm, m
  dayofyear	dy,y
  week	wk,ww
  weekday	dw,w

• DATENAME() example

        DECLARE @date datetime = '2019-03-24'
        SELECT
            YEAR(@date) AS year,
            DATENAME(YEAR, @date) AS year_name,
            MONTH(@date) AS month,
            DATENAME(MONTH, @date) AS month_name,
            DAY(@date) AS day,
            DATENAME(DAY, @date) AS day_name,
            DATENAME(WEEKDAY, @date) AS weekday
  

• DATEPART(datepart, date)

  • It is similar to DATENAME()
  • Returns an integer representing the specified part of the given date

      DECLARE @date datetime = '2019-03-24'
      SELECT
          DATEPART(YEAR, @date) AS year,
          DATENAME(YEAR, @date) AS year_name,
          DATEPART(MONTH, @date) AS month,
          DATENAME(MONTH, @date) AS month_name
    

• DATEFROMPARTS(year, month, day)

  • Receives 3 parameters: year, month, and day values
  • Generates a date

      SELECT
          YEAR('2019-03-05') AS date_year,
          MONTH('2019-03-05') AS date_month,
          DAY('2019-03-05') AS date_day,
          DATEFROMPARTS(YEAR('2019-03-05'),MONTH('2019-03-05'), DAY('2019-03-05')) AS reconstructed_date;
    

---

Performing Arithmetic Operations On Dates

• Types of Operations With Dates

  • Operations using arithmetic operators (+,-)
  • Modify the value of a date - DATEADD()
  • Return the difference between two dates - DATEDIFF()

• Arithmetic Operations

        DECLARE @date1 datetime = '2019-01-01';
        DECLARE @date2 datetime = '2020-01-01';
        SELECT
            @date2 + 1 AS add_one,
            @date2 -1 AS subtract_one,
            @date2 + @date1 AS add_dates,
            @date2 - @date1 AS subtract_date;
  

• DATEADD(datepart, number, date)

  Add or subtract a number of time units from a date

        SELECT
            first_name, 
            birthdate,
            DATEADD(YEAR, 5, birthdate) AS fifth_birthday,
            DATEADD(YEAR,-5,birthdate) AS subtract_5years,
            DATEADD(DAY,30, birthdate) AS add_30days,
            DATEADD(Day, -30, birthdate) AS subtract_30days
        FROM voters;
  

• DATEDIFF(datepart, startdate, enddate)

  Returns the number of units between two dates

        SELECT
            first_name,
            birthdate,
            first_vote_date,
            DATEDIFF(YEAR, birthdate, first_vote_date) AS age_years,
            DATEDIFF(QUARTER, birthdate, first_vote_date) AS age_quarters,
            DATEDIFF(DAY, birthdate, first_vote_date) AS age_days,
            DATEDIFF(HOUR, birthdate, first_vote_date) AS age_hours
        FROM voters;
  

---

Validating if an expression is a date

• ISDATE(expression)

  Determines whether an expression is a valid date data type

  ISDATE() expression	Return Type
  date, time, datetime	1
  datetime2	0
  other type	0

• ISDATE(expression)

        DECLARE @date1 NVARCHAR(20) = '2019-05-05'
        DECLARE @date2 NVARCHAR(20) = '2019-01-XX'
        DECLARE @date3 CHAR(20) = '2019-05-05 12:45:59.9999999'
        DECLARE @date4 CHAR(20) = '2019-05-05 12:45:59'
  
        SELECT
            ISDATE(@date1) AS valid_date,
            ISDATE(@date2) AS invalid_date,
            ISDATE(@date3) AS valid_datetime2,
            ISDATE(@date4) AS valid_datetime;
  

• SET DATEFORMAT

  SET DATEFORMAT {format}

  • Sets the order of the date parts for interpreting strings as dates
  • Valid formats:
    • mdy, dmy, ymd, ydm, myd, dym

        DECLARE @date1 NVARCHAR(20) = '12-30-2019'
        DECLARE @date2 NVARCHAR(20) = '30-12-2019'
  
        SET DATEFORMAT dmy;
        SELECT
            ISDATE(@date1) AS invalid_dmy,
            ISDATE(@date2) AS valid_dmy;
  

• SET LANGUAGE

  SET LANGUAGE {language}

  • Sets the language for the session
  • Implicitly sets the setting of SET DATEFORMAT
  • Valid languages: English, Italian, Spanish, etc.

        SET LANGUAGE English;
        SELECT
            ISDATE('12-30-2019') AS mdy,
            ISDATE('30-12-2019') AS dmy;