SQL - DATES
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 Type | Format |
---|---|
DATE | YYYY-MM-DD |
DATETIME | YYYY-MM-DD HH:MI:SS |
TIMESTAMP | YYYY-MM-DD HH:MI:SS |
YEAR | YYYY or YY |
SQL Server Date formats
The various date date formats present in SQL Server are mentioned below:
Data Type | Format |
---|---|
DATE | YYYY-MM-DD |
DATETIME | YYYY-MM-DD HH:MI:SS |
TIMESTAMP | A unique number |
SMALLDATETIME | YYYY-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:
EmpID | Name | City | Age | Date of Joining |
---|---|---|---|---|
Example: Working with date datatype
Consider a database table called Employee with the following records:
EmpID | Name | City | Age | Date of Joining |
---|---|---|---|---|
1 | John | London | 25 | 2019-05-25 |
2 | Marry | New York | 24 | 2018-10-15 |
3 | Jo | Paris | 27 | 2017-06-09 |
4 | Kim | Amsterdam | 30 | 2014-04-21 |
5 | Ramesh | New Delhi | 28 | 2019-05-25 |
6 | Huang | Beijing | 28 | 2020-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:
EmpID | Name | City | Age | Date of Joining |
---|---|---|---|---|
1 | John | London | 25 | 2019-05-25 |
5 | Ramesh | New Delhi | 28 | 2019-05-25 |