SQL-datediff Function

Datediff

Returns the number of date and time boundaries that span two specified dates.

I. Syntax

Datediff ( Datepart , Startdate, enddate)

Ii. Parameters
Datepart

Is a parameter that specifies which part of the date should be used to calculate the difference. The following table lists the dates and abbreviations recognized by Microsoft SQL Server.

Abbreviation of date
Year YY, yyyy
Quarter QQ, Q
Month Mm, m
Dayofyear dy, y
Day DD, d
week wk, WW
hour hh
minute Mi, n
second SS, S
millisecond MS

Startdate

Is the start date of the calculation. Startdate is an expression that returns the datetime, smalldatetime, or date format string.

Because smalldatetime is accurate to minutes, when smalldatetime is used, the second and millisecond are always 0.

If you only specify the last two digits of the year, the number of the last two digits smaller than or equal to the value of the "two-digit year deadline" configuration option is in the same century as the end year. The last two-digit number greater than the value of this option is in the century before the end of the century. For example, if two digit year cutoff is 2049 (default), 49 is interpreted as 2049,2050 as 1950. To avoid blurring, use a four-digit year.

Enddate

Is the end date of the calculation. Enddate is an expression that returns the datetime, smalldatetime, or date format string.

Iii. Return type

Integer  

Iv. Usage
This function calculates the number of date parts between two specified dates. The result is a signed integer equal to (date2-date1) in the date section.

When the result is not an even multiple of the date part, datediff is truncated instead of rounded.

When day is used as the date part, datediff returns the number of midnight between two specified times (including the second date but not the first date.

When month is used as the date part, datediff returns the number of the first day of the month between two dates (including the second date but not the first date.

When week is used as the date part, datediff returns the number of Sundays between two dates (including the second but not the first.

Overflow value for a smaller unit of time:

Milliseconds 24 Days

Seconds68Year

Minutes4083Year

Others has no overflow restrictions

 

If these limits are exceeded, this function returns an overflow error.

 

V. Standards and compatibility
SQL/92 Transact-SQL extension.

SQL/99 Transact-SQL extension.

Sybase is compatible with Adaptive Server Enterprise.

Vi. Example

The following statement returns 1 :

Select Datediff(Hour,''4: 00 AM'',''5: 50am'')
The following statement returns102:

Select   Datediff ( Month , '' 1987 / 05 / 02 '' , '' 1995 / 11 / 15 '' )
The following statement returns 0 :

select datediff ( day , '' 00 : 00 '' , '' 23 : 59 '' )
the following statement returns 4 :

Select   Datediff ( Day , '' 1999 / 07 / 19   00 : 00 '' , '' 1999 / 07 / 23   23 : 59 '' )
The following statement returns 0 :

Select   Datediff ( Month , '' 1999 / 07 / 19 '' , '' 1999 / 07 / 23 '' )
The following statement returns 1 :

Select   Datediff ( Month , '' 1999 / 07 / 19 '' , '' 1999 / 08 / 23 '' )

The datediff function is a very useful function that can provide some special effects for some webpages.
I used it and a new image to identify whether the information displayed on the webpage is the latest information.
For example, prompting recent notifications, recent news, and so on.

After reading the above introduction, we will add a sentence after the data bound by the gridview or datelist

< IMG SRC = ' <% # (Convert. toint16 (databinder. eval (container. dataitem, "daycount") <3 )? "Images/new.gif": "images/none.gif" %> ' Border = " 0 " >

In the backgroundCodeMedium:
SQL statement used to bind data

String SQL = " Select top 10 tid, title, datediff (day, tdate, getdate () as daycount from TB order by TID DESC "

Here, tdate is the time when you release the information, and daycount is the interval between the current time and the time when you release the information. In the preceding example, a "new" image is marked within three days.