Date and Time Functions in SQL SERVER Part I || CURRENT_TIMESTAMP and DATEADD
This video will provide you the details of each Date and Time Data Types and Function in SQL Server 2014. I'll be walking through each and every kinds of build in function which are available in SQL Server in my next videos.
Date and Time Data Types and Functions :-
CURRENT_TIMESTAMP (Transact-SQL)
Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.
This function is the ANSI SQL equivalent to GETDATE.
Syntax
CURRENT_TIMESTAMP
Arguments
Takes no arguments.
Return Type
Datetime
Remarks
Transact-SQL statements can refer to CURRENT_TIMESTAMP anywhere they can refer to a datetime expression.CURRENT_TIMESTAMP is a nondeterministic function. Views and expressions that reference this column cannot be indexed.
*********************************************************************
DATEADD (Transact-SQL)
Returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date.
Syntax
DATEADD (datepart , number , date )
Arguments
datepart
Is the part of date to which an integernumber is added. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid.
datepart Abbreviations
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw, w
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns
number
Is an expression that can be resolved to an int that is added to a datepart of date. User-defined variables are valid.
If you specify a value with a decimal fraction, the fraction is truncated and not rounded.
date
Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable, or string
literal. If the expression is a string literal, it must resolve to a datetime. To avoid ambiguity, use four-digit years. For information about two-digit years,
Return Types
The return data type is the data type of the date argument, except for string literals.
The return data type for a string literal is datetime. An error will be raised if the string literal seconds scale is more than three positions (.nnn) or contains the time zone offset part.
Return Value
datepart Argument
dayofyear, day, and weekday return the same value.
Each datepart and its abbreviations return the same value.
If datepart is month and the date month has more days than the return month and the date day does not exist in the return month, the last day of the return month is returned. For example, September has 30 days; therefore, the two following statements return 2006-09-30 00:00:00.000:
SELECT DATEADD(month, 1, '2006-08-30');
SELECT DATEADD(month, 1, '2006-08-31');
number Argument
The number argument cannot exceed the range of int. In the following statements, the argument for number exceeds the range of int by 1. The following error message is returned: "Msg 8115, Level 16, State 2, Line 1. Arithmetic overflow error converting expression to data type int."
SELECT DATEADD(year,2147483648, '2006-07-31');
SELECT DATEADD(year,-2147483649, '2006-07-31');
date Argument
The date argument cannot be incremented to a value outside the range of its data type. In the following statements, the number value that is added to the date value exceeds the range of the date data type. The following error message is returned: "Msg 517, Level 16, State 1, Line 1 Adding a value to a 'datetime' column caused overflow."
SELECT DATEADD(year,2147483647, '2006-07-31');
SELECT DATEADD(year,-2147483647, '2006-07-31');
Return Values for a smalldatetime date and a second or Fractional Seconds datepart
The seconds part of a smalldatetime value is always 00. If date is smalldatetime, the following apply:
• If datepart is second and number is between -30 and +29, no addition is performed.
• If datepart is second and number is less than-30 or more than +29, addition is performed beginning at one minute.
• If datepart is millisecond and number is between -30001 and +29998, no addition is performed.
• If datepart is millisecond and number is less than -30001 or more than +29998, addition is performed beginning at one minute.