Analysis of DATEDIFF and DATEADD Usage of Date Addition and Subtraction Function of SQL Server

Time:2019-8-9

This paper illustrates the usage of DATEDIFF and DATEADD of the date addition and subtraction function of SQL Server. Share for your reference, as follows:

Addition and subtraction function of SQL Server date: DATEDIFF DATEADD

DATEDIFFReturns the number of date boundaries and time boundaries across two specified dates.DATEDIFF ( datepart , startdate , enddate )Subtract start date from end date

Note:Datepart specifies which part of the date should be used to calculate the difference. When the date is subtracted, only the boundary value is concerned. For example:


SELECT DATEDIFF(YEAR,'2008-12-31','2009-1-1')

Return 1

DATEADD Returns a new datetime value after adding a time interval to the specified date. Grammar:DATEADD (datepart , number, date )

Note:Datepart specifies the component of the date on which the new value is to be returned

Number is used to increase the value of the datepart. Positive number means increase, negative number means decrease, if it is decimal, the decimal part is ignored, and no rounding is done.

Some Date Calculations Through DATEDIFF and DATEADD

1) The first day of the year


SELECT DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE()),0)

Note: First, DATEDIFF (YEAR, 0, GETDATE ()) – calculates the number of years that are different from 1900, and then calculates the date of 1900-1-1 plus the number of years that are different, that is, the first day of the year.

2) The first day of a season


SELECT DATEADD(Quarter,DATEDIFF(Quarter,0,GETDATE()),0)

Note: First of all, DATEDIFF (Quarter, 0, GETDATE ()) – calculates the number of seasons that differ from 1900 in the current month, then the date of 1900-1-1 plus the number of seasons that differ is the first day of the current season.

3) The first day of a month


SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)

Note: First DATEDIFF (MONTH, 0, GETDATE ()) – Calculate the number of months that are different from 1900, and then calculate the date of 1900-1-1 plus the number of months that are different is the first day of the month.

4) The first day of the week


SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)

5) Midnight (00:00:00.000)


SELECT DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)

6) Last day of last month


SELECT DATEADD(ms,-3,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0))

Note: Subtract 3 milliseconds from the first day of this month to get the most day of last month. The time of the SQL SERVER DATETIME type is accurate to 3 milliseconds.

7) Last day of the month


SELECT DATEADD(ms,-3,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,0))

8) Days of the month

i)


SELECT DAY(DATEADD(ms,-3,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,0)))

ii)


SELECT 32-DAY(GETDATE()+(32-DAY(GETDATE())))

9) Last day of the year


SELECT DATEADD(ms,-3,DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE())+1,0))

10) The first day of the week


SELECT DATEADD(DAY,1-DATEPART(weekday,GETDATE()),GETDATE())

The last day of the week


SELECT DATEADD(DAY,7-DATEPART(WeekDay,GETDATE()),GETDATE())
SELECT DATEADD(weekday,DATEDIFF(weekday,0,DATEADD(DAY,6-DATEPART(day,GETDATE()),GETDATE())),0)

Date conversion functionCONVERT CAST

Style. parameter in CONVERT: 108 and 114 can only get time.

example


SELECT CONVERT(NVARCHAR(12),GETDATE(),108) ---12:41:15
SELECT CONVERT(NVARCHAR(12),GETDATE(),114) ---12:43:12:590

Date Judgment FunctionISDATE()Determine whether the input expression is a valid date. If the valid return is 1 or 0, the return value is INT.

More readers interested in the relevant content of SQL Server can see the topics of this site: Summary of SQL Server Date and Time Operating Skills, SQL Server Query Operating Skills, SQL Server Stored Procedure Skills, SQL Server Index Operating Skills and Summary of Common Functions of SQL Server.

I hope this article will be helpful to the design of SQL Server database program.