0. Date and time type

0.0 time type

• [Note 1] the time types before SQL Server 2008 are DATETIME and SMALLDATETIME only, and the separate types of date and time only appear after 2008
• [Note 2] the range of DATETIME 2 is larger than that of DATETIME, and the accuracy is also improved
• [Note 3] the recommended format is only the recommended format. Any date type on it supports both "YYYY-MM-DD" and "YYYYMMDD"
• [Note 4] the + / - hh:mm in DateTimeOffset indicates the time zone difference
  • UTC: World coordinated time, the standard time in today's world
  • UTC + time zone difference = local time, time zone: East is positive and West is negative
  • The Beijing time zone is the East eighth District, 8 hours ahead of UTC time
    Simple test, with an intuitive understanding:

DECLARE @dateTime DATETIME = GETDATE();
SELECT @dateTime;
--Result: 2021-10-23 11:53:56.420

DECLARE  @smallDateTime SMALLDATETIME =GETDATE();
SELECT @smallDateTime
--Result: 2021-10-23 11:54:00

DECLARE @date DATE=GETDATE();
SELECT @date
--Result: 2021-10-23

DECLARE @time TIME =GETDATE();
SELECT @time
--Result: 11:53:56.4200000

DECLARE @dateTime2 DATETIME2 =GETDATE();
SELECT @dateTime2
--Result: 2021-10-23 11:53:56.4200000

DECLARE @dateTimeOffset DATETIMEOFFSET =GETDATE();
SELECT @dateTimeOffset
--Result: 2021-10-23 11:53:56.4200000 +00:00

1. Conversion function

1.1 CAST

• Description: converts the specified expression to the target data type. A premise here is that the specified expression can theoretically be converted to the target type you specify, and is responsible for error reporting

• [syntax] cast (expressionstring as datatype)

• [example]

  • Select cast ('012 'as int) Returns: 12
  • Select cast ('1.23 'as int) returned: failed to convert varchar value' 1.23 'to data type int.

1.2 CONVERT

• [description] its function is the same as that of CAST

  • However, CONVERT can set the third parameter to specify the conversion style, so it can CONVERT a specific time string to a specific time format through this parameter
  • CAST is standard SQL, while CONVERT is not standard SQL

• [syntax] COVNERT(dataType,expressionString,[style_number])

• [example]

  • SELECT CONVERT(INT, '012') Returns: 12
  • TODO......

2. Date operation function

2.0 GETDATE and getutdate

• [description] both GETDATE and GETUTDATE functions are used to return the current date and time of datetime type

  • GETUTDATE() uses the time zone setting on the database server to calculate UTC time

• [example]

  SELECT GETDATE()
  --Result: 2021-10-23 16:59:59.917
  SELECT GETUTCDATE()
  --Result: 2021-10-23 08:59:59.917
  --It can be seen that the current Beijing time (Dongba District) is 8 hours faster than the world coordinated time
  

2.1 SYSDATETIME and SYSUTCDATETIME

• [description] returns the current time. It is equivalent to GERDATE and GETUTCDATE. This is the result of datatime2 added in SQL Server 2008

• [example]

  SELECT SYSDATETIME()
  --Result: 2021-10-23 17:01:54.9879870
  SELECT SYSUTCDATETIME()
  --Result: 2021-10-23 09:01:54.9879870
  

2.2 DATEADD

• [description] used to add the interval specified by the specified unit to the date value

  • Note that the interval value can be negative to subtract the interval specified by the specified unit

• [syntax]
  DATEADD(datePart,number,date)

  time interval	parameter
  year	Year，yyyy,yy
  quarter	Quarter,qq,q
  month	Month,mm,m
  Days in a year	DayOfYear,dy,y
  day	Day,dd,d
  week	Week,wk,www

• [example]

  --Current date plus 1 day
  SELECT DATEADD(Day,1,GETDATE())
  --Current date minus 1 day
  SELECT DATEADD(Day,-1,GETDATE())
  
  

2.3 DATEDIFF

• [description] calculate the interval between two dates according to the specified interval unit

• [syntax]
  DATEDIFF(datePart,startDate,endDate)
  Note: the interval units in datePart and DATEADD are the same

• [examples]

  --Calculate the number of days between two dates
  SELECT DATEDIFF(DAY,'2021-01-01','2021-01-02')--Results: 1
  --Calculate the number of months between two dates
  SELECT DATEDIFF(MONTH,'2020-01-01','2021-01-02')--Result: 12
  

2.4 DATEPART and DATENAME

• [description] obtain the specified part of the date, such as year, month and day
  Differences between the two in Chinese environment:
  Datapart returns an integer,
  DATENAME returns a string type (if it is in English, for example, if it returns a month, it directly returns the name of the month)

• [syntax]
  DATEPART(datePart,dateString)
  DATENAME(datePart,dateString)

• [example]

  SELECT DATENAME(MONTH,'2021-01-01')--Result: 1 (string type)
  
  SELECT DATEPART(MONTH,'2021-01-01')--Result: 1 (integer)
  

2.5 YEAR, MONTH and DAY

• [description] get the specified part of the specified date, such as year, month and day
  It can be considered as a simplified function of DATEPART

• [syntax]
  YEAR(dateString)
  MONTH(dateString)
  DAY(dateString)

• [example]

  SELECT YEAR('20211031')--Result: 2021
  SELECT MONTH('20211031')--Result: 10
  SELECT DAY('20211031')--Result: 31
  

2.6 ISDATE

• [description] judge whether the specified string can be converted to time format

• [syntax]
  ISDATE(dateString)

• [example]

  SELECT ISDATE('20211031')--Results: 1
  SELECT ISDATE('20211031 22:46:01:01')--Results: 1
  SELECT ISDATE('2021-10-31')--Results: 1
  SELECT ISDATE('2021/10/31')--Results: 1
  SELECT ISDATE('2021-10-32')--Result: 0
  

3. Comprehensive use example

3.1 age based on date of birth

CREATE TABLE #temptable
(
    [Name] VARCHAR(4),
    [Brithday] VARCHAR(10)
);
INSERT INTO #temptable
VALUES
('Zhang San', '1994-01-01'),
('Li Si','1994-12-01');--Note: the test date is October 31, 2021

SELECT Name,
       DATEDIFF(YEAR, Brithday, GETDATE()) AS Age,--Accurate to year
	   CONVERT(INT,DATEDIFF(DAY,Brithday,GETDATE())/365.25) AS Age--Accurate to day
FROM #temptable;

--Results: the date of execution of this sentence is October 31, 2021, so Li Si hasn't reached his birthday this year, so it's 26
--Name Age         Age
------ -------- ---------
--Zhang San 27         27
--Li si27         26

3.2 to be supplemented