SQL - Date/Time to String Conversion by using Convert ( ) function

In this blog, we will learn how to convert the date/time into string by using Convert () SQL function and explore the different style of date/time format in conversion.

Convert () is used to convert a given value of one data type to another data type and it works same as Cast () function except it allows third parameter for date /time format style eg. yyyy-mm-dd, dd mm yyyy etc.

Here is an example for Converting from datetime with default style (0, 100) and without passing style options and all three return same output (mon dd yyyy hh:miAM (or PM))

Select Convert(varchar,getdate())

Select Convert(varchar,getdate(), 100)

Select Convert(varchar,getdate(), 0)

Output:

Jul 31 2019  4:18PM

Two Digit Years Style (yy):

The below sql script convert the datetime into date string with 2 digits year part

Select Convert(varchar,getdate(), 1)

Output:

07/31/19

Select Convert(varchar,getdate(), 2)

Output:

19.07.31

Below is a list of formats and an output

Style /Format	Output
1	mm/dd/yy
2	yy.mm.dd
3	dd/mm/yy
4	dd.mm.yy
5	dd-mm-yy
6	dd mon yy
7	Mon dd, yy
8	hh:mi:ss
10	mm-dd-yy
11	yy/mm/dd
12	yymmdd
14	hh:mi:ss:mmm

Four Digit Years Style (yyyy):

The below sql script convert the datetime to date string with 4 digits year part

Select Convert(varchar,getdate(), 101)

Output:

07/31/2019

Select Convert(varchar,getdate(), 102)

Output:

2019.07.31

Below is a list of formats and an output

Style /Format	Output
100	mon dd yyyy hh:miAM
101	mm/dd/yyyy
102	yyyy.mm.dd
103	dd/mm/yyyy
104	dd.mm.yyyy
105	dd-mm-yyyy
106	dd mon yyyy
107	Mon dd, yyyy
108	hh:mi:ss
109	mon dd yyyy hh:mi:ss:mmmAM (or PM)
110	mm-dd-yyyy
111	yyyy/mm/dd
112	yyyymmdd
113	dd mon yyyy hh:mi:ss:mmm(24h)
114	hh:mi:ss:mmm(24h)
120	yyyy-mm-dd hh:mi:ss(24h)
121	yyyy-mm-dd hh:mi:ss.mmm(24h)

SQL - String to Date/Time Conversion by using Convert ( ) function

In this blog, we will learn how to convert the string into Date/Time type by using Convert () SQL function.

Convert () is used to convert a given value of one data type to another data type.

Convert into DateTime type by using Convert () function:

The below SQL script is used to convert date string into DateTime type

Select CONVERT (Datetime,'02/12/2019')

Output:

2019-02-12 00:00:00.000

If input value is not valid date sting, it throw below exception

Example:

Select CONVERT (Datetime,'12S2019')

Exception:

Conversion failed when converting date and/or time from character string.

If input string is date string but it is invalid date, it throw below exception

Example: 31th day is invalid for February month

Select CONVERT (Datetime,'02/31/2019')

Exception:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Convert into Date type by using Convert () function:

The below SQL script is used to convert input string into Date type (only date information)

Select CONVERT (Date,'02/18/2019')

Output:

2019-02-18

Convert into time type by using Convert () function:

The below SQL script is used to convert input string into time type

Select CONVERT (Time,'02/18/2019 09:02')

Output:

09:02:00.0000000

Select CONVERT (Time,'09PM')

Output:

21:00:00.0000000

SQL - String to Date/Time Conversion by using Cast ( ) function

In this blog, we will learn how to convert the string into Date/Time type by using Cast () SQL function.

Cast () is used to convert a given value of one data type to another data type and Convert () function works almost same as Cast () function except syntax different.

Convert into DateTime type by using Cast () function:

The below SQL script is used to convert date string into DateTime type

Select CAST ('12/31/2019' AS DateTime)

Output:

2019-12-31 00:00:00.000

If input value is not valid date sting, it throw below exception

Example:

Select CAST ('12S2019' AS DateTime)

Exception:

Conversion failed when converting date and/or time from character string.

If input string is date string but it is invalid date, it throw below exception

Example: 31th day is invalid for February month

Select CAST ('02/31/2019' AS DateTime)

Exception:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Convert into Date type by using Cast () function:

The below SQL script is used to convert input string into Date type (only date information)

Select CAST ('02/18/2019' AS date)

Output:

2019-02-18

Convert into time type by using Cast () function:

The below SQL script is used to convert input string into time type

Select CAST ('02/18/2019 09:02' AS time)

Output:

