T-SQL -- time operation function

catalogue

Zhiming - October 31, 2021 22:57:15

0. Date and time type

0.0 time type

data type time frame Accuracy Recommended format Example
DATETIME 1753-01-01 to 9999-12-31 3.33 MS 'YYYYMMDD hh:mm:ss.nnn' 2021-10-23 14:35:20.7 twenty-seven
SMALLDATETIME 1900-01-01 to 2079-06-06 1 minute 'YYYYMMDD hh:mm' 2021-10-23 14:41:00
DATE 0001-01-01 to 9999-12-31 1 day 'YYYY-MM-DD' 2021-10-23
TIME 00:00:00: 0000000 to 23:59:59.999999 100 ns 'hh:mm:ss:nnnnnnn' 14:44:35.4170000
DATETIME2 0001-01-01 00:00:00: 0000000 to 9999-12-31 23:59:59.999999 100 ns 'YYYY-MM-DD hh:mm:ss.nnnnnnn' 2021-10-23 14:46:10.9600000
DATETIMEOFFSET 0001-01-01 00:00:00: 0000000 to 9999-12-31 23:59:59.999999 100 ns 'YYYY-MM-DD hh:mm:ss.nnnnnnn [+|-] hh:mm' 2021-10-23 14:48:30.8730000 +00:00
  • [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

Tags: SQL

Posted on Sun, 31 Oct 2021 10:56:02 -0400 by msk_1980