Date and Time Canonical Functions
Entity SQL includes date and time canonical functions.
Remarks
The following table shows the date and time Entity SQL canonical functions. datetime is a DateTime value.
| Function | Description |
|---|---|
AddNanoseconds(expression,number) |
Adds the specified number of nanoseconds to the expression.Arguments expression: DateTime, DateTimeOffset, or Time.number: Int32.Return Value The type of expression. |
AddMicroseconds(expression,number) |
Adds the specified number of microseconds to the expression.Arguments expression: DateTime, DateTimeOffset, or Time.number: Int32.Return Value The type of expression. |
AddMilliseconds(expression,number) |
Adds the specified number of milliseconds to the expression.Arguments expression: DateTime, DateTimeOffset, or Time.number: Int32.Return Value The type of expression. |
AddSeconds(expression,number) |
Adds the specified number of seconds to the expression.Arguments expression: DateTime, DateTimeOffset, or Time.number: Int32.Return Value The type of expression. |
AddMinutes(expression,number) |
Adds the specified number of minutes to the expression.Arguments expression: DateTime, DateTimeOffset, or Time.number: Int32.Return Value The type of expression. |
AddHours(expression,number) |
Adds the specified number of hours to the expression.Arguments expression: DateTime, DateTimeOffset, or Time.number: Int32.Return Value The type of expression. |
AddDays(expression,number) |
Adds the specified number of days to the expression.Arguments expression: DateTime or DateTimeOffset.number: Int32.Return Value The type of expression. |
AddMonths(expression,number) |
Adds the specified number of months to the expression.Arguments expression: DateTime or DateTimeOffset.number: Int32.Return Value The type of expression. |
AddYears(expression,number) |
Adds the specified number of years to the expression.Arguments expression: DateTime or DateTimeOffset.number: Int32.Return Value The type of expression. |
CreateDateTime(year,month,day,hour,minute,second) |
Returns a new DateTime value as the current date and time of the server in the server's time zone.Arguments year, month, day, hour, minute: Int16 and Int32.second: Double.Return Value A DateTime. |
CreateDateTimeOffset(year,month,day,hour,minute,second,tzoffset) |
Returns a new DateTimeOffset value as the current date and time of the server relative to the Coordinated Universal Time (UTC).Arguments year, month, day, hour, minute, tzoffset: Int32.second: Double.Return Value A DateTimeOffset. |
CreateTime(hour,minute,second) |
Returns a new Time value as the current time.Arguments hour and minute: Int32.second: Double.Return Value A Time. |
CurrentDateTime() |
Returns a DateTime value as the current date and time of the server in the server's time zone.Return Value A DateTime. |
CurrentDateTimeOffset() |
Returns the current date, time and offset as a DateTimeOffset.Return Value A DateTimeOffset. |
CurrentUtcDateTime() |
Returns a DateTime value as the current date and time of the server in the UTC time zone. Return Value A DateTime. |
Day(expression) |
Returns the day portion of expression as an Int32 between 1 and 31.Arguments A DateTime and DateTimeOffset.Return Value An Int32.Example -- The following example returns 12.Day(cast('03/12/1998' as DateTime)) |
DayOfYear(expression) |
Returns the day portion of expression as an Int32 between 1 and 366, where 366 is returned for the last day of a leap year.Arguments A DateTime or DateTimeOffset.Return Value An Int32. |
DiffNanoseconds(startExpression,endExpression) |
Returns the difference, in nanoseconds, between startExpression and endExpression.Arguments startExpression, endExpression: DateTime, DateTimeOffset, or Time. Note: startExpression and endExpression must be of the same type. Return Value An Int32. |
DiffMilliseconds(startExpression,endExpression) |
Returns the difference, in milliseconds, between startExpression and endExpression.Arguments startExpression, endExpression: DateTime, DateTimeOffset, or Time. Note: startExpression and endExpression must be of the same type. Return Value An Int32. |
DiffMicroseconds(startExpression,endExpression) |
Returns the difference, in microseconds, between startExpression and endExpression.Arguments startExpression, endExpression: DateTime, DateTimeOffset, or Time. Note: startExpression and endExpression must be of the same type. Return Value An Int32. |
DiffSeconds(startExpression,endExpression) |
Returns the difference, in seconds, between startExpression and endExpression.Arguments startExpression, endExpression: DateTime, DateTimeOffset, or Time. Note: startExpression and endExpression must be of the same type. Return Value An Int32. |
DiffMinutes(startExpression,endExpression) |
Returns the difference, in minutes, between startExpression and endExpression.Arguments startExpression, endExpression: DateTime, DateTimeOffset, or Time. Note: startExpression and endExpression must be of the same type. Return Value An Int32. |
DiffHours(startExpression,endExpression) |
Returns the difference, in hours, between startExpression and endExpression.Arguments startExpression, endExpression: DateTime, DateTimeOffset, or Time. Note: startExpression and endExpression must be of the same type. Return Value An Int32. |
DiffDays(startExpression,endExpression) |
Returns the difference, in days, between startExpression and endExpression.Arguments startExpression, endExpression: DateTime or DateTimeOffset. Note: startExpression and endExpression must be of the same type. Return Value An Int32. |
DiffMonths(startExpression,endExpression) |
Returns the difference, in months, between startExpression and endExpression.Arguments startExpression, endExpression: DateTime or DateTimeOffset. Note: startExpression and endExpression must be of the same type. Return Value An Int32. |
DiffYears(startExpression,endExpression) |
Returns the difference, in years, between startExpression and endExpression.Arguments startExpression, endExpression: DateTime or DateTimeOffset. Note: startExpression and endExpression must be of the same type. Return Value An Int32. |
GetTotalOffsetMinutes(datetimeoffset) |
Returns the number of minutes that the datetimeoffset is offset from GMT. This is generally between +780 and -780 (+ or - 13 hrs). Note: This function is supported in SQL Server 2008 only. Arguments A DateTimeOffset.Return Value An Int32. |
Hour(expression) |
Returns the hour portion of expression as an Int32 between 0 and 23.Arguments A DateTime, Time and DateTimeOffset.Example -- The following example returns 22.Hour(cast('22:35:5' as DateTime)) |
Millisecond(expression) |
Returns the milliseconds portion of expression as an Int32 between 0 and 999.Arguments A DateTime, Time and DateTimeOffset.Return Value An Int32. |
Minute(expression) |
Returns the minute portion of expression as an Int32 between 0 and 59.Arguments A DateTime, Time or DateTimeOffset.Return Value An Int32.Example -- The following example returns 35Minute(cast('22:35:5' as DateTime)) |
Month(expression) |
Returns the month portion of expression as an Int32 between 1 and 12.Arguments A DateTime or DateTimeOffset.Return Value An Int32.Example -- The following example returns 3.Month(cast('03/12/1998' as DateTime)) |
Second(expression) |
Returns the seconds portion of expression as an Int32 between 0 and 59.Arguments A DateTime, Time and DateTimeOffset.Return Value An Int32.Example -- The following example returns 5Second(cast('22:35:5' as DateTime)) |
TruncateTime(expression) |
Returns the expression, with the time values truncated.Arguments A DateTime or DateTimeOffset.Return Value The type of expression. |
Year(expression) |
Returns the year portion of expression as an Int32 YYYY.Arguments A DateTime and DateTimeOffset.Return Value An Int32.Example -- The following example returns 1998.Year(cast('03/12/1998' as DateTime)) |
These functions will return null if given null input.
Equivalent functionality is available in the Microsoft SQL Client Managed Provider. For more information, see SqlClient for Entity Framework Functions.