Learn Python

Learn Data Structure & Algorithm

Learn Numpy

Learn Pandas

Learn Matplotlib

Learn Seaborn

Learn Statistics

Learn Math

Learn MATLAB

Learn Machine learning

Learn Github

Learn OpenCV

Learn Deep Learning

MySQL Introduction

MySQL Setup

MySQL Operators

MySQL Basic Works

MySQL Create Table

MySQL insert and get values

MySQL Condition

MySQL GROUP BY

MySQL Case

MySQL sorting and calculate avg,sum,count

MySQL Update

MySQL Time

MySQL String

MySQL Rollback & Commit

MySQL Join and Concatenate

MySQL Index

MySQL Date

MySQL View

MYSQL With Python

Learn MongoDB

Learn Web scraping

Learn Excel

Learn Power BI

Learn Tableau

Learn Docker

Learn Hadoop

everything about sql date

Let's see Examples and practice with a database table

Suppose you have a database name database and inside that, you have a table named DataBaseTable which have a date column. In the date column, you have date and time.

CURRENT_DATE()

This function will return the current date and time. This date and time will be taken from the server.

SELECT date_col,CURRENT_DATE(date_col) AS Only_date from database.DataBaseTable;

DATE()

If you pass date and time together in the this function then it will return only date.

SELECT date_col,DATE(date_col) AS Only_date from database.DataBaseTable;

MONTH()

If you pass date and time together in this function then it will return you only month.

SELECT date_col,MONTH(date_col) AS Only_date from database.DataBaseTable;

MONTHNAME()

If you pass date and time together in this function then it will return you only month name.

SELECT date_col,MONTHNAME(date_col) AS Only_date from database.DataBaseTable;

YEAR()

If you pass date and time together in this function then it will return you only year.

SELECT date_col,YEAR(date_col) AS Only_date from database.DataBaseTable;

DAY()

If you pass date and time together in this function then it will return you only day.

SELECT date_col,DAY(date_col) AS Only_date from database.DataBaseTable;

DAYNAME()

If you pass date and time together in this function then it will return you only day name. Here day name means Sat, Sun, Mon, Tue, Wed, Thus, and Fry.

SELECT date_col,DAYNAME(date_col) AS Only_date from database.DataBaseTable;

DAYOFWEEK()

If you pass date and time together in this function then it will return you only day name index. Here day's name means Sat, Sun, Mon, Tue, Wed, Thus, and Fry. Index means, Sat in the first position so the index is 0. Same way Fri index is 6.

SELECT date_col,DAYOFWEEK(date_col) AS Only_date from database.DataBaseTable;

DAYOFYEAR()

If you pass date and time together in this function then it will return you only day number of the year. You know that a year has 364 days. So this function will return the day number like 77 number day from 364 days.

SELECT date_col,DAY(DAYOFYEAR) AS Only_date from database.DataBaseTable;

QUARTER()

If you pass date and time together in this function then it will return you a quarter of that date. It means in which quarter the date is present. In a year there are four quarters, first quarter=January-March, second quarter= April-June, third quarter=July-Sep, and fourth quarter=Oct-Dec.

SELECT date_col,QUARTER(date_col) AS Only_date from database.DataBaseTable;

ADDDATE()

In this function, we pass an existing date and interval. According to that date and interval this function will create a new date. As an interval, you can pass month, day, second, minute, hour, year, week, etc. Suppose you have the date and pass the day as an interval and the interval value is 10. So this function will create a new date from a given date and the date will after 10 days from the given date. It means that here we added 10 days more with the given date.

SELECT ADDDATE("2011-12-15, INTERVAL 10 DAY") AS date_col;

DATEIFF()

To see difference between two dates this function is used. To see the difference you have to pass two dates.

SELECT DATEIFF("2011-12-15","2011-12-27") AS date_col;

Now let's see with databse table which we saw first
SELECT date_col_1,date_col_2,DATEIFF(date_col_1,date_col_2) AS Only_date from database.DataBaseTable;

DATE_FORMAT()

To format the date according to your need, this function is used.

We can divide date format into four parts:
Day:For day you have three options:
  %d-->if you use this then for one digit date it will add 0 before. After
     9 you will have 2 digit date like 12, 25, etc. But from 0 to 9 you will have one digit date. Now
     if you want to show two digit date like 01, 02, 06, etc. Then you will use this.
  %e-->if you use this then for one digit date it will not add 0 before. After
     9 you will have 2 digit date like 12, 25, etc. But from 0 to 9 you will have one digit date. Now
     if you want to show one digit date like 1, 2, 6, etc then you will use this.
  %D-->If you want to show date where st, nd, rd or th is written after the number then use this option. For example 1st, 2nd, 3rd.

Year:For year you have two options:
  %Y-->This will display the year in 4 digit, like 2021, 2001.
  %y-->This will display the year in 2 digit, like 21, 01.

Month:For month you have three options:
  %M-->This will display the month name in full form, like January, March.
  %b-->This will display the month name in short form, like Jan, Mar.
  %m-->This will display the month in digit from, like 1, 5, 12.

Week:For week you have three options:
  %a-->This will show the week days name in the string form and it will show only three letters, like mon, sun.
  %W-->This will show the week days name in the string form and it will show only all the letters, like monday, sunday.
  %w-->This will show the week days in the form of digit(0-6).

Syntax:
SELECT DATE_FORMAT("2011-12-15","%d/%b/%Y") AS date_col;

Here we use slash as a separator but you can hyphen, etc.
Syntax:
SELECT DATE_FORMAT("2011-12-15","%d/%b/%Y, %W") AS date_col;

Now let's see with database table
SELECT date_col_1,date_col_2,DATE_FORMAT(date_col_1,"%d-%b-%Y") AS Only_date from database.DataBaseTable;

Time format

Time format is divided into four parts:
Hour:For hour you have four options:
  %h-->It will show hours from 1 to 12. After 12 it will show 1. It will add
    0 before one digit hour.
  %H-->It will show hours from 1-24. It will add a 0 before one digit hour.
  %g-->It will show hours from 1 to 12. After 12 it will show 1. It will not add
    0 before one digit hour.
  %G-->It will show hours from 1-24. It will not add add 0 before one digit hour.

Minutes:For minutes write %i

Seconds:For seconds write %s

Microseconds: For microseconds write %f

AM and PM write %P

Let's see time with date:

SELECT DATE_FORMAT("2011-12-15 02:30:50","%d/%b/%Y, %g:%i") AS date_col;

SELECT date_col_1,date_col_2,DATE_FORMAT(date_col_1,"%d-%b-%Y") AS Only_date from database.DataBaseTable;

STR_TO_DATE()

In the date format function, we have the date in SQL default format and we changed that in our own format. But in this function, we will have dates in our own format and it will change that date in SQL default format. Here you have to pass two parameters. First, the date which is in your format. Suppose you Mar 14, 2001. Here first you have a month so in the second parameter first write %M, then you have date so write %d and the year so write %Y

Example:
SELECT STR_TO_DATE("Mar 14 2001","%M %d %Y") AS date_col;

SELECT date_col_1,date_col_2,STR_TO_DATE(date_col_1,"%d-%b-%Y") AS Only_date from database.DataBaseTable;

CodersAim is created for learning and training a self learner to become a professional from beginner. While using CodersAim, you agree to have read and accepted our terms of use, privacy policy, Contact Us