Introduction to datetime type

A datetime field can store the date range from the first millisecond of January 1, January 1, 1753 to the last millisecond of January 1, December 31, 9999. If you do not need to cover such a large range of dates and times, you can use SMALLDATETIME data. It is used in the same way as datetime data, except that it can represent a smaller date and time range than datetime data, and is not as precise as datetime data. A smalldatetime field can be stored from January 1, January 1-20, 1900 to January 1, June 6. It can only be accurate to seconds.
Datetime data type operation

1. Date Truncation

Use extend to extract the partial score of a date, for example:
Start_time = '2017-06-15 11:12:13 'start_time shown below this article is datetime year to second
Extend (start_time, year to month) = '2017-06'
Extend (start_time, hour to hour) = '11'
It should be noted that the types '2017-06 'and '11' returned by the above functions are still 'datetime' type and cannot be directly added or subtracted.

2. Date Calculation

Date addition and subtraction is often used in applications, such as the day before the current date or the day before the specified date,
This operation is hard to say. Let's take a look at the example below to illustrate the problem:
Select
Curretn
Current year to day-interval (9) day to day,
Current year to second-interval (9) minute to minute
From tab1
Will return:
2004-06-13 19:30:42. 000
2004-06-04
19:21:42
I don't know.
Type conversion

The extend (start_time, hour to hour) = '11' is still 'datetime' type. If you want to convert the type:

1. extend (start_time, hour to hour) | ''returns char type, extend (start_time, hour to hour) |'' + 0 returns integer type, extend (start_time, hour to hour) | ''+ 0.0 will return the float type, although not very nice, but the effect is good.

Methods like extend (start_time, hour to hour) | ''+ 0 can be used in select, but when used in create view, the syntax error IDS 9.4 will occur ).

2. Use the to_char function: to_char (start_time) to return the character form of the start_time field as is, for example:
SELECT TO_CHAR (start_time, '% A % B % d, % Y % R') FROM tab1
Result: Wednesday July 23,200 3
Where: % A: Week, % B: monthly English), % m month), % d: Day, % Y: Year, % R: time.
According to the above method, you can process the date and time according to your favorite shape.
(IDS 9.4)

3. to_date function:
Usage is similar to to_char, for example, to_date ('20140901', '% Y % m % d % H % M % s ')
Returns a datetime type: 11:12:13. 00000