SQL (Structured Query Language)

 --- to Add New Column in Table---
ALTER TABLE dbo.[{Table Name}] add [{Column Name}] Nvarchar Null 

 --- to DELETE Column from data Table---
 ALTER TABLE [{Table Name}] DROP COLUMN [{Column Name}]; 

 --ALTER COLUMN data TYPE---
 ALTER TABLE [dbo].[{Table Name}] ALTER COLUMN [{Column Name}] Data-type Null 

 --- Rename Column name in data Table---

 exec sp_rename '[{Table Name}].[{Column Name}]','New Column Name','COLUMN' 

 ---****You can easily change the column name without recreating the table or losing ur records.
 ---Tools---
-----> Options 
--> Designers 
--> Table and database designers 
--> Uncheck 
-->Prevent saving changes that required table re-creation.


Cast([Receipt Date] as date)'Date' 

--Return '2021-12-17'

Cast([Receipt Date] as Time)'Time'

--Return '14:03:24' 

 ------- Add days into current date for future date or past Date 
Select DATEAdd(DAY,10, Cast(Getdate() as date)) 'Day 10 Added' 

Select DATEAdd(Month,-3, Cast(Getdate() as date)) 'Previous 3 Month' 

Select DATEAdd(YEAR,1, Cast(Getdate() as date)) 'Year 1 Added' 


 ----Day of Year(Number) 
Select DATEPART(DAYOFYEAR, Getdate())

 ----Day of Week 
Select DATEName(WEEKDAY, Getdate()) 
Select DATEName(DW, Getdate()) 

 -----date difference 
select DATEDIFF(day,'1983-03-14',Getdate()) 'Days Count' 

select DATEDIFF(yy,'1983-03-14',Getdate()) 'Year' 

 ---'Start date of last 4 Month' 
Select DATEAdd(Month,-4, cast(Getdate()-Day(Getdate())+1 as date)) 'Start date of last 4 Month' 

 ---'First date End Date of the Month' 
Select cast(Getdate()-Day(Getdate())+1 as date) 'Start date of the Month' 

Select EOMONTH(Getdate()) 'End date of the Month' 

 -----Replace Foumula 

Replace([Column Name],'{Removing Part of String}','{New Part to add in String}')

TRANSPOSE ARRAY FORMULA

TRANSPOSE

---

About : Returns the transpose of an array

Category : Lookup
Location : Built-In

Transpose Formula

=Transpose(A1:C5) + Ctrl+Shift+Enter




Check out the Excel Shortcut Keys

What Does It Do ?

---

This function copies data from a range, and places in it in a new range, turning it so that the data originally in columns is now in rows, and the data originally in rows is in columns.

The transpose range must be the same size as the original range.

The function needs to be entered as an array formula.

To enter an array formula you must first highlight all the cells where the formula is required.
Next type the formula, such as =TRANSPOSE(A1:A5).
Finally press Ctrl+Shift+Enter to confirm it.

If changes need to be made to the formula, the entire array has to be highlighted, the edits can then be made and the Ctrl+Shift+Enter used to confirm it.

Syntax

---

=TRANSPOSE(Range)



Formatting

---

No special formatting is needed.



Check out the Excel Shortcut Keys

Share This on :

Choose Function

Choose Function

---

About: Chooses a value from a list of values



Category: Lookup
Location: Built-in

What Does It Do?

---

This function picks from a list of options based upon an Index value given to by the user.

Syntax
 =CHOOSE(UserValue, Item1, Item2, Item3 through to Item29)

Formatting

---

No special formatting is required.

Lets understand with Example

---

The following table was used to calculate the medals for athletes taking part in a race.
The Time for each athlete is entered.
The =RANK() function calculates the finishing position of each athlete.
The =CHOOSE() then allocates the correct medal.
The =IF() has been used to filter out any positions above 3, as this would cause
the error of #VALUE to appear, due to the fact the =CHOOSE() has only three items in it.

---

Share This On: