Date and Time Functions of SQL Server 2008 R2
This article will describe the date and time functions which are available in the latest version of SQL Server 2008R2. Article will also show all the syntaxes which are required to use it in scenarios wherever required. This article is useful to all those who are working in Sql server database.
Hai Friends,
Lot many times I have seen that the friends get confused about the date and time function when they use in their front end applications.
Also many times, I have seen the peoples ask the questions related to conversion of date and time function due to some error they get in their queries which can be either at their front end application or at their backend database.
So today I decided to write an article which will help to all those functions which are in the latest version of SQL Server (SQL Server 2008 R2) including all the functions which were the part of Sql Server 2005 and in Sql Server 2008.
So I am giving the brief descriptions about those functions as below so that whenever anyone gets stuck with their issues related to the date and Time functions, they can just go through this article and get their problem resolved without asking the questions in the forum regarding the functions.Assumption:
I have taken the assumption here that all the default date and time will be the same as the system date and time. So the default current date and time is dependent upon the Operating system of the system where the instance of the Sql server is running.All Date and Time Functions of SQL Server 2008 R2
1. Functions which calculate the part of the date and time
a. DATENAME: This function takes the datepart and date as the input and returns the character sting as the datepart.
datepart can be – yy, yyyy,mm, mm, d,dd, ww, hh,mi,ss,ms etc
Syntax:
DateName( datepart, date);
b. DATEPART: This function returns an integer value which is the datepart of the entered date.
Syntax:
DATEPART ( datepart , date )
c. DAY: It returns an integer value as the day. E.g. 1 for Sunday, 2 for Monday etc.
Syntax:
DAY ( date )
d. MONTH: This function returns an integer value of the month. It's range is between 1 to 12. 1 indicates January and 12 for December.
Syntax:
MONTH ( date )
e. YEAR: It returns an integer as the year. So if the user enters the current date, it will return the year as 2011.
Syntax:
YEAR ( date )
2. There are few high precision System date and time functions
a. SYSDATETIME: This is the null parameter high precision function to get the datetime value up to the nanoseconds precision value. It doesn't include time one offset.
Syntax:
SYSDATETIME ()
b. SYSDATETIMEOFFSET: This function returns a datetimeoffset (7) value with high precision. It include the time zone offset.
Syntax:
SYSDATETIMEOFFSET ( )
c. SYSUTCDATETIME: It returns the UTC time (Coordinated Universal Time) in datetime2 (7) format.
Syntax:
SYSUTCDATETIME ();
3. There are some Lower-Precision System Date and Time Functions
a. CURRENT_TIMESTAMP: This function returns a datetime2 (7) with low precision as it calculate the date and time value till the second precision. The time zone offset is not included with the returned value.
Syntax:
CURRENT_TIMESTAMP
b. GETDATE: It returns current date and time with low precision value in the format datetime2 (7).
Syntax:
GETDATE ( )
c. GETUTCDATE: It returns the datetime values in the UTC time (Coordinated Universal Time) format without adding the time zone offset.
Syntax:
GETUTCDATE ( )
4. Functions That Get Date and Time Difference
a. DATEDIFF: This is the special function to get the difference between dates in a specific format. This function takes the datepart, startdate and enddate s the parameter. The user can get the returned value in any datepart.
Syntax:
DATEDIFF (datepart, startdate, enddate)
5. Functions That Modify Date and Time Values
a. DATEADD: The special function and mostly used function which can add the dates may be future date or past dates based on their datepart.
It mainly returns a new datetime value by adding datepart of the specified date.
Syntax:
DATEADD (datepart, number, date)
b. SWITCHOFFSET: To change the time zone offset of a DATETIMEOFFSET value and preserves the UTC value, we can use this function.
Syntax:
SWITCHOFFSET (DATETIMEOFFSET, time_zone)
c. TODATETIMEOFFSET: It transforms a datetime2 value into the datetimeoffset value.
Syntax:
TODATETIMEOFFSET (expression, time_zone)
6. Functions That Set or Get Session Format
a. @@DATEFIRST: To get the current date value, for the current session, this global variable is used.
Syntax:
@@DATEFIRST
b. SET DATEFIRST: To get the first day of the week, we can use this function, Its value varies between 1 through 7.
Syntax:
SET DATEFIRST { number | @number_var }
c. SET DATEFORMAT: To set the order of the datepart for the entered datetime, we can use this property value.
Syntax:
SET DATEFORMAT { format | @format_var }
d. @@LANGUAGE: this global variable is used to get the name of the language which is currently being used.
Syntax:
@@LANGUAGE
e. SET LANGUAGE: To sets the language environment for the current session and for the system generated messages, we can use this function.
Syntax:
SET LANGUAGE { [ N ] 'language' | @language_var }
f. SP_HELPLANGUAGE: This is the stored procedure which returns all the information about date formats of all supported languages.
Syntax:
sp_helplanguage [ [ @language = ] 'language' ]
7. Functions That Validate Date and Time Values
a. ISDATE: This is the unique function for the validation of the date. It validates whether a datetime or the input expression is in a valid date format or not. It always returns Boolean true or false.
Syntax:
ISDATE ( expression )
Hope this article will be helpful for all those who gets stuck with the datetime functions and looking around the solution.