SQL DateTime functions

 Return Current date in SQL

SELECT GETDATE();

Return the current UTC date and time:

SELECT GETUTCDATE();

Get the month of date

SELECT month('2017/08/13 09:08') AS DayOfMonth;

Get the Year of Date

SELECT YEAR('2024/11/24 09:08') AS Year;

Get Month Name 

SELECT DATENAME(month, GETDATE()) AS monthname;

Find the Weekdays as today

SELECT DATENAME(WEEKDAY, GETDATE()) AS DatePartString;

Add a year to date & return the date: 

SELECT DATEADD(year, 1, '2024/09/25') AS YearAdd;

Subtract 2 months from  date & return the date:

SELECT DATEADD(month, -2, '2024/09/25') AS MonthSub;

Get the difference of month 

SELECT DATEPART(day, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)))

Get the End of month date

SELECT DAY(EOMONTH(GETDATE()))

Get only todays date and month

select convert(varchar(5),getdate(),110)

Displaying the multiple rows data in single Row in SQL

 You have a scenario to show the multiple rows data into the  single rows like below.

In Above image you can see how the  multiple rows of above tables data is transformed into horzotally and make all the records into one row.

Just take the example of 1 data and taking it is making the horizontally as respect to the TIN wise.

where for TIN 1000 having the four different records but belongs to the one costumer only.

So first provide the row number to all the records like below.

SELECT SetId, AppCode, AppEventId, EventId,ValueData,phone,[Address], ROW_NUMBER()  OVER( PARTITION BY  AppCode  ORDER BY AppCode

) RowNumber

INTo #mineTable

FROM  dbo.valueDataCheck

In Above Code there we created a new temp table which will maintain the row number with other records and you can see in below line how we provide numbers to all the records

select * from #mineTable

Now main code how we will convert the row to horizontally like given in screen.

SELECT SetId, AppCode as TIN, AppEventId, EventId

  ,max(CASE WHEN RowNumber = 1 THEN ValueData END) AS val1

  ,max(CASE WHEN RowNumber = 2 THEN ValueData END) AS val2

  ,max(CASE WHEN RowNumber = 3 THEN ValueData END) AS val3

   --,max(CASE WHEN RowNumber = 4 THEN ValueData END) AS val4

   ,max(CASE WHEN RowNumber = 1 THEN phone END) AS phonenumber1

  ,max(CASE WHEN RowNumber = 2 THEN phone END) AS phonenumber2

  ,max(CASE WHEN RowNumber = 3 THEN phone END) AS phonenumber3

  -- ,max(CASE WHEN RowNumber = 4 THEN phone END) AS phonenumber4

   ,max(CASE WHEN RowNumber = 1 THEN [Address] END) AS add1

  ,max(CASE WHEN RowNumber = 2 THEN [Address] END) AS add2

  ,max(CASE WHEN RowNumber = 3 THEN [Address] END) AS add3

 -- ,max(CASE WHEN RowNumber = 4 THEN [Address] END) AS add4

 

   FROM #mineTable

   GROUP BY SetId,AppCode,AppEventId,EventId

  

   DROP TABLE #mineTable

It will help you to get the all the records horizontally . Dont forget to drop tables.

Difference between question of sql server

Most frequently asked Difference Between Question for SQL. (Click on each to see the Details)


1. Difference between Varchar and NVarchar Datatype in Sql Server

2. Difference between Char and varchar Datatype in Sql Server

3. Difference between Primary Key and Unique Key Datatype in Sql Server

Difference between Table and View

Below list is major difference between Table and View in SQL Server;

Table	View
Table contains Data.	View doesn’t contains data.
Table is physically existence.	View doesn’t physically existence. It’s like virtual table.
You can perform insert, update, delete and Select for it.

Difference between Where and Having

Below list is major difference between where and having in SQL Server;

