Date and Time Functions

 

The .NET Framework Data Provider for SQL Server (SqlClient) provides date and time functions that perform operations on a System.DateTime input value and return a string, numeric, or System.DateTime value result. These functions are in the SqlServer namespace, which is available when you use SqlClient. A provider's namespace property allows the Entity Framework to discover which prefix is used by this provider for specific constructs, such as types and functions.The following table shows the SqlClient date and time functions.

Function

Description

DATEADD(datepart, number, date)

Returns a new DateTime value that is based on adding an interval to the specified date.

Arguments

datepart: A String that represents the part of the date on which to return a new value.

number: The Int32, Int64, Decimal, or Double value used to increment datepart.

date: An expression that returns a DateTime, or DateTimeOffset, or Time with precision = [0-7], or a character string in a date format.

Return Value

A new DateTime, or DateTimeOffset, or Time value with precision = [0-7].

Example

SqlServer.DATEADD('day', 22, cast('6/9/2006' as DateTime))

DATEDIFF(datepart, startdate, enddate)

Returns the number of date and time boundaries crossed between two specified dates.

Arguments

datepart: A String that represents the part of the date to calculate the difference.

startdate: A starting date for the calculation is an expression that returns a DateTime, or DateTimeOffset, or Time value with precision = [0-7], or a character string in a date format.

enddate: An ending date for the calculation is an expression that returns a DateTime, or DateTimeOffset, or Time value with precision = [0-7], or a character string in a date format.

Return Value

An Int32.

Example

SqlServer.DATEDIFF('day', cast('6/9/2006' as DateTime),

cast('6/20/2006' as DateTime))

DATENAME(datepart, date)

Returns a character string representing the specified datepart of the specified date.

Arguments

datepart: A String that represents the part of the date on which to return a new value.

date: An expression that returns a DateTime, or DateTimeOffset, or Time value with precision = [0-7], or a character string in a date format.

Return Value

The character string representing the specified datepart of the specified date.

Example

SqlServer.DATENAME('year', cast('6/9/2006' as DateTime))

DATEPART(datepart, date)

Returns an integer that represents the specified datepart of the specified date.

Arguments

datepart: A String that represents the part of the date on which to return a new value.

date: An expression that returns a DateTime, or DateTimeOffset, or Time value with precision = [0-7], or a character string in a date format.

Return Value

The specified datepart of the specified date, as an Int32.

Example

SqlServer.DATEPART('year', cast('6/9/2006' as DateTime))

DAY(date)

Rreturns the day of the specified date as an integer.

Arguments

date: An expression of type DateTime or DateTimeOffset with precision = 0-7.

Return Value

The day of the specified date as an Int32.

Example

SqlServer.DAY(cast('6/9/2006' as DateTime))

GETDATE()

Produces the current date and time in SQL Server internal format for datetime values.

Return Value

The current system date and time as a DateTime with a precision of 3.

Example

SqlServer.GETDATE()

GETUTCDATE()

Produces the datetime value in UTC (Coordinated Universal Time or Greenwich Mean Time) format.

Return Value

The DateTime value with a precision of 3 in UTC format.

Example

SqlServer.GETUTCDATE()

MONTH(date)

Returns the month of the specified date as an integer.

Arguments

date: An expression of type DateTime or DateTimeOffset with precision = 0-7.

Return Value

The month of the specified date as an Int32.

Example

SqlServer.MONTH(cast('6/9/2006' as DateTime))

YEAR(date)

Returns the year of the specified date as an integer.

Arguments

date: An expression of type DateTime or DateTimeOffset with precision = 0-7.

Return Value

The year of the specified date as an Int32.

Example

SqlServer.YEAR(cast('6/9/2006' as DateTime))

SYSDATETIME()

Returns a DateTime value with a precision of 7.

Return Value

A DateTime value with a precision of 7.

Example

SqlServer.SYSDATETIME()

SYSUTCDATE()

Produces the datetime value in UTC (Coordinated Universal Time or Greenwich Mean Time) format.

Return Value

The DateTime value with precision = 7 in UTC format.

Example

SqlServer.SYSUTCDATE()

SYSDATETIMEOFFSET()

Returns a DateTimeOffset with a precision of 7.

Return Value

A DateTimeOffset value with precision of 7 in UTC format.

Example

SqlServer.SYSDATETIMEOFFSET()

For more information about the date and time functions that SqlClient supports, see the documentation for the SQL Server version that you specified in the SqlClient provider manifest:

SQL Server 2000

SQL Server 2005

SQL Server 2008

Date and Time Functions (Transact-SQL)

Date and Time Functions (Transact-SQL)

Date and Time Functions (Transact-SQL)

Show: