DateTime Functions

To get current date

SELECT GETDATE()

To get yesterday

SELECT DATEADD(d,-1,GETDATE())

First Day of Current Week

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)

Last Day of Current Week

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)

First Day of Last Week

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0)

Last Day of Last Week

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6)

First Day of Current Month

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

Last Day of Current Month

SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))

First Day of Last Month

SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))

Last Day of Last Month

SELECTDATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))

First Day of Current Year

SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)

Last Day of Current Year

SELECTDATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))

First Day of Last Year

SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))

Last Day of Last Year

SELECTDATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)))

Difference between DML DDL DCL and TCL?

DML:

DML is abbreviation of Data manipulation Language. It is useed to work with the data of the tables.

Basic Statements: SELECT, UPDATE, INSERT, delete statements.

DDL:

DDL is abbreviation of Data Defination Language. It is used to build and modify the structure of your tables and other objects in a database.

Basic Statements: CREATE, ALTER, RENAME, DROP and TRUNCATE statements.

DCL:

DCL is abbrivation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

Basic Statements: GRANT, REVOKE statements.

TCL:

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

Basic Statements:COMMIT, ROLLBACK statements.