Formatting Date Input - DateFOrmat

Formatting Date Input - Jan-7 or July 1 / British Way of Date Representation / British Date

DECLARE @datevar DATETIME;
SET @datevar = '01/07/2006';
SELECT @datevar AS DateVar;
SELECT datename(month, @datevar) AS month_name;
The above will return January

SET @datevar = '07/01/2006';
SELECT @datevar AS DateVar;
SELECT datename(month, @datevar) AS month_name;
The above will return July

If you are using British way of representing dates, then '01/07/2006' is 1-July. Use dateformat to change the input to dmy system
Set DateFOrmat dmy;
GO
DECLARE @datevar DATETIME;
SET @datevar = '01/07/2006';
SELECT @datevar AS DateVar;
SELECT datename(month, @datevar) AS month_name;
The above will return July

DECLARE @datevar DATETIME;
SET @datevar = '07/01/2006';
SELECT @datevar AS DateVar;
SELECT datename(month, @datevar) AS month_name;
The above will return Jan

DateFOrmat does not affect the display of date values

Read more »

Maximum Connections for SQL Server

All About Connections

@@MAX_Connections Returns the maximum number of simultaneous user connections allowed on an instance of SQL Server. The number returned is not necessarily the number currently configured

Select @@MAX_Connections as Max_Connections

No of attempted connections for SQL Server

Select @@Connections as TotalLoginAttempts

This will include either successful or unsuccessful since SQL Server was last started.

You can also check the same using sp_monitor System procedure

Exec sp_monitor

Read more »

Monitoring SQL SErver with SP_WHO

MONITORING SQL SERVER WITH SP_WHO

Exec sp_who
will provide information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine

Use the login to filter results for a particular user
Exec sp_who 'Login21'

Read more »

Current Database - SQL Server

What is the Current Database?

Select DB_NAME() as Curr_DB

Read more »

First day of the week - @@datefirst

The first day of the week will be 7 - Sunday (default for US English)

Select @@datefirst as FirstDay

You can set it by :
Set datefirst 1;

Read more »

DatePart - Get Part of the Date

-- Selecting the Current Year/Get Current Year/Separate Year Part from Date
Select DatePart(YY, GetDate()) as Current_Year

-- Selecting the Current Quarter/Get Current Quarter/Separate Quarter Part from Date
Select DatePart(QQ, GetDate()) as Current_Quarter

-- Selecting the Current Month/Get Current Month/Separate Month Part from Date
Select DatePart(MM, GetDate()) as Current_Month

-- Selecting the Name of Current Month/Get Name of Current Month/Separate Month Part from Date and display its Name
Select DateName(month, GetDate()) as Current_Month_Name

-- Selecting the Name of Current Month/Get Name of Current Month/Separate Month Part from Date and display its Name
Select DateName(day, GetDate()) as Current_Day

-- Selecting the Name of Current Month/Get Name of Current Month/Separate Month Part from Date and display its Name
Select DateName(wk, GetDate()) as Current_Week

Read more »

Maximum Level of Nesting in SQL Server Stored Procedure

Maximum Level of Nesting in SQL Server Stored Procedure

A Maximum of 32 levels of Nesting is allowed in SQL Server Stored Procedure. When the maximum of 32 is exceeded, the transaction is terminated.

Select @@NestLevel

Returns the nesting level of the current stored procedure execution (initially 0) on the local server

Keywords: @@NestLevel, Maximum Level of Nesting

Read more »