SQL - DATES


Advertisements

Previous Page Next Page

While working with SQL Date datatypes, it is essential to know about the dates format when performing different operations on a database. Please note that, the datatype of column must be chosen while creating a new table in the database.

MySQL Date formats

The different date date formats available in MySQL are mentioned below:

Data TypeFormat
DATEYYYY-MM-DD
DATETIMEYYYY-MM-DD HH:MI:SS
TIMESTAMPYYYY-MM-DD HH:MI:SS
YEARYYYY or YY

SQL Server Date formats

The various date date formats present in SQL Server are mentioned below:

Data TypeFormat
DATEYYYY-MM-DD
DATETIMEYYYY-MM-DD HH:MI:SS
TIMESTAMPA unique number
SMALLDATETIMEYYYY-MM-DD HH:MI:SS

Example: Create a table with date datatype

The below mentioned SQL code creates a table called Employee which contains five columns: EmpID, Name, City, Age and Date of Joining.

CREATE TABLE Employee (
  EmpID INT              NOT NULL,
  Name VARCHAR(255)      NOT NULL,
  City VARCHAR(100),
  Age INT,
  [Date of Joining] DATE FORMAT 'YYYY-MM-DD'
);

This will create a empty table named Employee containing five columns as shown below:

EmpIDNameCityAgeDate of Joining

Example: Working with date datatype

Consider a database table called Employee with the following records:

EmpIDNameCityAgeDate of Joining
1JohnLondon252019-05-25
2MarryNew York242018-10-15
3JoParis272017-06-09
4KimAmsterdam302014-04-21
5RameshNew Delhi282019-05-25
6HuangBeijing282020-01-10

To fetch data of all employees present in the Employee table who joined the organization on '2019-05-25', the SQL code is:

SELECT * FROM Employee
WHERE [Date of Joining] = '2019-05-25';

This will produce the result as shown below:

EmpIDNameCityAgeDate of Joining
1JohnLondon252019-05-25
5RameshNew Delhi282019-05-25

Previous Page Next Page
Advertisements