SQL Tip of the day

Tip 1
I need only Date part from date time. Different formats and examples

SELECT GETDATE()
SELECT CONVERT(char(20), GETDATE(), 101) --mm/dd/yyyy
SELECT CONVERT(char(20), GETDATE(), 1) --mm/dd/yy
SELECT CONVERT(char(20), GETDATE(), 0) --Month date yyyy hh:miAM (this is the default style)
SELECT CONVERT(char(20), GETDATE(), 1) --mm/dd/yy
SELECT CONVERT(char(20), GETDATE(), 112) --yyyymmdd
Tip 2
I want to group data based on month, year and day

SELECT GETDATE()
SELECT YEAR(GETDATE()),MONTH(GETDATE()), DAY(GETDATE())
Tip 3
I want to group data based on Hour, Minute and Second

SELECT GETDATE()
SELECT DATEPART(HOUR,GETDATE()), DATEPART(MINUTE,GETDATE()), DATEPART(SECOND,GETDATE())
Tip 4
COALESCE Function - Returns the first nonnull expression among its arguments

SELECT COALESCE(NULL+'Test',NULL,'Test1')--Test1
SELECT COALESCE('Test2'+'Test',NULL,'Test1')--Test2Test
SELECT COALESCE('Test2'+NULL,NULL,'Test1')--Test1
Tip 5
What is output for SELECT 'TEST'+ NULL
 
Cancatenation of anything to NULL will give only NULL output. Using ISNULL would fix it.
SELECT 'TEST'+ ISNULL(NULL,'')
Tip 6
Explore more on Datatype Date, Time, DateTime, SmallDateTime and Choose datatype based on need. This would help reduce space and appropriate use of datatypes.

IF OBJECT_ID ('TEST') IS NOT NULL DROP TABLE TEST
CREATE TABLE TEST
(
DateCol             Date,
TimeCol            Time,
DateTimeCol     DateTime,
SmallDTCol        SmallDateTime
)

INSERT INTO TEST (DateCol,TimeCol,DateTimeCol,SmallDTCol)
VALUES(GETDATE(), GETDATE(),GETDATE(),GETDATE())

SELECT * FROM TEST