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) |
Returns 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 Date and Time Data Types and Functions (Transact-SQL).