Where	Having
The WHERE clause selects rows before grouping	HAVING clause selects rows after grouping.
Where clause states the criteria which individual records should  meet to be selected by a query , used without the GROUP BY clause.	HAVING clause cannot be used without the GROUP BY clause.
WHERE clause cannot contain aggregate functions	HAVING clause can contain aggregate functions.
WHERE is used before the aggregation takes place.	HAVING is used to check conditions after the aggregation takes place.
select City, CNT=Count(1) From Address Where State = 'MA' Group By City	select City, CNT=Count(1) From Address Where State = 'MA' Group By City Having Count(1)>5

Difference between Delete and Truncate

Below list is major difference between Delete and Truncate in SQL Server;

	Delete	Truncate
Command Type	It is DML command Type.	It is DDL command Type.
Filter	Specify the filter for command like where.	Can’t use Where clause.
Reset Identity	Delete command won’t reset the identity Column.	Truncate command  reset the identity Column.
Performance	Slower than truncate as it keeps logs	High performance  as it won’t keep log.
RollBack	Rollback is possible.	Rollback is not possible.
Activate Trigger	Delete activates a trigger.	TRUNCATE cannot activate a trigger
Locked Behaviour	DELETE statement lock row, each row in the table is locked for deletion.	TRUNCATE always locks the table and page but not each row.

Difference between Datetime and Datetime2

Below list is major difference between Datetime and Datetime2 in Sql Server;

	Datetime	Datetime2
Storage Size	8 byte	6-8 Byte.
Min Value	1753-01-01 00:00:00	0001-01-01 00:00:00
Current Datetime	GetDate() - returns DB Current DateTime of datetime Data Type	SYSDATETIME()  - returns Current DateTime of DateTime2 Data Type
+/- Operator	+/- operator works, e.g  GetDate() + 1	+/- operator not works; DateAdd function required to add date.
Syntax	Declare @now datetime	Declare @now datetime2(7)

Difference between Store Procedure and User defined Function

Below list is major difference between Store Procedure and User defined Function in Sql Server;

	Store Procedure	User Defined Function
DML Statement	Store procedure supports the DML operations. Like Insert update and Delete.	Its not support DML statements.
Return	Store procedure may or may not return the value	Function must return a value.
Transaction	Transactions allows in Store procedure.	Transactions not allow in user defined functions.
Call by Store Procedure	Store Procedure can call other store procedure and function.	Function cannot call store procedure.
Call from select Statement	Procedures can’t be called from Select statement.	Function can be called from select statement.
Parameters	It Support both input and output Parameters.	It support input but not supports the output parameters.
Exception Handling	Its allow try and catch.	Its not allow try and catch.
Temporary Table	Its allow Temporary table.	Its not allow Temporary table.

Difference between Temporary Table and Table Variable

Below list is major difference between Temporary Table and Table Variable Datatype in Sql Server;

	Temporary Table	Table Variable
Change of Structure	We can change the structure of temporary table after creation of it, we can use DDL statements ALTER, CREATE, DROP	We can’t change the structure of table variable. It doesn’t support DDL Statement like ALTER, CREATE, DROP.
User defined Function	User defined function not allowed temporary table.	Table variables are allowed in User Defined functions.
Transaction	Temporary table support the explicit transactions. .	It not supports the explicit transactions.
Index	Temporary table allow adding explicit indexes after declarations.	Table variables not allow adding explicit indexes after declarations.
Scope	Scope of the Local Temporary Table is in the session in which it created and they are dropped automatically once the session ends and we can also drop them explicitly.	Scope of the Table variable is the Batch or Stored Procedure in which it is declared. and they can’t be dropped explicitly,
Syntax	Creating the temporary Table -- Create Temporary Table CREATE TABLE Employee (EmpId INT, Name VARCHAR(50)) --Insert records INSERT INTO #Employee VALUES(1,'BOB') --Reterive the records SELECT * FROM #Employe --DROP Temporary Table DROP TABLE #Employee	Using Table variables; -- Create Table Variable DECLARE @Employee TABLE (  Id INT,  Name VARCHAR(50)   ) --Insert Two records INSERT INTO @Employee VALUES(1,'BOb') --Reterive the records SELECT * FROM @Employee GO