INSERT INTO dbo.Duration(startDate, endDate) (startDate datetime2, endDate datetime2) This example calculates the number of day boundaries crossed between dates in two columns in a table. Specifying columns for startdate and enddate These examples use different types of expressions as arguments for the startdate and enddate parameters.
DATEDIFF always uses Sunday as the first day of the week to ensure the function operates in a deterministic way.ĭATEDIFF may overflow with a precision of minute or higher if the difference between enddate and startdate returns a value that is out of range for int. Specifying SET DATEFIRST has no effect on DATEDIFF. You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format. This means that DATEDIFF does not support the format YDM when the date is passed as a string. Use DATEDIFF in the SELECT, WHERE, HAVING, GROUP BY and ORDER BY clauses.ĭATEDIFF implicitly casts string literals as a datetime2 type. If startdate and enddate have different year values, but they have the same calendar week values, DATEDIFF will return 0 for datepart week. The difference between the startdate and enddate in each statement crosses one calendar or time boundary of its datepart. Those dates are adjacent and they differ in time by a hundred nanoseconds (.0000001 second). The following statements have the same startdate and the same enddate values. If startdate and enddate have different date data types, and one has more time parts or fractional seconds precision than the other, DATEDIFF sets the missing parts of the other to 0. If either startdate or enddate have only a time part and the other only a date part, DATEDIFF sets the missing time and date parts to the default values. If only a date value is assigned to a variable of a time or date data type, DATEDIFF sets the value of the missing time part to the default value: 00:00:00. If only a time value is assigned to a date data type variable, DATEDIFF sets the value of the missing date part to the default value. If startdate and enddate are both assigned only a time value, and the datepart is not a time datepart, DATEDIFF returns 0.ĭATEDIFF uses the time zone offset component of startdate or enddate to calculate the return value.īecause smalldatetime is accurate only to the minute, seconds and milliseconds are always set to 0 in the return value when startdate or enddate have a smalldatetime value. For second, the maximum difference is 68 years, 19 days, 3 hours, 14 minutes and 7 seconds. For millisecond, the maximum difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds. This case means that if we start at startdate '', and then count -2 days, we reach the enddate of ''.įor a return value out of range for int (-2,147,483,648 to +2,147,483,647), DATEDIFF returns an error. The int difference between the startdate and enddate, expressed in the boundary set by datepart.įor example, SELECT DATEDIFF(day, '', '') returns -2, hinting that 2036 must be a leap year. See Configure the two digit year cutoff Server Configuration Option for information about two-digit year values. Each specific datepart name and abbreviations for that datepart name will return the same value.Īn expression that can resolve to one of the following values:
0 kommentar(er)
