Commonly used Date Functions

          Several functions are available in SQL server, in those commonly used functions are

Date time functions:

• GETDATE()
• DATEDIFF()
• DATEPART()
• DATENAME()
• YEAR()
• MONTH()
• DAY()

GETDATE(): The GETDATE() function extracts current date time from the server.

Example: Select GETDATE()

DATEDIFF(): We can find the date difference between two date time elements.

            Syntax: DATEDIFF(datepart, startdate, enddate)

            Example:

            select DATEDIFF (YY,'2011-01-01 00:02:25','2012-01-01 00:02:25');

      select DATEDIFF (MM,'2011-01-01 00:02:25','2012-01-01 00:02:25');

      select DATEDIFF (DD,'2011-01-01 00:02:25','2012-01-01 00:02:25');

      select DATEDIFF (HH,'2011-01-01 00:02:25','2012-01-01 00:02:25');

      select DATEDIFF (MM,'2011-01-01 00:02:25','2012-01-01 00:02:25');

DATEPART(): We can select a part of date or time.

            Syntax: DATEPART(datepart, date)

            Example:

            SELECT DATEPART(YYYY,'2012-01-01 02:05:00');

SELECT DATEPART(MM,'2012-01-01 02:05:00');

SELECT DATEPART(DD,'2012-01-01 02:05:00');

SELECT DATEPART(HH,'2012-01-01 02:05:00');

SELECT DATEPART(MM,'2012-01-01 02:05:00');

SELECT DATEPART(SECOND,'2012-01-01 02:05:05');

DATENAME(): We can find the date name from date element.

            Example:

SELECT DATENAME(MM,'2012-01-01 02:05:00');

SELECT DATENAME(DW,'2012-01-01 02:05:00');

YEAR(): We can extract year part from the date time element.

            Example: SELECT YEAR('2012-01-01 02:05:00');

MONTH(): We can extract month part from the date time element.

            Example: SELECT MONTH('2012-01-01 02:05:00');

DAY(): We can extract day part from the date time element.

            Example: SELECT DAY('2012-01-01 02:05:00');

TCL Commands

TCL (Transactional Control Language):-

            It is used to manage different transactions occurring within a database.

Commit: Saves work done in transactions.

Rollback                   :           Restores database to original state since the last

Commit command in transactions.

Save Transaction   :           Sets a save point within a transaction.

DCL Commands

DCL (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.

Grant              :           Gives user’s access privileges to database

Revoke          :           Withdraws user’s access privileges to database given with

the Grant command.

DML Commands

DML (Data Manipulation Language):-

            It is used to extract, Insert, Update and Delete the data in database.

Select                        :           Extract data from a table.

Insert                         :           Insets data into a table.

Update                      :           Updates existing data from table.

Delete                        :           Deletes data from table.

DDL Commands

DDL (Data Definition Language):-

            It is used to create, modify, truncate and drop the table (Database object) in database.

Create             :           Creates tables in the database

Alter                :           Alters table tables in the database

Drop                :           Deletes table in the database.

Truncate         :            Deletes all records from a table and resets table identity to

initial value.

Sample Dept Table with Data

CREATE TABLE DEPT

(

DeptID INT

,Name VARCHAR(30)

,Location VARCHAR(30)

);

Sample data insertion Script:

INSERT INTO DEPT VALUES

(10,'Design','Hyd'),

(20,'Sales','Bang'),

(30,'Accounts','Che'),

(40,'Maintenance','Pune'),

(50,'Engineering','Delhi');

See Also: Sample Employee Table creation with data

BCP Command

      Using Bulk Copy Program (BCP) Utility, quickly we import and export large amounts of data. We can run the BCP command from the command prompt.

      Here i am going to explain, how to import data from a flat file to a data base table.

Source File:
File Path: D:\Programs\Product.txt

Target table:
ServerName      : KiranY
UserName        : sa
Password          : 123
DataBaseName : Sample

Table Name: PRODUCT

Table Creation Script:
          First we need to load source data into staging table. The stg table column data type should be character type. While the time of target table load (from stg table) we can convert to required data types.

                        CREATE TABLE STG_PRODUCT
                        (
                        PID VARCHAR(50)
                        ,PNAME VARCHAR(50)
                        ,PRICE VARCHAR(50)
                        )

BCP Command:
Syntax:
bcp <datbasename>..<tablename> in <pathname of the file with extension> -S<servername> -U<username> -P<password> -C -t"<column delimiter>" -r<row delimiter>

Command:
 bcp SAMPLE..STG_PRODUCT in D:\Programs\Product.txt -SKIRANY -Usa -P123 -c -t"," -r\n

Output: