MYSQL Date Format

MySQL Date Format :-

The date_format() function formats a date as specified format mask.

Format Description
%a Abbreviated weekday name (Sun to Sat)
%b Abbreviated month name (Jan to Dec)
%c Numeric month name (0 to 12)
%D Day of the month as a numeric value, followed by suffix (1st, 2nd, 3rd,
%d Day of the month as a numeric value (01 to 31)
%e Day of the month as a numeric value (0 to 31)
%f Microseconds (000000 to 999999)
%H Hour (00 to 23)
%h Hour (00 to 12)
%I Hour (00 to 12)
%i Minutes (00 to 59)
%j Day of the year (001 to 366)
%k Hour (00 to 23)
%l Hour (1 to 12)
%M Month name in full (January to December)
%m Month name as a numeric value (00 to 12)
%p AM or PM
%r Time in 12 hour AM or PM format (hh:mm:ss AM/PM)
%S Seconds (00 to 59)
%s Seconds (00 to 59)
%T Time in 24 hour format (hh:mm:ss)
%U Week where Sunday is the first day of the week (00 to 53)
%u Week where Monday is the first day of the week (00 to 53)
%V Week where Sunday is the first day of the week (01 to 53). Used with %X
%v Week where Monday is the first day of the week (01 to 53). Used with %X
%W Weekday name in full (Sunday to Saturday)
%w Day of the week where Sunday=0 and Saturday=6
%Y Year as a numeric, 4-digit value
%y Year as a numeric, 2-digit value

Movers in Dubai | Dubai Classifieds
Example :-
SELECT DATE_FORMAT(“2017-06-15”, “%d-%m-%Y”);

Results

15-06-2017

Mysql User Access | Grant Access to User in MySQL

How to give GRANT access to existing MYSQL Users ?

1) SELECT access

GRANT select on <> to <>@%

DBName > Database Name
User Name > MYSQL User Access Name

Movers in Dubai

Example :-

GRANT select on ‘MASTER.*’ to ‘GuestUser@%’

2) UPDATE, INSERT, DELETE Access

GRANT INSERT, UPDATE, DELETE on <> to <>@<>

MYSQL | Get Previous Date | Yesterday Date in MySQL

How to get Yesterday date in MYSQL Query ?

The below function return the yesterday date in MySQL
Movers in Dubai

SELECT SUBDATE(CURDATE(),1)

MYSQL Convert TimeZone | Local Time Zone

How to Convert the DATE TIME column to local time zone ?

In mysql the function CONVERT_TZ (, <>,<), converting a time zone from default to new / local zone.

Movers in Dubai
| Check Responsive site

Example :
Change to IST from Default.

convert_tz(order_date, ‘+00:00’ , ‘+05:30’)

MySQL Data to JSON

How to convert MYSQL Datas to JSON.

json_encode ($datas)

json_decode($datas)

Import CSV file to MySQL | MySQL Import CSV

This below example shows you how to use the LOAD DATA INFILE statement to import CSV file into MySQL table.

The LOAD DATA INFILE statement allows you to read data from a text file and import the file’s data into a database table very fast.

Before importing the file, you need to complete the following steps :

1) Data Import table should be created
2) CSV Columns Should be match with the Database Table.
3) Access required to execute the query

LOAD DATA INFILE ‘<<CSV File Name>>’
INTO TABLE <<Table Name>>
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’
IGNORE 1 ROWS

Example :-

LOAD DATA INFILE ‘f:/LeadsDetails.csv’
INTO TABLE oldleads
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’
IGNORE 1 ROWS

Copy MySQL Structure, Clone SQL Table Structure

Create an duplicate table structure from the primary table

      1) CREATE TABLE enquirydetail1 LIKE enquirydetail;