tailieunhanh - Expert SQL Server 2008 Development- P8

Tham khảo tài liệu 'expert sql server 2008 development- p8', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả | CHAPTER 11 WORKING WITH TEMPORAL DATA 3. Using the DATEDIFF function find the difference between the reference date time and the date time you want to truncate at the level of granularity you ve chosen. 4. Finally use DATEADD to add the output from the DATEDIFF function to the same reference date time that you used to find the difference. The result will be the truncated value of the original date time. Walking through an example should make this a bit clearer. Assume that you want to start with 2010-04-23 13 45 and truncate the time portion in other words come out with 2010-04-23 at midnight . The granularity used will be days since that is the lowest level of granularity above the units of time milliseconds seconds minutes and hours . The following T-SQL can be used to determine the number of days between the reference date of 1900-01-01 and the input date DECLARE @InputDate datetime 20100423 13 45 SELECT DATEDIFF day 19000101 @InputDate Running this T-SQL we discover that 40289 days passed between the reference date and the input date. Using DATEADD that number can be added to the reference date SELECT DATEADD day 40289 19000101 The result of this operation is the desired truncation 2010-04-23 00 00 . Because only the number of days was added back to the reference date with no time portion the date was rounded down and the time portion eliminated. Of course you don t have to run this T-SQL step by step in a real application you d probably combine everything into one inline statement SELECT DATEADD day DATEDIFF day 19000101 @InputDate 19000101 Because it is a very common requirement to round down date time values to different levels of granularity to find the first day of the week the first day of the month and so on you might find it helpful to encapsulate this logic in a reusable function with common named units of time as follows CREATE FUNCTION DateRound @Unit varchar 32 @InputDate datetime RETURNS datetime AS BEGIN DECLARE @RefDate .