SQL Server date addition and subtraction functions DATEDIFF and DATEADD Usage Analysis, datediffdateadd

SQL Server date addition and subtraction functions DATEDIFF and DATEADD Usage Analysis, datediffdateadd

This example describes how to add and subtract functions DATEDIFF and DATEADD on SQL Server. We will share this with you for your reference. The details are as follows:

SQL Server date addition and subtraction function: DATEDIFF DATEADD

DATEDIFF: Returns the number of date boundary and time boundary across two specified dates. Syntax:DATEDIFF ( datepart , startdate , enddate )Subtract startdate from enddate

Note:Datepart specifies which part of the date should be used to calculate the difference parameter. 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: Return the new datetime value after the specified date plus a time interval. Syntax:DATEADD (datepart , number, date )

Note:Datepart specifies the date component of the date to return the new value

Number to increase the value of datepart. A positive number indicates an increase, and a negative number indicates a decrease. If it is a decimal number, the fractional part is ignored without rounding.

It is calculated based on some date values of DATEDIFF and DATEADD.

1) the first day of a year

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

Note: DATEDIFF (YEAR, 0, GETDATE () -- calculates the number of years in which the current YEAR is different from that in January 1, and then calculates the date of 1900 plus the number of years in which the difference is the first day of the current YEAR.

2) the first day of a quarter

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

Note: first, DATEDIFF (Quarter, 0, GETDATE () -- calculates the number of seasonal copies of the current month and the month, and then calculates the date of the number of seasonal copies plus the difference between 1900 and, that is, the first day of the current season.

3) the first day of a month

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

Note: DATEDIFF (MONTH, 0, GETDATE () -- calculates the number of months in which the current MONTH is different from that in January 1, 1900. Then, the date of the number of months plus the number of different months is the first day of the current MONTH.

4) The first day of a week

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

5) midnight of the day (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 ms from the first day of the month to obtain the last day of the month. The time of the SQL SERVER DATETIME type is accurate to 3 ms.

7) the 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) the last day of the year

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

10) the first day of a 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

In CONVERT, the Style. Parameter: 108 and 114 can only get the time.

Example

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

Date judgment FunctionISDATE()Determines whether the input expression is a valid date. If 1 is valid, 0 is returned, and INT is returned.