Date operation in SQL Server
- Date and digital type relationship, DateTime Date and DateTime Time Add and subtraction, replace the date time of the datetime to another time
Basic Function Processing can be obtained from the help manual, the location is the instructions of the TRANSACT-SQL Reference / Functions / Date and Time Functions, including the date of date, date difference, date section, the day, etc., here only say a document Not included.
1, every DateTime field, it is in Japan, every day, meant 1, from 1900, the number of seconds is converted. For example, 10:00, it is equivalent to 10 / 24.0; 14:30, it is equivalent to 14.5 / 24.0
The results of the following two sets of query statements are equivalent Select Cast (Cast ('10: 00 'as datetime) as float "SELECT 10.0 / 24.0Select Cast (Cast ('16: 30: 00' as datetime) as float SELECT 16.5 / 24.0
Note: If the date is not given, that is, the number of days is zero, that is, from 1900-01-01
2, the time decimal in the same day can get SELECT CAST (GetDate () as float), Floor (Cast () AS Float) AS Today, Cast (Getdate () as float) -floor (CAST) (GetDate () as float)) Today
3, date and time add a datetime plus 5 and a half hours Select Cast (Cast ('as datetime) as float) Cast (Cast (' 5:30 : 00 'as datetime) As datetime) or SELECT CAST (Cast (Cast (' 2008-10-01 15:30:00 'as datetime) AS float) 5.5 / 24 as datetime)
4. Replace the time in a datetime to another, while the date remains constant SELECT DATEADD (Day, Cast (Cast ('2008-10-01 15:30:00' as datetime), Cast (' 5:30:00 'as datetime) or Select Cast (Cast (Cast (' 2008-10-01 15:30:00 'as datetime) AS INT) 5.5 / 24 as datetime)
Note: 5.5 / 24 is less than zero decimal, greater than zero is equivalent to adding more than one day.