09:02:00.0000000

Select CAST ('09pm' AS time)

Output:

21:00:00.0000000

SQL DEFAULT Constraint

SQL DEFAULT Constraint provides a default value to a column when adding new record and no value provided for a column.

Here is an example:  OrderDate Column has default value  GetDate() SQL function. If user doesn’t provide value for OrderDate, by default it will take today date in case of blank

Syntax: Create DEFAULT Constraint on Create Table:

The below SQL script creates a Default constraint for OrderDate Column

CREATE TABLE Orders (

    OrderID int NOT NULL,

    OrderNumber int NOT NULL,

    StatusID int, 

    OrderAmount Decimal(10,2),

    OrderDate date DEFAULT GETDATE(),

    PRIMARY KEY (OrderID),

    CONSTRAINT FK_OrderOrderStatus FOREIGN KEY (StatusID)

    REFERENCES OrderStatus(StatusID)

);

Syntax: Create DEFAULT Constraint on Alter Table:

The below SQL script creates a Default constraint for OrderDate Column of a table, which is already created

ALTER TABLE dbo.Orders

ADD CONSTRAINT df_OrderDate

DEFAULT GETDATE() FOR OrderDate;

Syntax: Drop DEFAULT Constraint:

The below SQL script uses to drop a Default constraint from table

ALTER TABLE Orders

DROP CONSTRAINT df_OrderDate;

SQL Check Constraint

A SQL Check Constraint is defined a condition on a column of table that must satisfy then it allows to insert or update for a column.

Here is an example: OrderAmount must be positive value

Syntax: Create Check Constraint on Create Table:

The below SQL script a Check Constraint on OrderAmount column and it will allow only positive value for OrderAmount.

CREATE TABLE Orders (

   OrderID int NOT NULL,

    OrderNumber int NOT NULL,

    StatusID int, 

    OrderAmount Decimal(10,2) Check(OrderAmount > 0) NOT NULL

    PRIMARY KEY (OrderID),

    CONSTRAINT FK_OrderOrderStatus FOREIGN KEY (StatusID)

    REFERENCES OrderStatus(StatusID)

);

Basically Check Keyword is used to apply Check Constraint on a column and usually it is a logically expression

Check(OrderAmount > 0) NOT NULL

Syntax: Create Check Constraint on Alter Table:

The below SQL script a Check Constraint on table, which is already created

ALTER TABLE dbo.Orders

ADD CONSTRAINT OrderAmount_Ckeck Check(OrderAmount > 0);

Syntax: Drop Check Constraint:

The below SQL is used to drop the check constraint from Table

ALTER TABLE Orders

DROP CONSTRAINT OrderAmount_Ckeck;

SQL FOREIGN KEY Constraint

The SQL Foreign Key is used to enforce the link between two tables and it will be a column or a collection of columns which refers to the primary key of another table.

SQL FOREIGN KEY Constraint ensures the referential integrity it means whenever a foreign key value is used it must reference a valid, existing primary key in the parent table. 

Here is an Orders Table, which has StatusID as Foreign Key that refers to OrderStatus Table.

Foreign Key constraint prevents to insert or update invalid data in primary table [ Orders ] which does not exist into another table [OrderStatus] and it always maintain relationship between these two tables.

Syntax: Create Foreign Key on Create Table:

The below SQL script creates a Foreign Key Constraint  ‘FK_OrderOrderStatus’ on StatusID column which refers to primary key column of another table [OrderStatus]

CREATE TABLE Orders (

    OrderID int NOT NULL,

    OrderNumber int NOT NULL,

    StatusID int, 

    PRIMARY KEY (OrderID),

    CONSTRAINT FK_OrderOrderStatus FOREIGN KEY (StatusID)

    REFERENCES OrderStatus(StatusID)

);

Create Table OrderStatus

(

       StatusID int IDENTITY(1,1) NOT NULL,

       StatusCode varchar(3) NOT NULL,

       StatusDescription varchar(50),

       CONSTRAINT [OrderStatus_PK] PRIMARY KEY CLUSTERED (StatusID ASC), 

)

Syntax: Create Foreign Key on Alter Table:

The below SQL creates a Foreign Key Constraint on a column StatusID and the Orders table is already created.

ALTER TABLE Orders

ADD CONSTRAINT FK_OrderOrderStatus

FOREIGN KEY (StatusID) REFERENCES OrderStatus(StatusID);

Syntax: Drop Foreign Key:

The below SQL is used to drop the Foreign Key constraint from Table

ALTER TABLE Orders

DROP CONSTRAINT FK_OrderOrderStatus;