Must-Know SQL Functions For All .NET Developers


This article explains the most commonly used and must-know SQL Server functions and techniques that help and are handy in daily .NET Development and working with SQL Server Database projects. I found that working knowledge of these functions are very helpful and used very often in software development.

Article Covers

SYSDATETIME()

Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running.

Syntax : SYSDATETIME()

Return Type : datetime2(7)

Note : SYSDATETIME and SYSUTCDATETIME have more fractional seconds precision than GETDATE and GETUTCDATE. SYSDATETIMEOFFSET includes the system time zone offset. SYSDATETIME, SYSUTCDATETIME, and SYSDATETIMEOFFSET can be assigned to a variable of any of the date and time types.

SELECT SYSDATETIME()

Output : 2015-08-16 09:49:22.163

DATEADD()

Returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date.

Syntax : DATEADD (datepart, number, date)

Return Type : The return data type is the data type of the date argument, except for string literals.

Arguments :

datepart

Is the part of date to which an integer number 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
TZoffset tz
ISO_WEEK ISOWK, ISOWW

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.

SELECT DATEADD(D, 1, '2015-01-01')
SELECT DATEADD(DAY, -1, '2015-01-01')
SELECT DATEADD(M, 1, '2015-01-01')
SELECT DATEADD(YEAR, 1, '2015-01-01')

Output : 2015-01-02 00:00:00.000, 2014-12-31 00:00:00.000, 2015-02-01 00:00:00.000, 2016-01-01 00:00:00.000

DATENAME()

Returns a character string that represents the specified datepart of the specified date.

Syntax : DATENAME (datepart, date)

Return Type : nvarchar

Arguments :

datepart

Is the part of the date to return. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid.

SELECT DATENAME(M, '2015-01-01')

Output : January

DATEPART()

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

Syntax : DATEPART (datepart, date)

Return Type : int

SELECT DATEPART(yyyy,'2015-01-01') AS [Year],
DATEPART(mm,'2015-01-01') AS [Month],
DATEPART(dd,'2015-01-01') AS [Day]

Output : 2015 1 1

DATEDIFF()

Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.

Syntax : DATEDIFF ( datepart, startdate, enddate)

Arguments :

datepart

Is the part of startdate and enddate that specifies the type of boundary crossed. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid.

startdate

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. startdate is subtracted from enddate.

enddate

See startdate.

Return Type : int

SELECT DATEDIFF(DAY, '2015-01-01', '2015-01-02')
SELECT DATEDIFF(MONTH, '2015-01-01', '2015-12-02')
SELECT DATEDIFF(YEAR, '2015-01-01', '2025-01-02')

Output : 1, 11, 10

DAY()

Returns an integer representing the day (day of the month) of the specified date.

Syntax : DAY ( date )

Return Type : int

SELECT DAY('2015-01-01')

Output : 1

MONTH()

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

Syntax : MONTH ( date )

Return Type : int

SELECT MONTH('2015-01-01')

Output : 1

YEAR()

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

Syntax : YEAR ( date )

Return Type : int

SELECT YEAR('2015-01-01')

Output : 2015

DATALENGTH()

Returns the number of bytes used to represent any expression.

Syntax : DATALENGTH ( expression )

Return Type : int

Arguments :

expression

Is an expression of any data type.

SELECT DATALENGTH('BASIC FUNCTIONS OF SQL SERVER')

Output : 29

APP_NAME()

Returns the application name for the current session if set by the application.

Syntax : APP_NAME ( )

Return Type : nvarchar(128)

SELECT APP_NAME()

Output : Microsoft SQL Server Management Studio – Query

HOST_NAME()

Returns the workstation name.

Syntax : HOST_NAME ()

Return Type : nvarchar(128)

SELECT HOST_NAME()

Output : This will show you your workstation name.

SYSTEM_USER

Allows a system-supplied value for the current login to be inserted into a table when no default value is specified.

Syntax : SYSTEM_USER

Return Type : ncahr

SELECT SYSTEM_USER

Output : Will show the UserName used to log-in to SQL Server using Windows Authentication or SQL Authentication.

SCOPE_IDENTITY

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

Syntax : SCOPE_IDENTITY()

Return Type : numeric(38, 0)

SELECT SCOPE_IDENTITY()

Remark : SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns. SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

@@VERSION

Returns system and build information for the current installation of SQL Server.

Syntax : @@VERSION

Return Type : nvarchar

SELECT @@VERSION
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s