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 35 Minute(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 5 Second(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.

See also

• Canonical Functions