Export (0) Print
Expand All

Date and Time Canonical Functions

Entity SQL includes date and time canonical functions. 

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 UTS 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.

Bb738563.note(en-us,VS.100).gifNote:
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.

Bb738563.note(en-us,VS.100).gifNote:
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.

Bb738563.note(en-us,VS.100).gifNote:
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.

Bb738563.note(en-us,VS.100).gifNote:
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.

Bb738563.note(en-us,VS.100).gifNote:
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.

Bb738563.note(en-us,VS.100).gifNote:
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.

Bb738563.note(en-us,VS.100).gifNote:
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.

Bb738563.note(en-us,VS.100).gifNote:
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.

Bb738563.note(en-us,VS.100).gifNote:
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).

Bb738563.note(en-us,VS.100).gifNote:
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.

Community Additions

ADD
Show:
© 2014 Microsoft