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